Обсуждение: How To Install Extension Via Script File?

Don Parris wrote on 08.08.2013 23:13:
> And to be able to run it from the Bash prompt (as securely as possible).
>
> I thought I could add the commands and run the create script by doing:
> sudo -u postgres psql -U user -W -d mydb --file=/home/user/dev/mydb_create.sql

> I thought that, running my script as the superuser, it would have the privileges necessary
>to install the extension, but I still got a "permission denied" error.

You are not running the script as superuser because you supplied "-U user" and thus the _script_ is executed
as "user". psql is started as "postgres" (the Linux user, not the DB user).

I don't see the reason for using sudo in the first place, -U is enough:

Leave out the sudo, and use:

   psql -U postgres -W -d mydb --file=/home/user/dev/mydb_create.sql

Or if you do want to use sudo, the leave out the -U user parameter:

   sudo -u postgres psql -d mydb --file=/home/user/dev/mydb_create.sql

Thomas

Hi all,

I have a database that uses the ltree extension.  I typically create a new database like so (as a normal user), using my script file:

CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

And then su to postgres, login and install the ltree extension on mydb.  Then I logout of my psql instance and re-run the script (as a normal user) to create the tables & views on mydb.  I comment out the table/view creation portion until I finish the first couple steps, and then uncomment the tables and views on the 2nd run.  Otherwise the script will fail because the ltree extension has to be installed as a superuser.

I want a script something like:

CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

\c mydb

CREATE EXTENSION ltree;

CREATE TABLE mytable(myfields);

rinse, repeat for additional tables and views.

And to be able to run it from the Bash prompt (as securely as possible).

I thought I could add the commands and run the create script by doing:

sudo -u postgres psql -U user -W -d mydb --file=/home/user/dev/mydb_create.sql

I thought that, running my script as the superuser, it would have the privileges necessary to install the extension, but I still got a "permission denied" error.

I did create a password for my postgres user (hence the -W option).  And this is on a local box.

How can I run my script?

Thanks!

Don

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/

GPG Key ID: F5E179BE

On 8/8/2013 2:13 PM, Don Parris wrote:
> I thought I could add the commands and run the create script by doing:
> sudo -u postgres psql -U user -W -d mydb
> --file=/home/user/dev/mydb_create.sql
>
> I thought that, running my script as the superuser, it would have the
> privileges necessary to install the extension, but I still got a
> "permission denied" error.

can the postgres user access /home/user/dev ?

thats aside from the rest of potentially wrong stuff the other guys
mentioned.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Don Parris <parrisdc@gmail.com> writes:
> When I try a simple psql -U postgres -W - just to initiate the psql
> session, I get:
> psql: FATAL:  Peer authentication failed for user "postgres"

> It's like my regular user cannot connect as the postgres user.

You're right, it can't, if you've selected peer authentication in
pg_hba.conf.  You'd need to use some other auth method, perhaps
password-based auth, if you want this to work.  Read up on auth methods
in the fine manual.

> However, this works (with me just typing my password for sudo):
> donp@wiesbaden:~$ sudo -u postgres psql -U postgres
> [sudo] password for donp:

Sure, because then psql is launched as the postgres OS user, and peer auth
will let that user connect as the postgres DB user.

            regards, tom lane

On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent <robjsargent@gmail.com> wrote:

On 08/08/2013 03:13 PM, Don Parris wrote:

Hi all,

I have a database that uses the ltree extension.  I typically create a new database like so (as a normal user), using my script file:

CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

And then su to postgres, login and install the ltree extension on mydb.  Then I logout of my psql instance and re-run the script (as a normal user) to create the tables & views on mydb.  I comment out the table/view creation portion until I finish the first couple steps, and then uncomment the tables and views on the 2nd run.  Otherwise the script will fail because the ltree extension has to be installed as a superuser.

I want a script something like:

CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';

\c mydb

CREATE EXTENSION ltree;

CREATE TABLE mytable(myfields);

rinse, repeat for additional tables and views.

<SNIP>

Have you tried adding the extension to template1.  I find that works nicely as it means "CREATE DATABASE <dbname>" gets the extension.  That said, I'm wondering if you're actually having trouble accessing the extension subdirectory.  Perhaps the server is running as different user than the owner of the extensions?

Thanks Rob,

If I do that, and then create DB, as I do, using "template0 ENCODING "UTF8", the extension does not appear to be installed on the new database.  At least, when I tried that before, it did not appear to work.  I had to install the extension on the database anyway.  I have not had time to delve into how to resolve that - hasn't really been all that important until now.

Chris Travers <chris.travers@gmail.com> writes:
> As for whether UTF-8 is the default, it is in many cases, but I remember
> struggling with the fact that a few Linux distros still default to
> SQL-ASCII.  Ultimately this is something of a packaging issue and the
> default may be set at the package level.

Actually, the default is taken from the locale environment that initdb
sees.  So it's a question of what the distro initializes LANG to (and
whether you've changed that, either system-wide or for the postgres user).

            regards, tom lane