Skip site navigation (1) Skip section navigation (2)

Re: 9.0: plpgsql eror when restoring a database as a non superuser

From: stefan <stefan(at)therp(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: 9.0: plpgsql eror when restoring a database as a non superuser
Date: 2011-10-22 15:33:01
Message-ID: j7unne$1kn$1@dough.gmane.org (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-general
On 22-10-11 16:12, Adrian Klaver wrote:
> On Saturday, October 22, 2011 2:12:14 am stefan wrote:

>> pg_restore: [archiver (db)] could not execute query: ERROR:  must be
>> owner of language plpgsql
>>
>>      Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
>>
>> Is there a way that this error can be prevented when restoring a
>> database as a non superuser?
>
> http://www.postgresql.org/docs/9.0/interactive/sql-createlanguage.html
>
> "Ordinarily, the user must have the PostgreSQL superuser privilege to register a
> new language. However, the owner of a database can register a new language
> within that database if the language is listed in the pg_pltemplate catalog and
> is marked as allowed to be created by database owners (tmpldbacreate is true).
>

Thank you, Adrian for your response.

I have checked your suggestion: in pg_pltemplate, tmpldbacreate is set 
to true. As you state above, this allows a non superuser to create the 
language in any database that does not yet has that language registered.

However, due to the fact that in 9.0, the language is in template1 and 
owned by postgres, a non superuser database owner will not have the 
right to create or replace this language in any newly created database 
owned by himself.

Therefore the only solution I see is to drop plpgsql from template1, or 
writing a wrapper script that exploits the -l and -L options of pg_restore.

Cheers,
Stefan.


In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2011-10-22 16:21:37
Subject: Re: 9.0: plpgsql eror when restoring a database as a non superuser
Previous:From: Tom LaneDate: 2011-10-22 15:25:43
Subject: Re: Understanding the output of make check

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group