On Mon, 16 Apr 2007, Andrew Dunstan wrote:

Larry Rosenman wrote:
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.


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;
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)


I would have expected these to be owned by test...



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at


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

Reply via email to