Re: [pgsql-hackers] Allow GRANT/REVOKE permissions to be applied to all schema

Matt,

> a) accept some sort of wildcard for the grant on table syntax:
>     GRANT ... ON TABLE schema.*
>
> b) use something like CASCADE for the grant on schema syntax:
>     GRANT ... ON SCHEMA CASCADE
>     In this case the grant on schema's need to swallow the permissions
>     (SELECT, INSERT, UPDATE ...) which are intended for TABLES. This
> seems to me
>     kind of strange.
>
> therefore I vote for Syntax a)
>
> What do you think?

Can't say I like either.     I'd prefer:

GRANT [PERM] ON ALL TABLES IN SCHEMA [schemaname] TO [user];

In fact, it would be good if you could multiplex this so that applicable
grants could be performed on all objects, for example:

GRANT SELECT ON ALL TABLES, VIEWS IN SCHEMA public TO php-user;

Of course, if you enhanced this further, we'd be storing a "default
permission" to each *new* table/view/function/etc. in the schema definition,
which would be the ideal.  That way, this command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user;

.. would set the defaults for any NEW tables created in public, and this
command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user CASCADE;

... would grant for existing tables as well.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco