Обсуждение: column level privileges

Apologies if this gets duplicated - original seems to have been dropped
due to patch size - this time I am sending it gzipped.

cheers

andrew

-------- Original Message --------
Subject:     column level privileges
Date:     Tue, 01 Apr 2008 08:32:25 -0400
From:     Andrew Dunstan <andrew@dunslane.net>
To:     Patches (PostgreSQL) <pgsql-patches@postgresql.org>



This patch by Golden Lui was his work for the last Google SoC. I was his
mentor for the project. I have just realised that he didn't send his
final patch to the list.

I guess it's too late for the current commit-fest, but it really needs
to go on a patch queue (my memory on this was jogged by Tom's recent
mention of $Subject).

I'm going to see how much bitrot there is and see what changes are
necessary to get it to apply.

cheers

andrew


-------------
Here is a README for the whole patch.

According to the SQL92 standard, there are four levels in the privilege
hierarchy, i.e. database, tablespace, table, and column. Most commercial
DBMSs support all the levels, but column-level privilege is hitherto
unaddressed in the PostgreSQL, and this patch try to implement it.

What this patch have done:
1. The execution of GRANT/REVOKE for column privileges. Now only
INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 specified.
SELECT privilege is now not supported. This part includes:
    1.1 Add a column named 'attrel' in pg_attribute catalog to store
column privileges. Now all column privileges are stored, no matter
whether they could be implied from table-level privilege.
    1.2 Parser for the new kind of GRANT/REVOKE commands.
    1.3 Execution of GRANT/REVOKE for column privileges. Corresponding
column privileges will be added/removed automatically if no column is
specified, as SQL standard specified.
2. Column-level privilege check.
    Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be
done ONLY on column level. Table-level privilege check was done in the
function InitPlan. Now in this patch, these three kind of privilege are
checked during the parse phase.
    2.1 For UPDATE/INSERT commands. Privilege check is done in the
function transformUpdateStmt/transformInsertStmt.
    2.2 For REFERENCES, privilege check is done in the function
ATAddForeignKeyConstraint. This function will be called whenever a
foreign key constraint is added, like create table, alter table, etc.
    2.3 For COPY command, INSERT privilege is check in the function
DoCopy. SELECT command is checked in DoCopy too.
3. While adding a new column to a table using ALTER TABLE command, set
appropriate privilege for the new column according to privilege already
granted on the table.
4. Allow pg_dump and pg_dumpall to dump in/out column privileges.
5. Add a column named objsubid in pg_shdepend catalog to record ACL
dependencies between column and roles.
6. modify the grammar of ECPG to support column level privileges.
7. change psql's \z (\dp) command to support listing column privileges
for tables and views. If \z(\dp) is run with a pattern, column
privileges are listed after table level privileges.
8. Regression test for column-level privileges. I changed both
privileges.sql and expected/privileges.out, so regression check is now
all passed.

Best wishes
Dong
--
Guodong Liu
Database Lab, School of EECS, Peking University
Room 314, Building 42, Peking University, Beijing, 100871, China

