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

On Sat, Jan 29, 2005 at 12:01:09AM -0500, Tom Lane wrote:
> 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.

FWIW, I like the subselect idea. What if there was some kind of column
or function added that returned the data as the command needed it?
Something like ( quote_ident(schema_name) || '.' ||
quote_ident(table_name) ) AS object_id.

Is there a way to go from an OID to a named identifier? That might make
it easier, though I guess it's still kindof exposing OID.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"