Re: Schema (namespace) privilege details

Tom Lane wrote:
> 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.

If user1, the owner of the schema1, creates a new table tab1, will user2
who has "Read" privilege to schema1, be automatically granted SELECT
privilege on tab1? Or will he be able to see that tab1 exists, but not
select from it (continuing the analogy with directories)?

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

This looks good to me. I only wonder if public should default to world
read and no create?

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

Agreed.

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

I'd agree with other posted comments -- db owner should also be
essentially a superuser in there own db.

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

I like this. That way I can lock out a particular user if I need to with
a single command. Would A automatically get ALL privileges on objects
created in his schema by others? I think he should.

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

I think there should be a way to prevent temp table creation, but not
set that way as the default. Presumably you could REVOKE INSERT on the
temp schema?

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

I think only the database owner should be able to create schemas in
their own database. That way if I want a user to be able to create
tables, I just grant them CREATE in the public schema, or create a
schema for them.

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

Agreed. How would it work though if say I wanted to create a view in the
public schema, which pointed at a table in a schema which has had SELECT
revoked? Same question for a public function/private table. It would be
ideal if you could do this.

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

I think the database owner should be just like a superuser in his little
world. The db owner should be able to drop contained schemas or other
objects at will.

Just my 2 cents.

Joe