Column-level privileges in PostgreSQL 8.4

Posted by Ivan / on 01/23/2009 / 1 Comment

Tom Lane just commited a patch providing column-level privileges in PostgreSQL, written by Stephen Frost and others. Here are some samples taken directly from the regression tests to show how this will work:

SET SESSION AUTHORIZATION regressuser1;
CREATE TABLE atest5 (one int, two int, three int);
CREATE TABLE atest6 (one int, two int, blue int);
GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regressuser4;
GRANT ALL (one) ON atest5 TO regressuser3;

INSERT INTO atest5 VALUES (1,2,3);

SET SESSION AUTHORIZATION regressuser4;
SELECT * FROM atest5; -- fail
SELECT one FROM atest5; -- ok

SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
SELECT 1 FROM atest5 WHERE two = 2; -- fail

SELECT one, two FROM atest5; -- fail

SET SESSION AUTHORIZATION regressuser1;
GRANT SELECT (one,two) ON atest6 TO regressuser4;

SET SESSION AUTHORIZATION regressuser4;
SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still

SET SESSION AUTHORIZATION regressuser1;
GRANT SELECT (two) ON atest5 TO regressuser4;

SET SESSION AUTHORIZATION regressuser4;
SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now

INSERT INTO atest5 (two) VALUES (3); -- ok
INSERT INTO atest5 (three) VALUES (4); -- fail

UPDATE atest5 SET three = 10; -- ok
UPDATE atest5 SET one = 8; -- fail

 

 

RSS Feed for this Blog    Comments Feed for this Post   

Comments

  • Ivan says:

    This is a great feature, but currently I have no use case for it. Do you?

    January 23, 2009 at 4:27 PM | Permalink

 

Join this Group Now!

Forgot Password?

Bulgarian PostgreSQL User Group
Powered by Groupsite.com

Visibility Limited Membership By Invitation or Approved Request Default Profile Professional

Your Status Not Logged-In