Re: CREATE DATABASE foo OWNER bar

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!

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

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.

cheers

andrew

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

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?

Right. This is on TODO:

%Set proper permissions on non-system schemas during db creation

Currently all schemas are owned by the super-user because they are copied from
the template1 database.

I note it is marked with a %, but it's clearly not easy at all.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera 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?
>>
>
> Right. This is on TODO:
>
> %Set proper permissions on non-system schemas during db creation
>
> Currently all schemas are owned by the super-user because they are copied from
> the template1 database.
>
>
> I note it is marked with a %, but it's clearly not easy at all.
>
>

If it's only schemas I don't see why it would be very hard. If you want
that to cascade to all non-system objects, as Larry suggests, it would
possibly be harder.

In the most common case it will only be the public schema and that will
be empty.

cheers

andrew

Andrew Dunstan wrote:
> Alvaro Herrera 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?
> >>
> >
> >Right. This is on TODO:
> >
> >%Set proper permissions on non-system schemas during db creation
> >
> >Currently all schemas are owned by the super-user because they are copied
> >from
> >the template1 database.
> >
> >
> >I note it is marked with a %, but it's clearly not easy at all.
>
> If it's only schemas I don't see why it would be very hard. If you want
> that to cascade to all non-system objects, as Larry suggests, it would
> possibly be harder.
>
> In the most common case it will only be the public schema and that will
> be empty.

There was already a patch (by Fabien Coelho IIRC) but it was never
applied.

... searches for a while ...

Ah, yes, here it is:
http://archives.postgresql.org/pgsql-patches/2004-06/msg00084.php

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Larry Rosenman" <ler(at)lerctr(dot)org> writes:
> Shouldn't everything that is in the DB be owned by the purported owner?

Not any more than the owner of a schema owns everything in it.

regards, tom lane

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> 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.

That's a different issue entirely, which is that if you want to restore
a dump containing objects of multiple ownerships, you need to be
superuser; else you can't "give away" the ownership.

regards, tom lane

On Mon, 16 Apr 2007, Tom Lane wrote:

> Larry Rosenman <ler(at)lerctr(dot)org> writes:
>> 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.
>
> That's a different issue entirely, which is that if you want to restore
> a dump containing objects of multiple ownerships, you need to be
> superuser; else you can't "give away" the ownership.
>
I guess the issue is that I'd expect public to be owned by the DB Owner after
a CREATE DATABASE foo OWNER bar, which would then quiet up the pg_restore
since that is the error we get on the public schema.

I've remedy'ed the issue with a ALTER SCHEMA, but I think PG ought to do that.

LER

> regards, tom lane
>

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

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> I guess the issue is that I'd expect public to be owned by the DB Owner after
> a CREATE DATABASE foo OWNER bar,

Why? Do you expect the system catalogs to be owned by the DB owner?
What about other random objects that might have been created in the
template database? If the DBA has installed nondefault permission
settings on the public schema or other objects, how do you expect those
to be transformed?

I do not actually agree with that TODO item, as I think it requires
AI-completeness to guess what sorts of changes to apply, and getting
ownership/permissions wrong would create a significant risk of security
issues.

regards, tom lane

Alvaro Herrera 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?
>
> Right. This is on TODO:
>
> %Set proper permissions on non-system schemas during db creation
>
> Currently all schemas are owned by the super-user because they are copied from
> the template1 database.
>
>
> I note it is marked with a %, but it's clearly not easy at all.

'%' removed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> > I guess the issue is that I'd expect public to be owned by the DB Owner after
> > a CREATE DATABASE foo OWNER bar,
>
> Why? Do you expect the system catalogs to be owned by the DB owner?
> What about other random objects that might have been created in the
> template database? If the DBA has installed nondefault permission
> settings on the public schema or other objects, how do you expect those
> to be transformed?
>
> I do not actually agree with that TODO item, as I think it requires
> AI-completeness to guess what sorts of changes to apply, and getting
> ownership/permissions wrong would create a significant risk of security
> issues.

Caution added to TODO item:

* Set proper permissions on non-system schemas during db creation

Currently all schemas are owned by the super-user because they
are copied from the template1 database. However, since all
objects are inherited from the template database, it is not
clear that setting schemas to the db owner is correct.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +