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(at)dunslane(dot)net>
To: Patches (PostgreSQL) <pgsql-patches(at)postgresql(dot)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