Re: Schema (namespace) privilege details - Mailing list pgsql-hackers

I said:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> An aclitem[] column on pg_database seems like the most flexible solution
>> to me.

> Yeah, I was afraid you would say that ;-).  I'd prefer to avoid it
> because I think we'd need to have a TOAST table for pg_database then.
> And I'm not at all sure how to setup a shared toast table.  Can we get
> away with constraining pg_database rows to 8K if they contain ACL lists?

After further thought, ACLs in pg_database are clearly the right way to
go, and we shouldn't let some possible implementation ugliness stop us.
I think we can probably get away without a TOAST table for the time
being, but if we get lots of squawks some way can be found to make one
happen.

So my second pass at a proposal goes like this:

Schemas (namespaces) have two grantable rights: SELECT allows looking up
objects within the namespace, and CREATE allows creating new objects
within the namespace.  A newly created schema allows both rights to its
owner and none to anyone else.  The predefined schemas have rights as
previously stated.

Databases have two grantable rights: CREATE allows creating new regular
(permanent) schemas within the database, while TEMP allows creation of
a temp schema (and thus temp tables).  A new database will initially
allow both these rights to world.  I am inclined to think that template1
should have both rights turned off, however, to prevent the common
I-created-a-lot-of-trash-in-template1 error.  (Not that this will help,
if you do it as superuser.  So maybe it's not worth the trouble.)

To delete an object you must be either owner of that object or owner of
its containing namespace.  (Ownership of a namespace doesn't grant any
other ownership rights over contained objects.)  You will need SELECT
rights on the namespace to look up the object in the first place, but
there's no specific namespace-level right associated with deletion.

To delete a namespace you must be either owner of the namespace or owner
of the database.  All contained objects are dropped.  (The database
owner can thus drop things he does not own, but only as part of deleting
a whole namespace.)

Renaming an object is a right reserved to the object owner.  Possibly
we should also check that the owner (still) has CREATE rights in the
containing namespace; any thoughts there?  Should we allow renaming
to move an object from one namespace to another?

Similarly, renaming a namespace is reserved to the namespace owner,
and perhaps should require that he (still) have schema CREATE rights.


BTW, it occurs to me that once we have ACLs on pg_database entries,
we could define a CONNECT right for databases, and then eliminate
most of the complexity of pg_hba.conf in favor of GRANT/REVOKE CONNECT.
But that's a separate discussion.
        regards, tom lane