Skip site navigation (1) Skip section navigation (2)

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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Allow GRANT/REVOKE permissions to be applied to all schema
Date: 2005-01-31 23:13:20
Message-ID: 200501311513.20424.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: operationsengineer1Date: 2005-01-31 23:33:02
Subject: Re: Last ID Problem
Previous:From: Neil ConwayDate: 2005-01-31 23:00:17
Subject: Re: FunctionCallN improvement.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group