Re: GRANT ON ALL IN schema - Mailing list pgsql-hackers

Stephen Frost wrote:
> As for changing the
> default ACL syntax to not be based around SCHEMA- I'm concerned that
> we'll then have to define some kind of ordering preference if we get
> away from the defaults being associated with the container object.  If
> we have defaults for users and schemas, which takes precedence?  I don't
> like the idea of trying to merge them.  I'm also not really a fan of
> having the defaults be based on pattern-matching to a relation name,
> that's just creating another namespace headache, imv.
>   
Right, if we make it per user with different types of filters, we'd have 
to merge them when more then one applies, that might be confusing.

> For my needs, the syntax is not of great importance, I'll use what I
> have to.  If ALTER DEFAULT PERMISSIONS is the concensus, then I'd rather
> at least have it than not have anything.
>   
Yeah ALTER DEFAULT PERMISSIONS actually seems like quite reasonable.
But we need to have consensus on the filters, either have one (either 
schema or user based) or have multiple possibilities and then merge them 
if more then one applies.

> While I don't want to go against the SQL spec, it's opinion is that in
> 'GRANT SELECT ON TABLE tab1' the 'TABLE' is optional and not relevant.
> We can keep that and still implement a 'GRANT SELECT ON VIEW tab1' which
> is limited to only operating on views, allowing admins to be more
> explicit about what they want.  That would at least reduce the
> disconnect between 'grant on all', 'default acls', and regular GRANT
> with regard to tables vs. views, presuming we keep them split.
>   
Well, reducing confusion between GRANT ON ALL + DefaultACLs and regular 
GRANT is the whole reason for GRANT ON VIEW. I think we either have to 
have VIEW in all of them or none of them.

> I do like the general idea of making it easier to run commands across
> multiple tables, etc, rather than having 'GRANT ON ALL' syntax.  As I
> believe has been mentioned before, this is a case where we could improve
> our client tools rather than implement it on the server.  For example:
>
> \cmd grant select on * to user
>
> Of course, our new psql * handling would mean this would grant
> select on everything in pg_catalog too, at least if we do the same as
> \d *
>   
This could be fixed using schema.* maybe if we did this ?

> Adding some kind of 'run-multiple' stored proc is an interesting idea
> but I'm afraid the users this is really targetting aren't going to
> appreciate or understand something like:
>
> select
>   cmd('grant select on '
>    || quote_ident(nspname)
>    || '.'
>    || quote_ident(relname)
>    || ' to public')
> from pg_class
> join pg_namespace on (pg_class.nspoid = pg_namespace.oid)
> where pg_namespace.nspname = 'myschema';
>   
Right, something like that goes against the idea of having something simple.
GRANT ON ALL was meant to be simple tool for beginners not swiss knife 
for mass granting. I don't think all new features have to be targeted at 
advanced dbas or VLDBs.

> I really feel like we should be able to take a page from the unix book
> here and come up with some way to handle wildcards in certain
> statements, ala chmod.
>
> grant select on * to role;
> grant select on myschema.* to role;
> grant select on ab* to role;
>   
This syntax would be doable although I am not particularly fond of 
having that "ab*" option.

So, I still don't see consensus on these 3 things.
Do we want to differentiate views from tables in these commands or not ?
Do we want GRANT ON ALL (or GRANT ON * which is mysql style, btw) in SQL 
form (not functions or client enhancements) at all ? - if we decide that 
we don't want to have this as SQL statement then I'll drop the effort.
And how do we want to filter default acls ?

-- 
Regards
Petr Jelinek (PJMODOS)