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

On Fri, 2002-04-19 at 00:14, Tom Lane wrote:

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

Then A should take over ownership.  It would be like the expiry of a
lease on a piece of land: any buildings erected by the lessee become the
property of the landowner.  (If this consequence was not desired, the
objects should not have been created in a database/schema outside the
owner's control.)

> 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 it could be both: a database owner may not want any schemas
created by anyone else, or by some particular user; alternatively, the
administrator may not want a particular user to create any schemas
anywhere.  These are two different kinds of restriction:
  GRANT CREATE SCHEMA TO user | PUBLIC  REVOKE CREATE SCHEMA FROM user | PUBLIC

would allow/disallow the user (other than the database owner) the
theoretical right to create a schema, whereas
  GRANT CREATE SCHEMA IN database TO user | PUBLIC  REVOKE CREATE SCHEMA IN database FROM user | PUBLIC

would allow/disallow him it on a particular database.  Having both gives
more flexibility and allows different people control for different
purposes (suppose someone needs to pay for the privilege to create
schemas in a variable set of databases; the general permission could be
turned on or off according to whether the bill was paid.).  A general
permission would be needed before permission could be effective on a
particular database.


> 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 a database owner should be able to override the owner of a
schema within the database; similarly a schema owner should be able to
override the owner of an object within the schema.  This makes sense in
practice, since the higher owner can delete the schema/object and
recreate it under his own ownership; so there is little point in not
allowing him to change it directly.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "For I am persuaded, that neither death, nor life, nor      angels, nor principalities, nor powers, nor things
present,nor things to come, nor height, nor depth,      nor any other creature, shall be able to separate us      from
thelove of God, which is in Christ Jesus our      Lord."     Romans 8:38,39