Schema (namespace) privilege details

I've been thinking about exactly what to do with access privileges for
namespaces (a/k/a schemas). The SQL99 spec isn't much guidance, since
as far as I can tell it doesn't have explicit privileges for schemas
at all --- and in any case, since it identifies schemas and ownership,
the really interesting cases don't arise.

Here is a straw-man definition --- any comments appreciated.

We'll define two privilege bits for namespaces/schemas: "read" and
"create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
choices). "Read" controls the ability to look up objects within
that namespace --- it's similar to "execute" permission on directories
in Unix. "Create" controls the ability to create new objects within
a namespace. As usual, superusers bypass these checks.

The initial state of the database will be: pg_catalog is world readable,
but has no create permissions; public has world read and create
permissions; pg_toast has no permissions (you can't explicitly inspect
toast tables). Newly created schemas will initially have all permissions
for the owner, no permissions for anyone else. Whenever a pg_temp
namespace is created or recycled by a fresh backend, it will be set to be
owned by the user running that backend, with all permissions for him and
none for anyone else.

Renaming of an object is allowed to the owner of that object regardless of
schema permissions. While we could invent an UPDATE privilege on schemas
to control this, leaving it with the owner seems simpler.

Deletion of an object is allowed either to the owner of the object, or to
the owner of the containing schema. (Without the latter provision, you
couldn't DROP a schema containing objects created by other people; which
seems wrong.) Again, I'd rather keep this based on ownership than invent,
say, a DELETE privilege for schemas.

It's not quite clear what should happen if User A allows User B to create
an object in a schema owned by A, but then revokes read access on that
schema from B. Presumably, B can no longer access the object, even though
he still owns it. A would have the ability to delete the object under
these rules, but is that enough?

One of the things I'd like this mechanism to do is answer the request
we've heard so often about preventing users from creating new tables.
If the DBA revokes write access on the public namespace from a particular
user, and doesn't create a personal schema for that user, then under this
proposal that user would have noplace to create tables --- except TEMP
tables in his temp schema. Is that sufficient, or do the folks who want
this also want a way to prevent TEMP table creation?

Another thing that would be needed to prevent users from creating new
tables is to prevent them from creating schemas for themselves. I am not
sure how to handle that --- should the right to create schemas be treated
as a user property (a column of pg_shadow), or should it be attached
somehow to the database (and if the latter, how)?

As sketched so far, the schema privilege bits would be the same for all
object types --- whether table, type, function, or operator, either you
can look it up (resp. create it) in a given namespace, or you can't.
Offhand I see no need to distinguish different kinds of objects for this
purpose; does anyone think differently?

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.

regards, tom lane