WIP: Column-level Privileges

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: WIP: Column-level Privileges
Date: 2008-09-01 06:38:55
Message-ID: 20080901063855.GJ16005@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Greetings,

First, a quick digression into what I've gleaned from the spec in
terms of implementation, from SQL2003:

-- Table-level grants. The spec basically says that a table-level
-- grant is as if the same grant was done on all existing and future
-- columns. (I looked at GRANT and ALTER ADD COLUMN definitions to
-- discover that)
GRANT SELECT, INSERT ON tab1 TO role1;

-- Column-level grants. These apply when accessing the column, and
-- are independent of table-level grants. If you have a
-- column-level grant to a table, you can access that column even if
-- you do not have table-level rights to same table.
GRANT SELECT (col1, col2), INSERT (col1, col2) ON tab1 TO role2;

-- Column-level revokes. Mostly what you would expect, but be aware
-- that table-level grants 'with admin option' apply when revoking
-- column-level grants on that table.
REVOKE SELECT (col1, col2), INSERT (col1, col2) ON tab1 FROM role2;

-- Table-level revokes. A table-level revoke of a given privilege
-- applies to all columns as well. So if you have INSERT on col1
-- and I do a 'REVOKE INSERT ON tab1 FROM you;', your column-level
-- permissions will also be removed.
REVOKE SELECT, INSERT ON tab1 FROM role1;

One of the implications from all of this is that we're going to have
to keep column-level and table-level permissions seperate and
distinct from each other. An upshot from this is that it probably
more closely matches what people expect from how we've handled things
in the past. Also, as long as we do table-level checks first, people
who don't use column-level permissions shouldn't see much of
performance hit. It'll be a bit slower on failure cases because it'll
do per-column checks for ACLs which aren't defined. I'm on the fence
as to if that's a big enough concern to warrant a flag in pg_class.

Attached is the current state of the patch. The grammer/parser
changes have been tested and seem to work reasonably well so far. The
aclchk.c changes are probably broken at the moment. I've been
frustrated with implementing what the spec calls for and what it means
for the code. Specifically, I don't like the number of nested loops
to get the per-privilege column lists into per-column ACL masks, and
dealing with multiple relations at a time. I also don't like the
amount of what seems like mostly duplicated code between the
table-level permissions handling and the column-level handling, but I
might be a little pedantic there. Working the column-level
permissions into other parts of the code has also proven challenging
since you need a (Oid,AttrNumber) combination to identify a column
instead of just an Oid like everything expects.

I've yet to even start in on the changes necessary to get the column
information down to the executor, unfortunately.

Comments welcome, apologies for it not being ready by 9/1. I'm
planning to continue working on it tomorrow, and throughout September
as opportunity allows (ie: when Josh isn't keeping me busy).

Thanks,

Stephen

Attachment Content-Type Size
colprivs_wip.20080901.diff.gz application/octet-stream 13.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jaime Casanova 2008-09-01 06:50:48 Re: Extending grant insert on tables to sequences
Previous Message Heikki Linnakangas 2008-09-01 06:29:29 Re: [PATCHES] VACUUM Improvements - WIP Patch