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