On Thu, February 21, 2013 13:23, Merlin Moncure wrote: > On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne > <byrnejb@xxxxxxxxxxxxx> 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. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@xxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
