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 <[EMAIL PROTECTED]> To: Patches (PostgreSQL) <[EMAIL PROTECTED]>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
pg_colpriv_version_0.4.patch.gz
Description: GNU Zip compressed data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
