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

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?

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.

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

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.

stefan <stefan(at)therp(dot)nl> writes:
> 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.

True.

> 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.

Or just ignore the error during restore.

regards, tom lane

On Saturday, October 22, 2011 8:33:01 am stefan wrote:

>
> 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.

I misunderstood the intent of the above. I thought the language would acquire
the ownership of the database creator. Some testing (should have done first)
proved me wrong.

>
> 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.

Yes. Though I could see an option to create a template database without plpgsql
in it for use by non-superuser users and leave template1 as is.

>
> Cheers,
> Stefan.

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

>> 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.
>
> Yes. Though I could see an option to create a template database without plpgsql
> in it for use by non-superuser users and leave template1 as is.
>

Adrian,

Thanks for suggesting that. I had not thought about that option and it
may just be the best.

Cheers,
Stefan.

On 22-10-11 18:21, Tom Lane wrote:

> Or just ignore the error during restore.
>

Hi Tom,

for a manual restore I could just live with that, but this is a semi
automated setup and we feel very strongly about acting on pg_restore's
exit code.

Cheers,
Stefan.

On Saturday, October 22, 2011 12:06:25 pm stefan wrote:
> >> 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.
> >
> > Yes. Though I could see an option to create a template database without
> > plpgsql in it for use by non-superuser users and leave template1 as is.
>
> Adrian,
>
> Thanks for suggesting that. I had not thought about that option and it
> may just be the best.

Actually I was going to suggest something more. In the new template CREATE a
non-superuser ROLE and then as super user change ownership of the plpgsql to
that ROLE for that template and databases derived from it. That actually works
until you do the dump when it reverts ownership back to the postgres user. Not
quite sure why that is? At any rate a dead end.

>
> Cheers,
> Stefan.

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