Re: Google SoC: column-level privilege subsystem

>-----Original Message-----
>From: pgsql-hackers-owner(at)postgresql(dot)org
>[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of August Zajonc
>Sent: dinsdag 24 april 2007 18:34
>To: Golden Liu
>Cc: pgsql-hackers(at)postgresql(dot)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