On Mon, 16 Apr 2007, Andrew Dunstan wrote:
Larry Rosenman wrote:
Greetings,
I think I found a bug, or at least a POLA violation. At work, I
created
a user that is NOT a superuser, nor can that user create databases. When I
did a create database foo owner bar, all the schemas are set to be owned by
the superuser that created the database, not the database owner.
Shouldn't everything that is in the DB be owned by the purported owner?
This is on 8.2.3, btw.
Thanks!
umm ... objects are initially owned by their creator, no? Ownership of a db
means you can grant privs over the db, but ownership doesn't cascade. If you
want your user to own objects you should arrange for that user to create
them, or run ALTER objtype foo OWNER TO username. The latter is what pg_dump
does.
the issue is the initial schemas like PUBLIC.
When I try and RESTORE a pg_dump in the current state, we get errors because
the public schema is owned by postgres, and the grant commands are issued
as the user (since I'm restoring as the purported owner.
It would seem to me, that the CREATE DATABASE command should change the owner
of them to the OWNER verb.
$ psql postgres
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \du test
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
test | no | no | no | no limit |
(1 row)
postgres=# create database testing owner test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test".
test=# \dn
List of schemas
Name | Owner
--------------------+-------
information_schema | pgsql
pg_catalog | pgsql
pg_toast | pgsql
public | pgsql
(4 rows)
test=#
I would have expected these to be owned by test...
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED]
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
