Re: Schema (namespace) privilege details

Peter Eisentraut <peter_e(at)gmx(dot)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