Re: 9.0: plpgsql eror when restoring a database as a non superuser - Mailing list pgsql-general

From stefan
Subject Re: 9.0: plpgsql eror when restoring a database as a non superuser
Date
Msg-id j7unne$1kn$1@dough.gmane.org
Whole thread Raw
In response to Re: 9.0: plpgsql eror when restoring a database as a non superuser  (Adrian Klaver <adrian.klaver@gmail.com>)
Responses Re: 9.0: plpgsql eror when restoring a database as a non superuser  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: 9.0: plpgsql eror when restoring a database as a non superuser  (Adrian Klaver <adrian.klaver@gmail.com>)
List 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.

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Understanding the output of make check
Next
From: Tom Lane
Date:
Subject: Re: 9.0: plpgsql eror when restoring a database as a non superuser