Re: GRANT ON ALL IN schema

* Andrew Dunstan (andrew(at)dunslane(dot)net) wrote:
> Tom Lane wrote:
>> I'm not sure whether there is consensus on not using GRANT ON VIEW
>> (ie, having these patches treat tables and views alike). I was waiting
>> to see if Stephen would put forward a convincing counterargument ...
>
> Conceptually it is right, I think. A view is a virtual table, so the
> counter-argument would need to be pretty good ISTM.

With regard to DefaultACL-

I don't like just masking out the bits for views at create view time.
Right now, a user can 'GRANT INSERT ON <view> TO role;' and it'll
actually store insert privs for that view and use them for ON INSERT DO
INSTEAD type of work. If we're going to treat them as virtual tables,
then we should do that and include all the same permissions that tables
get for views. Additionally, this will make it less of a suprise if we
support updatable views at some point in the future (we wouldn't have
to deal with possibly changing the default acl mask).

Personally, I find that I want different controls on views in general.
This may stem from my compulsive need for a 'clean' system where I don't
want permissions granted on objects that can't support them (eg: views
which don't have ON INSERT DO INSTEAD rules). 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.

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.

With regard to GRANT ALL-

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.

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 *

I've got a simple perl script which does this, and I know others have
pl/pgsql functions and the like for doing it. Adding that capability to
psql, if we can do it cleanly, would be nice.

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';

Writing a function which takes something like:
select grant('SELECT','myschema','*','role');
or takes any kind of actual syntax like:
select cmd('grant select on * to role');
just strikes me as forcing users to use a function for the sake of it
being a function.

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;

We don't currently allow "*" in GRANT syntax, and I strongly doubt that
the SQL committee will some day allow it AND make it mean something
different. If we're really that worried about it, we could have
'GRANTALL' or 'MGRANT' or something.

Thanks,

Stephen