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

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.