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

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

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: stefan <stefan(at)therp(dot)nl>
Subject: Re: 9.0: plpgsql eror when restoring a database as a non superuser
Date: 2011-10-22 14:12:30
Message-ID: (view raw or whole thread)
Lists: pgsql-general
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
> 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:
> Is there a way that this error can be prevented when restoring a
> database as a non superuser?

"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

In response to


pgsql-general by date

Next:From: Tom LaneDate: 2011-10-22 15:25:43
Subject: Re: Understanding the output of make check
Previous:From: miesiDate: 2011-10-22 13:48:47
Subject: Understanding the output of make check

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