Thread: Google SoC: column-level privilege subsystem
Hi all,
I'm one of the Google SoC's students for PostgreSQL. My project is
to implement column-level privilege in PG. Here is a description of my
project. Any and all help and/or comment is appreciated.
Table-level privilege subsystem in PG is now used like this:
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE
]tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [
PRIVILEGES] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE |
RESTRICT]
According to this and SQL92 standard, I'll define the grammar of
column-level privilege as follows:
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename[ '('
columnname[, ...] ')' ] [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ]
tablename['(' columnname [, ...] ')' ] [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE |
RESTRICT]
According to SQL92, if TABLE privilege is granted to a grantee,
he/she will also get the privilege of ALL columns added to the table.
That is to say, if you commit this command: GRANT SELECT ON student TO Tom;
Then Tom can select all columns of the student table. This is also
true for UPDATE, INSERT and REFERENCES. So, if Tom can see the name
column of the student table but has no right to see other columns, a
superuser should commit some commands like these: REVOKE SELECT ON student FROM Tom; GRANT SELECT ON
student(name)TO Tom;
Here is a plan of my project:1. Modifying the parser for supporting column-level Grant/Revoke
grammar. The grammar is defined as before. This will change gram.y and
some relative data structures.2. Add codes to record column-level privilege information as
meta-data in system catalog pg_attribute. This will add a column named
'attacl' in pg_attribute. The format of this column is just the same
as 'pg_class.relacl'.3. Before evaluating a SQL command, check column-level privilege.
This is done AFTER checking table-level privilege. As I mentioned
before, if table-level privilege is granted, it's not necessary to
check column-level privilege.
Golden Liu wrote: > 3. Before evaluating a SQL command, check column-level privilege. > This is done AFTER checking table-level privilege. As I mentioned > before, if table-level privilege is granted, it's not necessary to > check column-level privilege. Golden, this sounds good. I'm just a user. It sounds like table || column is the check, so table implies all of columns. ie, revoking a column permission does nothing unless TABLE permission is also revoked. It also might be nice to specify some of the failure / usage modes. ie, how does "SELECT * FROM Students" work if I don't have permission to a column. Return all values except for forbidden ones? How does "SELECT ForbiddenColumn FROM Students" work. For INSERTS, they probably need to fail if you don't have permission to non-null columns. What about columns with default values? Are inserts permitted if you don't have permission to a column with default values? Do you have a project page up somewhere? I wouldn't mind helping with some of the documentation for example. Good luck! - August
Am Dienstag, 24. April 2007 14:54 schrieb Golden Liu: > Here is a plan of my project: All of that should be pretty straightforward. But have you given any thoughts to how you want to represent the column privileges in the parse tree and how you want to process them in the rewriter? That will be the real difficulty in this project, I think. -- Peter Eisentraut http://developer.postgresql.org/~petere/
>-----Original Message----- >From: pgsql-hackers-owner@postgresql.org >[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of August Zajonc >Sent: dinsdag 24 april 2007 18:34 >To: Golden Liu >Cc: pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Google SoC: column-level privilege subsystem > >Golden Liu wrote: >> 3. Before evaluating a SQL command, check column-level privilege. >> This is done AFTER checking table-level privilege. As I mentioned >> before, if table-level privilege is granted, it's not necessary to >> check column-level privilege. > >Golden, this sounds good. I'm just a user. This is already fiddling on several very preliminary details on what coming up. Since they are already presented, these are my ideas about them. The addition of column-specific priviledges is a good step imho. >It sounds like table || column is the check, so table implies >all of columns. ie, revoking a column permission does nothing >unless TABLE permission is also revoked. IF this will be implemented as suggested here, it will become extremely counter-intuitive. Its just like you have access to a file if you have (explicitly been granted) access to the file OR to its constaining directory (thus sort of implicit). My strongly opinion is that, REVOKE column-level priviledge should revoke access to that column, in effect it should reduce the table-level grant to column-level grants. Alternatively, if I grant myself rights to all the columns that is now different than 'ALL' columns? Perhaps some other thoughts... >It also might be nice to specify some of the failure / usage modes. > >ie, how does "SELECT * FROM Students" work if I don't have >permission to a column. Return all values except for forbidden >ones? How does "SELECT ForbiddenColumn FROM Students" work. Since * means all columns, this is what you (usually) want, or at least should expect to get. Other options might confuse users by having disappearing columns and strange errors in effect. Again, my intuitive idea about it. A more interesting example might be, what if you request rows you don't have access to, but the optimizer can get rid of them (pointing to a badly written query usually) e.g.: SELECT x FROM (SELECT * FROM T); Should this constitute an error if I don't have access to column T.y? In my opinions its perfectly legal, and desirable, to let it fail. >For INSERTS, they probably need to fail if you don't have >permission to non-null columns. What about columns with >default values? Are inserts permitted if you don't have >permission to a column with default values? You are suggesting different behaviour for different 'defaults'. 1) If the default is not given (i.e. its actually NULL) than put in NULL. 2) If the default is explicitly given (NULL or a value) than fail? I would really suggest using a single one: If the user has no access and tries to specify it should fail. Otherwise take the current behaviour of the system. Just my EUR 0,02... Good luck with the project. - Joris Dobbelsteen
> >It sounds like table || column is the check, so table implies > >all of columns. ie, revoking a column permission does nothing > >unless TABLE permission is also revoked. > > IF this will be implemented as suggested here, it will become extremely > counter-intuitive. Its just like you have access to a file if you have > (explicitly been granted) access to the file OR to its constaining > directory (thus sort of implicit). > > My strongly opinion is that, REVOKE column-level priviledge should > revoke access to that column, in effect it should reduce the table-level > grant to column-level grants. I think this causes problems when columns are added to the table. If table X has columns A, B, C, and D, and the user has access to A-C but not D, then what happens when column E is added? Logically, if the user started with table access and column D was revoked, then they should have access to column E. But if they started with access to nothing and were explicitly granted A-C, then they shouldn't. You could solve this by having explicit positive and negative ACLs, i.e. your permissions for a particular column are: [table permissions] + [positive column permissions] - [negative column permissions] However, this is both more complicated and possibly imposes a considerably larger performance penalty than the proposed design. In the proposed design, if the appropriate table permissions are granted (presumably the common case), we don't even need to look at the column permissions. But with this design, we need to check every column for negative permissions (unless we cache something at the table level that tells us whether any per-column permissions exist). You'll also need a more complicated grant/revoke syntax so that you can add a positive permission, add a negative permission, or eliminate the per-column setting entirely (whereas in the proposed design grant and revoke are logically opposites of each other, that's not the case here). ...Robert
Dear August Thank you for your reply. On 4/25/07, August Zajonc <augustz@augustz.com> wrote: > Golden Liu wrote: > > 3. Before evaluating a SQL command, check column-level privilege. > > This is done AFTER checking table-level privilege. As I mentioned > > before, if table-level privilege is granted, it's not necessary to > > check column-level privilege. > > Golden, this sounds good. I'm just a user. > > It sounds like table || column is the check, so table implies all of > columns. ie, revoking a column permission does nothing unless TABLE > permission is also revoked. > > It also might be nice to specify some of the failure / usage modes. > > ie, how does "SELECT * FROM Students" work if I don't have permission to > a column. Return all values except for forbidden ones? How does "SELECT > ForbiddenColumn FROM Students" work. > For "SELECT * FROM Students", I think this will just raise an error. In PG, if you commit a command like "SELECT * FROM T1, T2" but do not have permission to T2, PG will raise an error. For column, we should do the same thing. "SELECT ForbiddenColumn FROM Students" will raise an error too. > For INSERTS, they probably need to fail if you don't have permission to > non-null columns. What about columns with default values? Are inserts > permitted if you don't have permission to a column with default values? > For INSERTS, privilege check will just do on columns specified. For table T with two columns, say C1 and C2, and C2 has a default value. If you just have INSERT permission on C1, this will be right: INSERT INTO T(C1) VALUES (V1) since you just specified C1. But this will raise an error: INSERT INTO T VALUES (V1, default) since you specified C2 which you do not have permission to insert into. > Do you have a project page up somewhere? I wouldn't mind helping with > some of the documentation for example. > > Good luck! > > - August > > > > Golden
Robert Haas wrote: >> My strongly opinion is that, REVOKE column-level priviledge should >> revoke access to that column, in effect it should reduce the table-level >> grant to column-level grants. >> > > I think this causes problems when columns are added to the table. If > table X has columns A, B, C, and D, and the user has access to A-C but > not D, then what happens when column E is added? Logically, if the user > started with table access and column D was revoked, then they should > have access to column E. But if they started with access to nothing and > were explicitly granted A-C, then they shouldn't. > > You could solve this by having explicit positive and negative ACLs, i.e. > your permissions for a particular column are: > Agreed I think. While the explicit Table + Positive - Negative is perhaps ideal and the most intuitive (and I think it is) for now I suppose my point was simply that this should be clearly documented. On the question of SELECT raising an error vs omitting the rows, it clearly needs to raise an error in some cases (INSERTS, and FK situations, where there is no permission for the FK). I suppose it'll need to raise an error on the Select * case as well. For systems behind glue to the database, raising errors in these situations will cause some pain as usually the systems do a lot of inspection of the database. I don't think pg_attribute is MVCC safe? As more stuff is added to pg_attribute eventually that risks causing some problems doesn't it? - August
"Robert Haas" <Robert.Haas@dyntek.com> writes:
...
>> IF this will be implemented as suggested here, it will become
>> extremely counter-intuitive.
...
> You could solve this by having explicit positive and negative ACLs, i.e.
> your permissions for a particular column are:
Uh, wait a moment, people. The proposed project is to implement a
capability that is fully, 100% specified by the SQL standard. There
is zero scope for API invention here. You read the spec, you do
what it says.
regards, tom lane
Tom Lane wrote: > "Robert Haas" <Robert.Haas@dyntek.com> writes: > ... > >>> IF this will be implemented as suggested here, it will become >>> extremely counter-intuitive. >>> > ... > >> You could solve this by having explicit positive and negative ACLs, i.e. >> your permissions for a particular column are: >> > > Uh, wait a moment, people. The proposed project is to implement a > capability that is fully, 100% specified by the SQL standard. There > is zero scope for API invention here. You read the spec, you do > what it says. > > I did read the spec. My suggestion still stands. Because this is a non-standard construct in the security world (which generally does && when combining attributes) the fact that revoking permissions on a column does nothing unless table exist deserves being documented. I couldn't find the detail on the rest in the spec (what section is that in?) but I know Oracle allows inserts to happen if the columns without privilege are null or have a default value. Am I missing something obvious in the spec that describes this explicitly? - August