On Tue, February 28, 2012 12:17, Adrian Klaver wrote:
> 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.
>
Sigh. I will have to think on this before changing anything.
To my mind, the most straight-forward way of dealing with
this is to remove the language from template1 altogether.
Thereafter, the db owner must explicitly add it back in
where required. So, the default would be no extension.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
