On 02/28/2012 10:37 AM, James B. Byrne wrote:
>
> On Tue, February 28, 2012 12:52, Adrian Klaver wrote:
>> On 02/28/2012 09:50 AM, James B. Byrne wrote:
>>>
>>>
>>> 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.
>>
>> Well that is how it was prior to 9.0.
>>
>
> It does not now appear to be the case. When I drop the
> test database and attempt to recreate it the plpgsql
> extension is automatically added back in with postgres as
> the owner. This was a new installation of 9.1 and I have
> made no modifications to the templates whatsoever.
http://www.postgresql.org/docs/9.0/interactive/release-9-0.html
"
E.8.3.7.1. PL/pgSQL Server-Side Language
Install PL/pgSQL by default (Bruce Momjian)
The language can still be removed from a particular database if the
administrator has security or performance concerns about making it
available.
"
Prior to version 9.0 plpgsql was included with the database but not
installed until the DBA requested it. Starting with 9.0 it was installed
by default. Basically went from opt-in to opt-out. You do not have to
touch template1, the language is already loaded. If you do not want that
behavior you have to remove it from template1 or any 'template' database
you choose to CREATE a new database from. In your case, the new database
you created inherited the language from template1 with the super user
privileges. To undo that, in that particular database, you need to DROP
the superuser version of the language(as the superuser) and then
recreate it as the database owner user to get it to run as that user.
For future use, assuming you do not want new databases to inherit the
superuser version of plpgsl, you need to DROP it from template1.
--
Adrian Klaver
adrian.klaver@gmail.com