Hello Andrew,<br />  <br /> When do you expect this patch to go in production and available for public use? I would
keepan eye for its release.<br />  <br /> Sanjay Sharma<br /><br />> Date: Tue, 1 Apr 2008 18:40:24 -0400<br />>
From:andrew@dunslane.net<br />> To: pgsql-hackers@postgresql.org<br />> Subject: [HACKERS] column level
privileges<br/>> <br />> <br />> Apologies if this gets duplicated - original seems to have been dropped <br
/>>due to patch size - this time I am sending it gzipped.<br />> <br />> cheers<br />> <br />> andrew<br
/>><br />> -------- Original Message --------<br />> Subject: column level privileges<br />> Date: Tue, 01
Apr2008 08:32:25 -0400<br />> From: Andrew Dunstan <andrew@dunslane.net><br />> To: Patches (PostgreSQL)
<pgsql-patches@postgresql.org><br/>> <br />> <br />> <br />> This patch by Golden Lui was his work
forthe last Google SoC. I was his <br />> mentor for the project. I have just realised that he didn't send his <br
/>>final patch to the list.<br />> <br />> I guess it's too late for the current commit-fest, but it really
needs<br />> to go on a patch queue (my memory on this was jogged by Tom's recent <br />> mention of
$Subject).<br/>> <br />> I'm going to see how much bitrot there is and see what changes are <br />> necessary
toget it to apply.<br />> <br />> cheers<br />> <br />> andrew<br />> <br />> <br />>
-------------<br/>> Here is a README for the whole patch.<br />> <br />> According to the SQL92 standard,
thereare four levels in the privilege <br />> hierarchy, i.e. database, tablespace, table, and column. Most
commercial<br />> DBMSs support all the levels, but column-level privilege is hitherto <br />> unaddressed in the
PostgreSQL,and this patch try to implement it.<br />> <br />> What this patch have done:<br />> 1. The
executionof GRANT/REVOKE for column privileges. Now only <br />> INSERT/UPDATE/REFERENCES privileges are supported,
asSQL92 specified. <br />> SELECT privilege is now not supported. This part includes:<br />> 1.1 Add a column
named'attrel' in pg_attribute catalog to store <br />> column privileges. Now all column privileges are stored, no
matter<br />> whether they could be implied from table-level privilege.<br />> 1.2 Parser for the new kind of
GRANT/REVOKEcommands.<br />> 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding <br />> column
privilegeswill be added/removed automatically if no column is <br />> specified, as SQL standard specified.<br
/>>2. Column-level privilege check.<br />> Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be
<br/>> done ONLY on column level. Table-level privilege check was done in the <br />> function InitPlan. Now in
thispatch, these three kind of privilege are <br />> checked during the parse phase.<br />> 2.1 For UPDATE/INSERT
commands.Privilege check is done in the <br />> function transformUpdateStmt/transformInsertStmt.<br />> 2.2 For
REFERENCES,privilege check is done in the function <br />> ATAddForeignKeyConstraint. This function will be called
whenevera <br />> foreign key constraint is added, like create table, alter table, etc.<br />> 2.3 For COPY
command,INSERT privilege is check in the function <br />> DoCopy. SELECT command is checked in DoCopy too.<br />>
3.While adding a new column to a table using ALTER TABLE command, set <br />> appropriate privilege for the new
columnaccording to privilege already <br />> granted on the table.<br />> 4. Allow pg_dump and pg_dumpall to dump
in/outcolumn privileges.<br />> 5. Add a column named objsubid in pg_shdepend catalog to record ACL <br />>
dependenciesbetween column and roles.<br />> 6. modify the grammar of ECPG to support column level privileges.<br
/>>7. change psql's \z (\dp) command to support listing column privileges <br />> for tables and views. If
\z(\dp)is run with a pattern, column <br />> privileges are listed after table level privileges.<br />> 8.
Regressiontest for column-level privileges. I changed both <br />> privileges.sql and expected/privileges.out, so
regressioncheck is now <br />> all passed.<br />> <br />> Best wishes<br />> Dong<br />> -- <br />>
GuodongLiu<br />> Database Lab, School of EECS, Peking University<br />> Room 314, Building 42, Peking
University,Beijing, 100871, China<br />> <br />> <br /><br /><br /><hr />Exclusive Marriage Proposals! Find UR
lifepartner at Shaadi.com <a href="http://ss1.richmedia.in/recurl.asp?pid=430" target="_new">Try it!</a> 

The earliest will be 8.4, which is many many months away.

It should be possible to produce a patch for 8.3 if you're interested.

cheers

andrew

