Re: Schema (namespace) privilege details

Tom Lane writes:

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

I think other databases actually use GRANT CREATE.

About the read permission, I think that other databases use the rule that
you can "see" an object if and only if you have some sort of privilege on
it. I see little reason to create an extra privilege to just see the
existence of objects.

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

That concern would be eliminated by the system above. B can still access
anything it owns. If A doesn't like B anymore, just delete B's stuff in
A's schemas.

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

Maybe the temp schema should be a permanent catalog entry. That way the
DBA can revoke create access from it as a means to disallow users to
create temp tables.

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

An aclitem[] column on pg_database seems like the most flexible solution
to me.

> Offhand I see no need to distinguish different kinds of objects for this
> purpose; does anyone think differently?

Not me.

--
Peter Eisentraut peter_e(at)gmx(dot)net