Hi all,
I'm one of the Google SoC's students for PostgreSQL. My project is
to implement column-level privilege in PG. Here is a description of my
project. Any and all help and/or comment is appreciated.
Table-level privilege subsystem in PG is now used like this:
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE
]tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [
PRIVILEGES] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE |
RESTRICT]
According to this and SQL92 standard, I'll define the grammar of
column-level privilege as follows:
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename[ '('
columnname[, ...] ')' ] [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ]
tablename['(' columnname [, ...] ')' ] [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE |
RESTRICT]
According to SQL92, if TABLE privilege is granted to a grantee,
he/she will also get the privilege of ALL columns added to the table.
That is to say, if you commit this command: GRANT SELECT ON student TO Tom;
Then Tom can select all columns of the student table. This is also
true for UPDATE, INSERT and REFERENCES. So, if Tom can see the name
column of the student table but has no right to see other columns, a
superuser should commit some commands like these: REVOKE SELECT ON student FROM Tom; GRANT SELECT ON
student(name)TO Tom;
Here is a plan of my project:1. Modifying the parser for supporting column-level Grant/Revoke
grammar. The grammar is defined as before. This will change gram.y and
some relative data structures.2. Add codes to record column-level privilege information as
meta-data in system catalog pg_attribute. This will add a column named
'attacl' in pg_attribute. The format of this column is just the same
as 'pg_class.relacl'.3. Before evaluating a SQL command, check column-level privilege.
This is done AFTER checking table-level privilege. As I mentioned
before, if table-level privilege is granted, it's not necessary to
check column-level privilege.