sanjay sharma wrote:
> Hello Andrew,
>  
> When do you expect this patch to go in production and available for 
> public use? I would keep an eye for its release.
>  
> Sanjay Sharma
>
> > Date: Tue, 1 Apr 2008 18:40:24 -0400
> > From: andrew@dunslane.net
> > To: pgsql-hackers@postgresql.org
> > Subject: [HACKERS] column level privileges
> >
> >
> > Apologies if this gets duplicated - original seems to have been dropped
> > due to patch size - this time I am sending it gzipped.
> >
> > cheers
> >
> > andrew
> >
> > -------- Original Message --------
> > Subject: column level privileges
> > Date: Tue, 01 Apr 2008 08:32:25 -0400
> > From: Andrew Dunstan <andrew@dunslane.net>
> > To: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
> >
> >
> >
> > This patch by Golden Lui was his work for the last Google SoC. I was 
> his
> > mentor for the project. I have just realised that he didn't send his
> > final patch to the list.
> >
> > I guess it's too late for the current commit-fest, but it really needs
> > to go on a patch queue (my memory on this was jogged by Tom's recent
> > mention of $Subject).
> >
> > I'm going to see how much bitrot there is and see what changes are
> > necessary to get it to apply.
> >
> > cheers
> >
> > andrew
> >
> >
> > -------------
> > Here is a README for the whole patch.
> >
> > According to the SQL92 standard, there are four levels in the privilege
> > hierarchy, i.e. database, tablespace, table, and column. Most 
> commercial
> > DBMSs support all the levels, but column-level privilege is hitherto
> > unaddressed in the PostgreSQL, and this patch try to implement it.
> >
> > What this patch have done:
> > 1. The execution of GRANT/REVOKE for column privileges. Now only
> > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 specified.
> > SELECT privilege is now not supported. This part includes:
> > 1.1 Add a column named 'attrel' in pg_attribute catalog to store
> > column privileges. Now all column privileges are stored, no matter
> > whether they could be implied from table-level privilege.
> > 1.2 Parser for the new kind of GRANT/REVOKE commands.
> > 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding
> > column privileges will be added/removed automatically if no column is
> > specified, as SQL standard specified.
> > 2. Column-level privilege check.
> > Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be
> > done ONLY on column level. Table-level privilege check was done in the
> > function InitPlan. Now in this patch, these three kind of privilege are
> > checked during the parse phase.
> > 2.1 For UPDATE/INSERT commands. Privilege check is done in the
> > function transformUpdateStmt/transformInsertStmt.
> > 2.2 For REFERENCES, privilege check is done in the function
> > ATAddForeignKeyConstraint. This function will be called whenever a
> > foreign key constraint is added, like create table, alter table, etc.
> > 2.3 For COPY command, INSERT privilege is check in the function
> > DoCopy. SELECT command is checked in DoCopy too.
> > 3. While adding a new column to a table using ALTER TABLE command, set
> > appropriate privilege for the new column according to privilege already
> > granted on the table.
> > 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.
> > 5. Add a column named objsubid in pg_shdepend catalog to record ACL
> > dependencies between column and roles.
> > 6. modify the grammar of ECPG to support column level privileges.
> > 7. change psql's \z (\dp) command to support listing column privileges
> > for tables and views. If \z(\dp) is run with a pattern, column
> > privileges are listed after table level privileges.
> > 8. Regression test for column-level privileges. I changed both
> > privileges.sql and expected/privileges.out, so regression check is now
> > all passed.
> >
> > Best wishes
> > Dong
> > --
> > Guodong Liu
> > Database Lab, School of EECS, Peking University
> > Room 314, Building 42, Peking University, Beijing, 100871, China
> >
> >
>
>
> ------------------------------------------------------------------------
> Exclusive Marriage Proposals! Find UR life partner at Shaadi.com Try 
> it! <http://ss1.richmedia.in/recurl.asp?pid=430>

