Thread: Google SoC: column-level privilege subsystem

Google SoC: column-level privilege subsystem

From
"Golden Liu"
Date:
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.


Re: Google SoC: column-level privilege subsystem

From
August Zajonc
Date:
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





Re: Google SoC: column-level privilege subsystem

From
Peter Eisentraut
Date:
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/


Re: Google SoC: column-level privilege subsystem

From
"Joris Dobbelsteen"
Date:
>-----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



Re: Google SoC: column-level privilege subsystem

From
"Robert Haas"
Date:
> >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


Re: Google SoC: column-level privilege subsystem

From
"Golden Liu"
Date:
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


Re: Google SoC: column-level privilege subsystem

From
August Zajonc
Date:
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



Re: Google SoC: column-level privilege subsystem

From
Tom Lane
Date:
"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


Re: Google SoC: column-level privilege subsystem

From
August Zajonc
Date:
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