On Mon, Feb 27, 2012 at 6:22 PM, James B. Byrne
<byrnejb@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:
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.
