Re: CREATE DATABASE foo OWNER bar

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: ler(at)lerctr(dot)org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893