It would be great help to me, and I am sure for many other people too who are working with security solutions, if this
featureis released as patch before 8.4 release.<br />  <br /> Sanjay Sharma<br /><br />> Date: Tue, 1 Apr 2008
22:02:30-0400<br />> From: andrew@dunslane.net<br />> To: sanksh@hotmail.com<br />> CC:
pgsql-hackers@postgresql.org<br/>> Subject: Re: [HACKERS] column level privileges<br />> <br />> <br />>
<br/>> The earliest will be 8.4, which is many many months away.<br />> <br />> It should be possible to
producea patch for 8.3 if you're interested.<br />> <br />> cheers<br />> <br />> andrew<br />> <br
/>>sanjay sharma wrote:<br />> > Hello Andrew,<br />> > <br />> > When do you expect this patch to
goin production and available for <br />> > public use? I would keep an eye for its release.<br />> > <br
/>>> Sanjay Sharma<br />> ><br />> > > Date: Tue, 1 Apr 2008 18:40:24 -0400<br />> > >
From:andrew@dunslane.net<br />> > > To: pgsql-hackers@postgresql.org<br />> > > Subject: [HACKERS]
columnlevel privileges<br />> > ><br />> > ><br />> > > Apologies if this gets duplicated -
originalseems to have been dropped<br />> > > due to patch size - this time I am sending it gzipped.<br />>
>><br />> > > cheers<br />> > ><br />> > > andrew<br />> > ><br />> >
>-------- Original Message --------<br />> > > Subject: column level privileges<br />> > > Date:
Tue,01 Apr 2008 08:32:25 -0400<br />> > > From: Andrew Dunstan <andrew@dunslane.net><br />> > >
To:Patches (PostgreSQL) <pgsql-patches@postgresql.org><br />> > ><br />> > ><br />> >
><br/>> > > This patch by Golden Lui was his work for the last Google SoC. I was <br />> > his<br
/>>> > mentor for the project. I have just realised that he didn't send his<br />> > > final patch to
thelist.<br />> > ><br />> > > I guess it's too late for the current commit-fest, but it really
needs<br/>> > > to go on a patch queue (my memory on this was jogged by Tom's recent<br />> > >
mentionof $Subject).<br />> > ><br />> > > I'm going to see how much bitrot there is and see what
changesare<br />> > > necessary to get it to apply.<br />> > ><br />> > > cheers<br />>
>><br />> > > andrew<br />> > ><br />> > ><br />> > > -------------<br />>
>> Here is a README for the whole patch.<br />> > ><br />> > > According to the SQL92 standard,
thereare four levels in the privilege<br />> > > hierarchy, i.e. database, tablespace, table, and column. Most
<br/>> > commercial<br />> > > DBMSs support all the levels, but column-level privilege is hitherto<br
/>>> > unaddressed in the PostgreSQL, and this patch try to implement it.<br />> > ><br />> >
>What this patch have done:<br />> > > 1. The execution of GRANT/REVOKE for column privileges. Now only<br
/>>> > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 specified.<br />> > > SELECT
privilegeis now not supported. This part includes:<br />> > > 1.1 Add a column named 'attrel' in pg_attribute
catalogto store<br />> > > column privileges. Now all column privileges are stored, no matter<br />> >
>whether they could be implied from table-level privilege.<br />> > > 1.2 Parser for the new kind of
GRANT/REVOKEcommands.<br />> > > 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding<br />>
>> column privileges will be added/removed automatically if no column is<br />> > > specified, as SQL
standardspecified.<br />> > > 2. Column-level privilege check.<br />> > > Now for
UPDATE/INSERT/REFERENCESprivilege, privilege check will be<br />> > > done ONLY on column level. Table-level
privilegecheck was done in the<br />> > > function InitPlan. Now in this patch, these three kind of privilege
are<br/>> > > checked during the parse phase.<br />> > > 2.1 For UPDATE/INSERT commands. Privilege
checkis done in the<br />> > > function transformUpdateStmt/transformInsertStmt.<br />> > > 2.2 For
REFERENCES,privilege check is done in the function<br />> > > ATAddForeignKeyConstraint. This function will be
calledwhenever a<br />> > > foreign key constraint is added, like create table, alter table, etc.<br />>
>> 2.3 For COPY command, INSERT privilege is check in the function<br />> > > DoCopy. SELECT command is
checkedin DoCopy too.<br />> > > 3. While adding a new column to a table using ALTER TABLE command, set<br
/>>> > appropriate privilege for the new column according to privilege already<br />> > > granted on
thetable.<br />> > > 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.<br />> > > 5.
Adda column named objsubid in pg_shdepend catalog to record ACL<br />> > > dependencies between column and
roles.<br/>> > > 6. modify the grammar of ECPG to support column level privileges.<br />> > > 7.
changepsql's \z (\dp) command to support listing column privileges<br />> > > for tables and views. If \z(\dp)
isrun with a pattern, column<br />> > > privileges are listed after table level privileges.<br />> >
>8. Regression test for column-level privileges. I changed both<br />> > > privileges.sql and
expected/privileges.out,so regression check is now<br />> > > all passed.<br />> > ><br />> >
>Best wishes<br />> > > Dong<br />> > > --<br />> > > Guodong Liu<br />> > >
DatabaseLab, School of EECS, Peking University<br />> > > Room 314, Building 42, Peking University, Beijing,
100871,China<br />> > ><br />> > ><br />> ><br />> ><br />> >
------------------------------------------------------------------------<br/>> > Exclusive Marriage Proposals!
FindUR life partner at Shaadi.com Try <br />> > it! <http://ss1.richmedia.in/recurl.asp?pid=430><br />>
<br/>> -- <br />> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)<br />> To make changes to
yoursubscription:<br />> http://www.postgresql.org/mailpref/pgsql-hackers<br /><br /><br /><hr />Windows Live Spaces
:Help your online world come to life, add 500 photos a month. <a href="http://home.services.spaces.live.com/"
target="_new">Tryit!</a> 

