Thread: How To Install Extension Via Script File?
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';
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
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE
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
On 08/08/2013 03:13 PM, Don Parris wrote:
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?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.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';I want a script something like:CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';\c mydbCREATE 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.sqlI 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 AdvocateGPG 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
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:You are not running the script as superuser because you supplied "-U user" and thus the _script_ is executedAnd 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.sqlI 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.
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"
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
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
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: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.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';I want a script something like:CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';\c mydbCREATE 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
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
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:can the postgres user access /home/user/dev ?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.
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
On Thu, Aug 8, 2013 at 8:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don Parris <parrisdc@gmail.com> writes:You're right, it can't, if you've selected peer authentication in
> 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.
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.Sure, because then psql is launched as the postgres OS user, and peer auth
> However, this works (with me just typing my password for sudo):
> donp@wiesbaden:~$ sudo -u postgres psql -U postgres
> [sudo] password for donp:
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
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;"On Fri, Aug 9, 2013 at 3:57 AM, Don Parris <parrisdc@gmail.com> wrote:
On Thu, Aug 8, 2013 at 8:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Don Parris <parrisdc@gmail.com> writes:You're right, it can't, if you've selected peer authentication in
> 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.
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.Sure, because then psql is launched as the postgres OS user, and peer auth
> However, this works (with me just typing my password for sudo):
> donp@wiesbaden:~$ sudo -u postgres psql -U postgres
> [sudo] password for donp:
will let that user connect as the postgres DB user.
regards, tom laneAhhhh... 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 AdvocateGPG Key ID: F5E179BE
On Fri, Aug 9, 2013 at 11:49 AM, Quentin Hartman <qhartman@direwolfdigital.com> wrote:
That way your script won't error out if the extension is already enabled.A bit of an aside, but you also might want to change that "CREATE EXTENSION ltree;" to"CREATE EXTENSION IF NOT EXISTS ltree;"
Thanks for that, Quentin. I'll be sure to do that.
On 08/08/2013 04:57 PM, Don Parris wrote:
Yes, I would drop the "template0" call and I think UTF8 is default now anyway.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: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.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';I want a script something like:CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';\c mydbCREATE 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.
Or maybe add them to template0 but that would be unusual I think
Just a note on this.
Don't add to template0. I think this would mess up backups.
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.
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