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

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> What about a list,

> GRANT ... ON TABLE table1, table2, ... TO user1, user2, ...;

We already allow a list (and have since at least 7.0).

> It would be good if it was a list of wildcards.

I'm a bit itchy about allowing wildcards --- it doesn't seem to fit well
with SQL syntax. The idea of allowing a subselect that returns a set of
names seems cleaner, though I'm not totally sure what to do to make it
schema-proof. I don't much like the idea that it returns a set of
strings that we then parse as possibly-quoted identifiers --- that opens
all sorts of traps for the unwary who forget to use quote_ident etc.

It would be unambiguous to make the subselect return a set of OIDs, eg

GRANT SELECT ON TABLE (SELECT oid FROM pg_class
WHERE relname LIKE 'some-pattern') TO ...

but exposing OIDs like this seems mighty bletcherous too, not to mention
not very easy to use for someone not intimately familiar with the system
catalog layout.

Josh's last suggestion (ALL TABLES IN someschema) seems to me to be a
reasonable compromise between usefulness, syntactic weirdness, and
hiding implementation details.

regards, tom lane