Postgres does not backport features, so you would need to retrofit the 
patch to 8.3 yourself, or pay / persuade somebody else to do that for 
you. That should not be too hard, as it was in fact developed late in 
the 8.3 cycle.

Before you jump on it as suiting your needs, read carefully. In 
particular, take note of the fact that it is SQL92 privileges, which 
specifically do NOT include SELECT restrictions.

cheers

andrew

sanjay sharma wrote:
> It would be great help to me, and I am sure for many other people too 
> who are working with security solutions, if this feature is released 
> as patch before 8.4 release.
>  
> Sanjay Sharma
>
> > Date: Tue, 1 Apr 2008 22:02:30 -0400
> > From: andrew@dunslane.net
> > To: sanksh@hotmail.com
> > CC: pgsql-hackers@postgresql.org
> > Subject: Re: [HACKERS] column level privileges
> >
> >
> >
> > The earliest will be 8.4, which is many many months away.
> >
> > It should be possible to produce a patch for 8.3 if you're interested.
> >
> > cheers
> >
> > andrew
> >
> > sanjay sharma wrote:
> > > Hello Andrew,
> > >
> > > When do you expect this patch to go in production and available for
> > > public use? I would keep an eye for its release.
> > >
> > > Sanjay Sharma
> > >
> > > > Date: Tue, 1 Apr 2008 18:40:24 -0400
> > > > From: andrew@dunslane.net
> > > > To: pgsql-hackers@postgresql.org
> > > > Subject: [HACKERS] column level privileges
> > > >
> > > >
> > > > Apologies if this gets duplicated - original seems to have been 
> dropped
> > > > due to patch size - this time I am sending it gzipped.
> > > >
> > > > cheers
> > > >
> > > > andrew
> > > >
> > > > -------- Original Message --------
> > > > Subject: column level privileges
> > > > Date: Tue, 01 Apr 2008 08:32:25 -0400
> > > > From: Andrew Dunstan <andrew@dunslane.net>
> > > > To: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
> > > >
> > > >
> > > >
> > > > This patch by Golden Lui was his work for the last Google SoC. I 
> was
> > > his
> > > > mentor for the project. I have just realised that he didn't send his
> > > > final patch to the list.
> > > >
> > > > I guess it's too late for the current commit-fest, but it really 
> needs
> > > > to go on a patch queue (my memory on this was jogged by Tom's recent
> > > > mention of $Subject).
> > > >
> > > > I'm going to see how much bitrot there is and see what changes are
> > > > necessary to get it to apply.
> > > >
> > > > cheers
> > > >
> > > > andrew
> > > >
> > > >
> > > > -------------
> > > > Here is a README for the whole patch.
> > > >
> > > > According to the SQL92 standard, there are four levels in the 
> privilege
> > > > hierarchy, i.e. database, tablespace, table, and column. Most
> > > commercial
> > > > DBMSs support all the levels, but column-level privilege is hitherto
> > > > unaddressed in the PostgreSQL, and this patch try to implement it.
> > > >
> > > > What this patch have done:
> > > > 1. The execution of GRANT/REVOKE for column privileges. Now only
> > > > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 
> specified.
> > > > SELECT privilege is now not supported. This part includes:
> > > > 1.1 Add a column named 'attrel' in pg_attribute catalog to store
> > > > column privileges. Now all column privileges are stored, no matter
> > > > whether they could be implied from table-level privilege.
> > > > 1.2 Parser for the new kind of GRANT/REVOKE commands.
> > > > 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding
> > > > column privileges will be added/removed automatically if no 
> column is
> > > > specified, as SQL standard specified.
> > > > 2. Column-level privilege check.
> > > > Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be
> > > > done ONLY on column level. Table-level privilege check was done 
> in the
> > > > function InitPlan. Now in this patch, these three kind of 
> privilege are
> > > > checked during the parse phase.
> > > > 2.1 For UPDATE/INSERT commands. Privilege check is done in the
> > > > function transformUpdateStmt/transformInsertStmt.
> > > > 2.2 For REFERENCES, privilege check is done in the function
> > > > ATAddForeignKeyConstraint. This function will be called whenever a
> > > > foreign key constraint is added, like create table, alter table, 
> etc.
> > > > 2.3 For COPY command, INSERT privilege is check in the function
> > > > DoCopy. SELECT command is checked in DoCopy too.
> > > > 3. While adding a new column to a table using ALTER TABLE 
> command, set
> > > > appropriate privilege for the new column according to privilege 
> already
> > > > granted on the table.
> > > > 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.
> > > > 5. Add a column named objsubid in pg_shdepend catalog to record ACL
> > > > dependencies between column and roles.
> > > > 6. modify the grammar of ECPG to support column level privileges.
> > > > 7. change psql's \z (\dp) command to support listing column 
> privileges
> > > > for tables and views. If \z(\dp) is run with a pattern, column
> > > > privileges are listed after table level privileges.
> > > > 8. Regression test for column-level privileges. I changed both
> > > > privileges.sql and expected/privileges.out, so regression check 
> is now
> > > > all passed.
> > > >
> > > > Best wishes
> > > > Dong
> > > > --
> > > > Guodong Liu
> > > > Database Lab, School of EECS, Peking University
> > > > Room 314, Building 42, Peking University, Beijing, 100871, China
> > > >
> > > >
> > >
> > >
> > > 
> ------------------------------------------------------------------------
> > > Exclusive Marriage Proposals! Find UR life partner at Shaadi.com Try
> > > it! <http://ss1.richmedia.in/recurl.asp?pid=430>
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> ------------------------------------------------------------------------
> Windows Live Spaces : Help your online world come to life, add 500 
> photos a month. Try it! <http://home.services.spaces.live.com/>

On Tue, Apr 1, 2008 at 5:40 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Apologies if this gets duplicated - original seems to have been dropped due
> to patch size - this time I am sending it gzipped.
>

just for the record, this patch doesn't apply cleanly to CVS

-- 
regards,
Jaime Casanova

Jaime Casanova wrote:
> On Tue, Apr 1, 2008 at 5:40 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>   
>> Apologies if this gets duplicated - original seems to have been dropped due
>> to patch size - this time I am sending it gzipped.
>>
>>     
>
> just for the record, this patch doesn't apply cleanly to CVS
>
>   

I'm not at all surprised. As I said in the original post:

> I'm going to see how much bitrot there is and see what changes are 
> necessary to get it to apply.

cheers

andrew