Re: column level privileges - Mailing list pgsql-hackers

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>