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