Skip site navigation (1) Skip section navigation (2)

column level privileges

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: column level privileges
Date: 2008-04-01 22:40:24
Message-ID: 47F2B9D8.90702@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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



Attachment: pg_colpriv_version_0.4.patch.gz
Description: application/x-gzip (25.1 KB)

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2008-04-01 23:43:11
Subject: Re: Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P
Previous:From: Paul RamseyDate: 2008-04-01 22:08:17
Subject: Re: Access to Row ID information in Functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group