Re: Need help extripating plpgsql - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Need help extripating plpgsql |
Date | |
Msg-id | 5126C029.6030005@gmail.com Whole thread Raw |
In response to | Re: Need help extripating plpgsql ("James B. Byrne" <byrnejb@harte-lyne.ca>) |
List | pgsql-general |
On 02/21/2013 03:18 PM, James B. Byrne wrote: > > On Thu, February 21, 2013 16:02, Adrian Klaver wrote: >> On 02/21/2013 12:14 PM, James B. Byrne wrote: > >>> >>> The current arrangement is not really satisfactory as it requires >>> either separate template databases for each userid granted the >>> DBCREATE role or the superuser role has to be granted in place of >>> the DBCREATE role. >>> >>> >> >> That is sort of the point of the template system, different templates >> for different situations. >> > > Creating a new database from the system provided standard templates is > not what I would consider a different situation requiring a > specialized template for each and every user granted the DBCREATE > role. Requiring that seems to me to be busywork and a complete waste > of DBA resources. > > If all the elements contained in the standard templates had their > ownerships changed to that of the owner of the new database then my > problem would never have arisen. I do not understand why this is not > the case. Is there a reason why this is so? Hmm, you might be on to something: I changed owner in template1 to me: p_test=# \c template1 You are now connected to database "template1" as user "postgres". template1=# \dL List of languages Name | Owner | Trusted | Description -----------+----------+---------+------------------------------ plpgsql | aklaver | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) Create new database as me: template1=# \c - aklaver You are now connected to database "template1" as user "aklaver". template1=> create database p_test template=template1 owner=aklaver; CREATE DATABASE template1=> \c p_test You are now connected to database "p_test" as user "aklaver". In new database language is owned by me. p_test=> \dL List of languages Name | Owner | Trusted | Description -----------+----------+---------+------------------------------ plpgsql | aklaver | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) Dump the database: aklaver@panda:~> /usr/local/pgsql92/bin/pg_dump p_test -C -U aklaver -p 5442 -f p_test.sql Dropped the database: postgres=> drop database p_test ; DROP DATABASE Restored it: aklaver@panda:~> /usr/local/pgsql92/bin/psql -d postgres -U aklaver -p 5442 -f p_test.sql SET SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database "p_test" as user "aklaver". SET SET SET SET SET CREATE EXTENSION psql:p_test.sql:39: ERROR: must be owner of extension plpgsql Now plpgsql is back to being owned by postgres: postgres=> \c p_test You are now connected to database "p_test" as user "aklaver". p_test=> \dL List of languages Name | Owner | Trusted | Description ---------+----------+---------+------------------------------ plpgsql | postgres | t | PL/pgSQL procedural language (1 row) The issue seems to be, from the p_test.sql file: CREATE DATABASE p_test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; It is creating the database using template0 instead for the template specified in the CREATE DATABASE run from psql. > > -- Adrian Klaver adrian.klaver@gmail.com
pgsql-general by date: