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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
