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

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