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>
> 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?