Re: Allow GRANT/REVOKE permissions to be applied to all schema objects with one command

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:
> Is this:
> GRANT SELECT ON ALL TABLES IN public TO phpuser;
> GRANT SELECT ON NEW TABLES IN public TO phpuser;

> Really better than this?
> GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER
> | EXECUTE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [,
> ...]

The latter confuses privileges-for-a-schema with privileges-for-a-table.
The proposal would fail completely if we had any similarly spelled
privileges for both schemas and tables. Which we don't at the moment,
but it would be foolish to assume that we never will --- especially when
you consider extending this idea to non-table objects.

If you want it to work that way (essentially, losing the distinction
between ALL and NEW cases) then you could spell it like

GRANT privileges ON TABLES IN schemas TO users;

which is implementation-wise the same but avoids the assumption about
non overlap of privilege types.

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?

> This will be faster (FWIW) than a multiple table grant
> because it's just setting one permission at the schema level.

I think this argument is bogus, because the savings in time spent to do
the GRANT will be eaten many times over by extra time spent to look in
two places every time the privileges are checked. But it might be worth
doing it this way anyway, because of the cleaner conceptual model.

regards, tom lane