Thread: 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@lerctr.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
\gor 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@lerctr.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@lerctr.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@lerctr.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@lerctr.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@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
Larry Rosenman <ler@lerctr.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@momjian.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@lerctr.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 becausethey 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@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +