On Mon, Feb 27, 2012 at 6:22 PM, James B. Byrne <byrn...@harte-lyne.ca>wrote:
> > On Mon, February 27, 2012 17:16, Adrian Klaver wrote: > > > > > > From psql do \l and see who actually owns the database. > > > List of databases > Name | Owner | Encoding | > -------------------+----------------------+----------+-- > devl | devl | UTF8 | > test | devl | UTF8 | > postgres | postgres | UTF8 | > template0 | postgres | UTF8 | > | | | > template1 | postgres | UTF8 | > | | | > (5 rows) > > Collation, C-Type and Access Privileges columns removed. > > Alright here is what I found: template1=# \dL List of languages Name | Owner | Trusted ---------+----------+--------- plpgsql | postgres | t template1=# CREATE DATABASE pl_test with owner=aklaver; CREATE DATABASE template1=# \c pl_test aklaver - You are now connected to database "pl_test" as user "aklaver". pl_test=> pl_test=> \dL List of languages Name | Owner | Trusted ---------+----------+--------- plpgsql | postgres | t (1 row) pl_test=> \c - postgres You are now connected to database "pl_test" as user "postgres". pl_test=# DROP EXTENSION plpgsql ; DROP EXTENSION pl_test=# \c - aklaver You are now connected to database "pl_test" as user "aklaver". pl_test=> CREATE EXTENSION plpgsql ; CREATE EXTENSION pl_test=> \dL List of languages Name | Owner | Trusted ---------+---------+--------- plpgsql | aklaver | t (1 row) So when you do the CREATE DATABASE it goes to template1 and grabs the PL with the permissions present in template1. If you want to have the language run with the permissions of the database owner you need to drop the superuser version(as the superuser) and then load it as the database owner. As to why: http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html I guess the options are either do as I did above or create a new template database as the owner you want and use that as the template for your CREATE DATABASE. -- Adrian Klaver adrian.kla...@gmail.com