Re: column level privileges - Mailing list pgsql-hackers

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>