Thread: Re: Allow GRANT/REVOKE permissions to be applied to all schema
Merlin, Tom: > A table or function privilege, if it exists, will override anything for > the table. This will be faster (FWIW) than a multiple table grant > because it's just setting one permission at the schema level. Someone > else will have to comment on how effectively this will work with > existing implementation, however. The problem with this approach is it leaves us with no way to REVOKE permissions on a specific table from a user who has permissions on the SCHEMA. Our permissions model is completely additive, so if you did: GRANT SELECT ON SCHEMA public TO phpuser; then REVOKE SELECT ON TABLE user_passwords FROM phpuser; ... would have no real effect. At the very least, we'd have to code a warning to the effect of: "WARNING: user phpaccess has permissions on the schema level which override the current statement." And overall, I'd think it would make the feature a *lot* less useful; basically it would encourage a lot of DBAs to organize their schemas by security level, which is not really what schemas are for. > This does seem conceptually cleaner than GRANT ON NEW TABLES, which to > me has a flavor of action-at-a-distance about it. Does anyone see any > cases where it's really important to have the distinction between acting > on existing tables and acting on future tables? Databases which are already in production. I suggested it, of course, because I would utilize the distinction if it was available. I don't know about other users. For example, I have one content-serving database for a website which already has a complex set of permissions in place (some of the content is confidential company information, available only to officers of that company). I'd like to, by default, have each new VIEW available to the phpwebuser, because that's why I create views in the first place, 95% of the time. However, I don't want to automatically grant permissions on all existing views to that user in order to get the new default. The analogue here is file permissions vs. umask for unix directories. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > > And overall, I'd think it would make the feature a *lot* less useful; > basically it would encourage a lot of DBAs to organize their schemas by > security level, which is not really what schemas are for. > > >>This does seem conceptually cleaner than GRANT ON NEW TABLES, which to >>me has a flavor of action-at-a-distance about it. Does anyone see any >>cases where it's really important to have the distinction between acting >>on existing tables and acting on future tables? > > > Databases which are already in production. I suggested it, of course, because > I would utilize the distinction if it was available. I don't know about > other users. Do we perhaps want a pg_find tool instead, rather than getting too clever inside the backend? pg_find --type=table --schema=foo --name='system_*' --execute='GRANT ALL ON % TO myuser' -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Do we perhaps want a pg_find tool instead, rather than getting too > clever inside the backend? > > pg_find --type=table --schema=foo --name='system_*' --execute='GRANT > ALL ON % TO myuser' We really should reimplement our query language in Scheme and implement SQL on top of that. It will make many things much easier. :-) -- Peter Eisentraut http://developer.postgresql.org/~petere/
At 2005-02-01 11:02:52 +0100, peter_e@gmx.net wrote: > > We really should reimplement our query language in Scheme and > implement SQL on top of that. It will make many things much > easier. :-) Speaking of which, see <http://schematics.sourceforge.net> -- ams
At 2005-02-01 16:31:32 +0530, ams@oryx.com wrote: > > Speaking of which, see <http://schematics.sourceforge.net> Actually, <http://schematics.sourceforge.net/schemeql.html> -- ams
Richard, > pg_find --type=table --schema=foo --name='system_*' --execute='GRANT ALL > ON % TO myuser' Hey, that's a way keen idea. Regardless of what we do with GRANT/REVOKE. You don't happen to, uh, have any code for that? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > Richard, > > >>pg_find --type=table --schema=foo --name='system_*' --execute='GRANT ALL >> ON % TO myuser' > > > Hey, that's a way keen idea. Regardless of what we do with GRANT/REVOKE. > You don't happen to, uh, have any code for that? Well, currently, what you see posted above is the full extent of the idea. If there's a feeling it'd be useful I could pull my finger out and have a working prototype ready in Perl fairly quickly. It'd take me a while to get something decent in C though. -- Richard Huxton Archonet Ltd
Josh Berkus wrote:
> > pg_find --type=table --schema=foo --name='system_*'
> > --execute='GRANT ALL ON % TO myuser'
>
> Hey, that's a way keen idea. Regardless of what we do with
> GRANT/REVOKE. You don't happen to, uh, have any code for that?
psql -t -A -c "select tablename from pg_tables where schemaname = 'foo'
and tablename like 'system_%';" | xargs -i -n 1 psql -c 'grant all on
{} to myuser;'
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Josh Berkus <josh@agliodbs.com> writes:
> The problem with this approach is it leaves us with no way to REVOKE
> permissions on a specific table from a user who has permissions on the
> SCHEMA. Our permissions model is completely additive, so if you did:
Why is that a problem? The complaint seems about analogous to saying
we should not have groups because you can't REVOKE rights from an
individual user if he has them via a group membership.
> And overall, I'd think it would make the feature a *lot* less useful;
> basically it would encourage a lot of DBAs to organize their schemas by
> security level, which is not really what schemas are for.
Why would this mechanism encourage that more than the other one would?
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes: > And overall, I'd think it would make the feature a > *lot* less useful; basically it would encourage a > lot of DBAs to organize their schemas by > security level, which is not really what schemas > are for. > that's not the way Oracle do things? one schema per user and the objects of the user in its own schema? at least i was tought that way. regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
Tom, > Why is that a problem? The complaint seems about analogous to saying > we should not have groups because you can't REVOKE rights from an > individual user if he has them via a group membership. Oh, mostly I'm just bitching because I had seeing a new feature I can't use ;-) -- Josh Berkus Aglio Database Solutions San Francisco