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

Peter Eisentraut <peter_e@gmx.net> writes:
>> We'll define two privilege bits for namespaces/schemas: "read" and
>> "create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
>> choices).

> I think other databases actually use GRANT CREATE.

Okay, I'm not picky about the keywords.

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

Hm.  That seems like it would not interact at all well with resolution
of ambiguous functions and operators.  In the first place, I don't want
to execute a permission check for every candidate function/operator
before I can assemble the list of candidates to be chosen among.  (For
example, on every use of an '=' operator that would cost us seventy-three
permissions checks, rather than one.)  In the second place, that would
mean that granting or revoking access to a particular operator could
change resolution decisions for *other* operators of the same name ---
which is certainly surprising.  In the third place, it's wrong to be
applying permissions checks at parse-analysis time; they should be done
at run-time.  Otherwise rules have big problems.  I realize that we have
to apply the namespace permissions checks at parse time, but I don't
want to do it for ordinary objects.

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

Hm, we could clone a prototype pg_temp schema entry as a means of
getting this set up, I suppose.  But the first question should be is it
worth troubling with?

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

Yeah, I was afraid you would say that ;-).  I'd prefer to avoid it
because I think we'd need to have a TOAST table for pg_database then.
And I'm not at all sure how to setup a shared toast table.  Can we get
away with constraining pg_database rows to 8K if they contain ACL lists?
(We might get some benefit from compression of the ACL list, but
probably not a heck of a lot.)
        regards, tom lane