Re: Need help extripating plpgsql - Mailing list pgsql-general

On Thu, February 21, 2013 13:23, Merlin Moncure wrote:
> On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne
> <byrnejb@harte-lyne.ca> wrote:
>>
>> On Thu, February 21, 2013 12:38, James B. Byrne wrote:
>>> I am trying, without success, to create a PG-9.2 database without
>>> including the plpgsql extension.  I have tried specifying template0
>>> and the database is nonetheless created with plpgsql.  I have
>>> deleted plpgsql from template1 and the new database is
>>> nonetheless created with plpgsql.
>>>
>>> I desire to remove plpgsql from newly created databases because the
>>> dump that is generated by pgdump contains this line:
>>>
>>> CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
>>>
>>
>> Wrong line.  This is the line
>>
>> COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
>>
>> And yes, I went through this a year ago with PG-9.1 and resolved it
>> once by switching to template0 in the connection configuration. Now
>> it is back with PG-9.2. using the exact same configuration code
>> because evidently plpgsql is added regardless.
>
> curious why you want to do this.  there was actually some debate back
> in the day about pros/cons of having pl/pgsql be a built-in feature,
> which as you can see is where things are going.
>

I want to do this because my automated test harness is choking because
it cannot add an absolutely worthless COMMENT to that extension.  It
cannot add the comment because the language extension is added to the
database with an incorrect owner.  A database created by userid X
should, in ALL RESPECTS, be OWNED by userid X.  When the ownership of
database Y is changed from user A to user B then all of the attributes
of database Y should become owned by B.  For some reason this is not
the case with the plpgsql language extension.

> if you don't mind surgery with a shotgun, you can simply drop the
> extension after the load resolves.

I have tried this and it does not work.  It does not work for the
simple reason that the test harness recreates the test database from
the dump file each run.  The dump file is created with a COMMENT
statement which cannot be applied to the plpgsql language extension
statement unless the user that connects to the database is a
superuser.  That condition makes the granting of DBCREATE to another
userid somewhat pointless.


I have resolved this by:

1. as the postgres user creating a copy of template1 (template2)

2. as the postgres user assigning the test userid as owner of template2

3. as the postgres user dropping the plpgsql extension from template2

4. as the test user adding the plpgsql extension to template2

5. specifying template2 in the database connection configuration file.

I believe, however, that this entire situation is a defect in
postgresql-9.2 and 9.1.  The plpgsql language extension should not be
included in new databases if it does not already exist in the selected
template or when no template is used at all.  Surely the local DBA is
the final arbiter of what a given installation wishes to have in their
databases and forcing them to go through hoops to accomplish this is
hardly user-friendly.

Further, if a language, or for that matter any, extension is added to
a new database from a template or other source then that extension
should be owned by the owner of the resulting database and not by any
other userid.  If there is a good reason as to why this should be
otherwise I would certainly like to have it explained to me.

The current arrangement is not really satisfactory as it requires
either separate template databases for each userid granted the
DBCREATE role or the superuser role has to be granted in place of the
DBCREATE role.



--=20
***          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