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@dcc.uchile.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@decibel.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?"