Re: Allow GRANT/REVOKE permissions to be applied to all schema

Merlin, Tom:

> A table or function privilege, if it exists, will override anything for
> the table. This will be faster (FWIW) than a multiple table grant
> because it's just setting one permission at the schema level. Someone
> else will have to comment on how effectively this will work with
> existing implementation, however.

The problem with this approach is it leaves us with no way to REVOKE
permissions on a specific table from a user who has permissions on the
SCHEMA. Our permissions model is completely additive, so if you did:

GRANT SELECT ON SCHEMA public TO phpuser;
then
REVOKE SELECT ON TABLE user_passwords FROM phpuser;
... would have no real effect.
At the very least, we'd have to code a warning to the effect of:
"WARNING: user phpaccess has permissions on the schema level which override
the current statement."

And overall, I'd think it would make the feature a *lot* less useful;
basically it would encourage a lot of DBAs to organize their schemas by
security level, which is not really what schemas are for.

> This does seem conceptually cleaner than GRANT ON NEW TABLES, which to
> me has a flavor of action-at-a-distance about it. Does anyone see any
> cases where it's really important to have the distinction between acting
> on existing tables and acting on future tables?

Databases which are already in production. I suggested it, of course, because
I would utilize the distinction if it was available. I don't know about
other users.

For example, I have one content-serving database for a website which already
has a complex set of permissions in place (some of the content is
confidential company information, available only to officers of that
company). I'd like to, by default, have each new VIEW available to the
phpwebuser, because that's why I create views in the first place, 95% of the
time. However, I don't want to automatically grant permissions on all
existing views to that user in order to get the new default.

The analogue here is file permissions vs. umask for unix directories.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco