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

On Saturday, October 22, 2011 2:12:14 am stefan wrote:
> Hi,
>
> we run a setup with multiple non superusers who have the option of
> restoring their databases in a semi-automated fashion.
>
> In 9.0, we run into the following error:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
>
> pg_restore: [archiver (db)] Error from TOC entry 1038; 2612 11574
> PROCEDURAL LANGUAGE plpgsql postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR: must be
> owner of language plpgsql
>
> Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
>
> Pg_restore thus returns an error value, causing trouble in our procedures.
>
> In its simplest form, the error can be reproduced as follows:
>
> createdb test
> pg_dump -Fc test > test.psql
> pg_restore -d test < test.psql
>
> The database is created on the same postgresql server as on which it is
> restored. It is therefore no upgrade issue, as in this thread:
> http://archives.postgresql.org/pgsql-general/2010-12/msg00499.php
>
> 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).
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.
The creator of a language becomes its owner and can later drop it, rename it, or
assign it to a new owner. '

>
> I am already aware of a workaround using -l and -L, but I would strongly
> prefer the elegance of letting the bare psql tools do their job properly
>
> Cheers,
> Stefan.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com