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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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/ <https://www.xing.com/profile/Don_Parris&gt;
GPG Key ID: F5E179BE

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 8, 2013 at 5:44 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

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).

My bad - forgot to change the example above to reflect accurately the
user... I did use the postgres user.

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

Fair enough. But I think you are onto something here below...

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

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.

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

postgres=# \q

Maybe I have some permissions issues?

Thanks,
Don

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.

Thanks!
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris&gt;&lt;http://www.linkedin.com/in/dcparris&gt;
GPG Key ID: F5E179BE

On Thu, Aug 8, 2013 at 6:30 PM, John R Pierce <pierce@hogranch.com> wrote:

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.

Oops! That *could* create havoc, couldn't it? Still, should be easy
enough to rectify.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 8, 2013 at 8:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Ahhhh... now I understand. Thanks Tom! That should really help! I'll
check that out.

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris&gt;&lt;http://www.linkedin.com/in/dcparris&gt;
GPG Key ID: F5E179BE

A bit of an aside, but you also might want to change that "CREATE EXTENSION
ltree;" to

"CREATE EXTENSION IF NOT EXISTS ltree;"

That way your script won't error out if the extension is already enabled.

On Fri, Aug 9, 2013 at 3:57 AM, Don Parris <parrisdc@gmail.com> wrote:

On Fri, Aug 9, 2013 at 11:49 AM, Quentin Hartman <
qhartman@direwolfdigital.com> wrote:

A bit of an aside, but you also might want to change that "CREATE
EXTENSION ltree;" to

"CREATE EXTENSION IF NOT EXISTS ltree;"

That way your script won't error out if the extension is already enabled.

Thanks for that, Quentin. I'll be sure to do that.

On 08/08/2013 04:57 PM, Don Parris wrote:

On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent <robjsargent@gmail.com
<mailto: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.

Yes, I would drop the "template0" call and I think UTF8 is default now
anyway.

Or maybe add them to template0 but that would be unusual I think

Just a note on this.

Yes, I would drop the "template0" call and I think UTF8 is default now
anyway.

Or maybe add them to template0 but that would be unusual I think

Don't add to template0. I think this would mess up backups.

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.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general