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

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