Обсуждение: Re: Allow GRANT/REVOKE permissions to be applied to all schema

Поиск
Список
Период
Сортировка

Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Josh Berkus
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all

От
Richard Huxton
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all

От
Peter Eisentraut
Дата:
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/


Re: Allow GRANT/REVOKE permissions to be applied to all

От
Abhijit Menon-Sen
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all

От
Abhijit Menon-Sen
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Josh Berkus
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all

От
Richard Huxton
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Peter Eisentraut
Дата:
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/


Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Tom Lane
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Jaime Casanova
Дата:
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


Re: Allow GRANT/REVOKE permissions to be applied to all schema

От
Josh Berkus
Дата:
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