Обсуждение: Need help extripating plpgsql
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; And for some reason this statement causes my test suit to fail with this error: psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/= structure.sql:22: ERROR: must be owner of extension plpgsql I do not understand why this happens since, as I read this, if the plpgsql extension already exists in the database, which it does, then this statement should not be executed at all. But it does. Further, I do not understand why or how plpgsql is being included into databases on create as I have removed it from template1 and it does not exist in template0 to begin with. I note that plpgsql has NO owner specified in the newly created database, which mystifies me as well. Naively I thought that newly created databases would have all of their components owned by the database owner. How do I resolve this problem? --=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
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. --=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
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. if you don't mind surgery with a shotgun, you can simply drop the extension after the load resolves. merlin
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
On 02/21/2013 09:38 AM, 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; > > And for some reason this statement causes my test suit to fail with > this error: > > psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/structure.sql:22: > ERROR: must be owner of extension plpgsql > > I do not understand why this happens since, as I read this, if the > plpgsql extension already exists in the database, which it does, then > this statement should not be executed at all. But it does. Further, > I do not understand why or how plpgsql is being included into > databases on create as I have removed it from template1 and it does > not exist in template0 to begin with. Actually it does exist in template0. -- Adrian Klaver adrian.klaver@gmail.com
On 02/21/2013 12:14 PM, James B. Byrne wrote:
>
> 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.
template1=# \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
template1=# alter language plpgsql owner to aklaver;
ALTER LANGUAGE
template1=# \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | aklaver | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
>
> 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.
>
>
That is sort of the point of the template system, different templates
for different situations.
--
Adrian Klaver
adrian.klaver@gmail.com
On Thu, February 21, 2013 16:02, Adrian Klaver wrote: > On 02/21/2013 12:14 PM, James B. Byrne wrote: >> >> 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. >> >> > > That is sort of the point of the template system, different templates > for different situations. > Creating a new database from the system provided standard templates is not what I would consider a different situation requiring a specialized template for each and every user granted the DBCREATE role. Requiring that seems to me to be busywork and a complete waste of DBA resources. If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? --=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
On 02/21/2013 03:18 PM, James B. Byrne wrote:
>
> On Thu, February 21, 2013 16:02, Adrian Klaver wrote:
>> On 02/21/2013 12:14 PM, James B. Byrne wrote:
>
>>>
>>> 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.
>>>
>>>
>>
>> That is sort of the point of the template system, different templates
>> for different situations.
>>
>
> Creating a new database from the system provided standard templates is
> not what I would consider a different situation requiring a
> specialized template for each and every user granted the DBCREATE
> role. Requiring that seems to me to be busywork and a complete waste
> of DBA resources.
>
> If all the elements contained in the standard templates had their
> ownerships changed to that of the owner of the new database then my
> problem would never have arisen. I do not understand why this is not
> the case. Is there a reason why this is so?
Hmm, you might be on to something:
I changed owner in template1 to me:
p_test=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | aklaver | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
Create new database as me:
template1=# \c - aklaver
You are now connected to database "template1" as user "aklaver".
template1=> create database p_test template=template1 owner=aklaver;
CREATE DATABASE
template1=> \c p_test
You are now connected to database "p_test" as user "aklaver".
In new database language is owned by me.
p_test=> \dL
List of languages
Name | Owner | Trusted | Description
-----------+----------+---------+------------------------------
plpgsql | aklaver | t | PL/pgSQL procedural language
plpythonu | postgres | f |
(2 rows)
Dump the database:
aklaver@panda:~> /usr/local/pgsql92/bin/pg_dump p_test -C -U aklaver -p
5442 -f p_test.sql
Dropped the database:
postgres=> drop database p_test ;
DROP DATABASE
Restored it:
aklaver@panda:~> /usr/local/pgsql92/bin/psql -d postgres -U aklaver -p
5442 -f p_test.sql
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "p_test" as user "aklaver".
SET
SET
SET
SET
SET
CREATE EXTENSION
psql:p_test.sql:39: ERROR: must be owner of extension plpgsql
Now plpgsql is back to being owned by postgres:
postgres=> \c p_test
You are now connected to database "p_test" as user "aklaver".
p_test=> \dL
List of languages
Name | Owner | Trusted | Description
---------+----------+---------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
(1 row)
The issue seems to be, from the p_test.sql file:
CREATE DATABASE p_test WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
It is creating the database using template0 instead for the template
specified in the CREATE DATABASE run from psql.
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 02/21/2013 03:18 PM, James B. Byrne wrote: > > On Thu, February 21, 2013 16:02, Adrian Klaver wrote: >> On 02/21/2013 12:14 PM, James B. Byrne wrote: > >>> >>> 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. >>> >>> >> >> That is sort of the point of the template system, different templates >> for different situations. >> > > Creating a new database from the system provided standard templates is > not what I would consider a different situation requiring a > specialized template for each and every user granted the DBCREATE > role. Requiring that seems to me to be busywork and a complete waste > of DBA resources. > > If all the elements contained in the standard templates had their > ownerships changed to that of the owner of the new database then my > problem would never have arisen. I do not understand why this is not > the case. Is there a reason why this is so? My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template with a privilege scheme that suits their needs and then can be replicated. Under your proposal every time a database was created the privilege scheme would need to be reestablished. You want the one user model which can be had by doing everything as a superuser. This is why it is generally recommended to have various roles defined in your database cluster. One role being sufficiently privileged to do the superuser work and others for other tasks. -- Adrian Klaver adrian.klaver@gmail.com
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> If all the elements contained in the standard templates had their
> ownerships changed to that of the owner of the new database then my
> problem would never have arisen. I do not understand why this is not
> the case. Is there a reason why this is so?
I don't see why you expect that. Should a non-superuser database owner
have the ability to redefine, say, sum(int4)? You might as well just
give him superuser privileges.
In PG's security model, ownership of a database does *not* automatically
confer any privileges with respect to the contained objects. It doesn't
really give much at all except the ability to drop or rename the
database as a whole.
regards, tom lane
On Thu, February 21, 2013 20:27, Adrian Klaver wrote: > > My previous not withstanding there is a reason I can see why this not > so. Just because a user does not own an object does not mean they > cannot use it. This allows a DBA to set up a template with a > privilege scheme that suits their needs and then can be replicated. > Under your proposal every time a database was created the privilege > scheme would need to be reestablished. You want the one user model > which can be had by doing everything as a superuser. This is why it > is generally recommended to have various roles defined in your > database cluster. One role being sufficiently privileged to do the > superuser work and others for other tasks. > It seems strange to me that a trusted extension, one that can be added by any database owner, is prevented from being treated as trusted in the default configuration. I have no opinion on whether or not plpgsql should be included by default in newly created databases but, I do object that it is included in such a way as to make its management by the subsequent database owner impossible. Lacking the expertise myself might I impose upon you to suggest what configuration of roles would permit the plpgsql extension to be owned by the database owner when added from a template? I am quite willing to use a template2 of my own devising to create new databases but I would rather not have to create a template for every user that might be granted the DBCREATE privilege. This an issue because each project requires at least two separate userids that require the DBCREATE role and both are used to automatically drop and create test and development databases as part of the testing arrangements specific to their project. --=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
On 02/22/2013 07:25 AM, James B. Byrne wrote: > > On Thu, February 21, 2013 20:27, Adrian Klaver wrote: > >> >> My previous not withstanding there is a reason I can see why this not >> so. Just because a user does not own an object does not mean they >> cannot use it. This allows a DBA to set up a template with a >> privilege scheme that suits their needs and then can be replicated. >> Under your proposal every time a database was created the privilege >> scheme would need to be reestablished. You want the one user model >> which can be had by doing everything as a superuser. This is why it >> is generally recommended to have various roles defined in your >> database cluster. One role being sufficiently privileged to do the >> superuser work and others for other tasks. >> > > It seems strange to me that a trusted extension, one that can be added > by any database owner, is prevented from being treated as trusted in > the default configuration. I have no opinion on whether or not > plpgsql should be included by default in newly created databases but, > I do object that it is included in such a way as to make its > management by the subsequent database owner impossible. It would seem there is an interaction between the extension mechanism and CREATE LANGUAGE that is not entirely clear to me. Looking at the extension packaging for plpgsql shows that all it does is call CREATE LANGUAGE and add the COMMENT. The control files has superuser = false which as I understand it means the EXTENSION can be created by non-superusers subject to privilege restrictions on the CREATE LANGUAGE and COMMENT command. In the CREATE LANGUAGE docs there is this: "The default is that trusted languages can be created by database owners, but this can be adjusted by superusers by modifying the contents of pg_pltemplate." By default in pg_pltemplate plpgsql has tmpldbacreate = 't' which would seem to mean it can be created by non-superusers. The issue from what I am seeing is that when the cluster is created the template databases have plpgsql created in them by the superuser(postgres) and that ownership cannot really be transferred. > > Lacking the expertise myself might I impose upon you to suggest what > configuration of roles would permit the plpgsql extension to be owned > by the database owner when added from a template? I am quite willing > to use a template2 of my own devising to create new databases but I > would rather not have to create a template for every user that might > be granted the DBCREATE privilege. This an issue because each project > requires at least two separate userids that require the DBCREATE role > and both are used to automatically drop and create test and > development databases as part of the testing arrangements specific to > their project. At this point I am not sure how to do this with out creating role that has superuser privileges. > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote:=0A=0A> At this point I am no= t sure how to do this with out creating role=0A> that has superuser privile= ges.=0A=0ASomething like this?:=0A=0A-- Set up the template using database = superuser.=0Acreate database template2;=0A\c template2=0Adrop extension plp= gsql;=0Avacuum freeze analyze;=0A\c postgres=0Aupdate pg_database set datis= template =3D true=0A=A0 where datname =3D 'template2';=0Acheckpoint;=0A=0A-= - Create a user who can own the database and plpgsql.=0Acreate user bob wit= h createdb;=0Aset role bob;=0Acreate database bob template template2;=0A\c = bob=0Acreate extension plpgsql;=0A=0A--=0AKevin Grittner=0AEnterpriseDB: ht= tp://www.enterprisedb.com=0AThe Enterprise PostgreSQL Company
On 02/22/2013 11:59 AM, Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> At this point I am not sure how to do this with out creating role >> that has superuser privileges. > > Something like this?: > > -- Set up the template using database superuser. > create database template2; > \c template2 > drop extension plpgsql; > vacuum freeze analyze; > \c postgres > update pg_database set datistemplate = true > where datname = 'template2'; > checkpoint; > > -- Create a user who can own the database and plpgsql. > create user bob with createdb; > set role bob; > create database bob template template2; > \c bob > create extension plpgsql; If you do as above plpgsql is created as with owner postgres. To get owner to be bob you need to do: \c bob set role bob; create extension plpgsql; Either way you still get the error on the COMMENT which is what is tripping up the OP. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote:=0A> On 02/22/2013 11:59 AM, = Kevin Grittner wrote:=0A>> Adrian Klaver <adrian.klaver@gmail.com> wrote:= =0A>>=0A>>> At this point I am not sure how to do this with out creating ro= le=0A>>> that has superuser privileges.=0A>>=0A>> Something like this?:=0A>= >=0A>> -- Set up the template using database superuser.=0A>> create databas= e template2;=0A>> \c template2=0A>> drop extension plpgsql;=0A>> vacuum fre= eze analyze;=0A>> \c postgres=0A>> update pg_database set datistemplate =3D= true=0A>>=A0=A0=A0=A0 where datname =3D 'template2';=0A>> checkpoint;=0A>>= =0A>> -- Create a user who can own the database and plpgsql.=0A>> create us= er bob with createdb;=0A>> set role bob;=0A>> create database bob template = template2;=0A>> \c bob=0A>> create extension plpgsql;=0A>=0A> If you do as = above plpgsql is created as with owner postgres.=0A>=0A> To get owner to be= bob you need to do:=0A>=0A> \c bob=0A> set role bob;=0A> create extension = plpgsql;=0A=0AGood point, I forgot that the user was reset by \c.=0A=0A> Ei= ther way you still get the error on the COMMENT which is what is=0A> trippi= ng up the OP.=0A=0AThe good news is that it seems to be fixed on HEAD:=0A= =0Atest=3D# drop database bob;=0ADROP DATABASE=0Atest=3D# set role bob;=0AS= ET=0Atest=3D> create database bob template template2;=0ACREATE DATABASE=0At= est=3D> \c bob bob=0AYou are now connected to database "bob" as user "bob".= =0Abob=3D> create extension plpgsql;=0ACREATE EXTENSION=0Abob=3D> \dL=0A=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 List of languages=0A= =A0 Name=A0=A0 | Owner | Trusted |=A0=A0=A0=A0=A0=A0=A0=A0 Description=A0= =A0=A0=A0=A0=A0=A0 =A0=0A---------+-------+---------+----------------------= --------=0A=A0plpgsql | bob=A0=A0 | t=A0=A0=A0=A0=A0=A0 | PL/pgSQL procedur= al language=0A(1 row)=0A=0A--=0A=0AKevin Grittner=0AEnterpriseDB: http://ww= w.enterprisedb.com=0AThe Enterprise PostgreSQL Company
On 02/22/2013 02:25 PM, Kevin Grittner wrote: >> >> To get owner to be bob you need to do: >> >> \c bob >> set role bob; >> create extension plpgsql; > > Good point, I forgot that the user was reset by \c. > >> Either way you still get the error on the COMMENT which is what is >> tripping up the OP. > > The good news is that it seems to be fixed on HEAD: I should have been clearer, the problem is in the dump file created from the database. When you try to restore it chokes on the COMMENT line unless you do the restore as a superuser. > > test=# drop database bob; > DROP DATABASE > test=# set role bob; > SET > test=> create database bob template template2; > CREATE DATABASE > test=> \c bob bob > You are now connected to database "bob" as user "bob". > bob=> create extension plpgsql; > CREATE EXTENSION > bob=> \dL > List of languages > Name | Owner | Trusted | Description > ---------+-------+---------+------------------------------ > plpgsql | bob | t | PL/pgSQL procedural language > (1 row) > > -- > > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@gmail.com