Re: Schema (namespace) privilege details

"Rod Taylor" <rbt(at)zort(dot)ca> writes:
>> Should the owner of a database (assume he's not a superuser) have the
>> right to drop any schema in his database, even if he doesn't own it?
>> I can see arguments either way on that one.

> Given that you've chosen to allow the owner of a schema or the table
> to drop a table, it would be consistent to allow the owner of the
> database, schema or table to drop the table.

> Much as I'd tend to allow the owner of a trigger, the table it's on,
> the schema, or the database to drop the trigger.

Hmm, interesting analogy. I don't much like the idea of allowing a
non-owner of a table to drop a trigger; that could lead directly to
data consistency problems, etc. I was envisioning granting the
schema owner the right to drop another user's table in toto --- but not
to have ownership rights to mess with its innards.

That would suggest that a database owner should be allowed to drop a
schema in toto, but not to selectively drop objects within it. Just
as with a table, a schema might have some consistency requirements that
would be broken by zapping individual elements.

> Technically if the owner of a database doesn't have permission to drop
> a table, do they have permission to drop the database? In which case,
> pg_dump, drop create table statement, drop db, create db, restore data
> will accomplish the same thing. All we've done is make the process
> long and drawn out.

If the owner is not superuser, he does not have the privileges to do
dump and restore --- even if he can read everything to dump it, he
won't be allowed to recreate objects under other people's names. So
this analogy is faulty.

However, the database owner definitely does have the right to drop the
whole database, so at some level he should have the right to drop
contained objects. The question is, how selectively can he do it?

regards, tom lane