Hi all, As mentioned in this thread, it would be good to have regression tests to test the interactions with permissions and LOCK TABLE: http://www.postgresql.org/message-id/20150511195335.ge30...@tamriel.snowman.net Attached is a patch achieving that. Note that it does some checks on the modes SHARE ACCESS, ROW EXCLUSIVE and ACCESS EXCLUSIVE to check all the code paths of LockTableAclCheck@lockcmds.c.
I'll add an entry in the next CF to keep track of it. Regards, -- Michael
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 64a9330..c0cd9fa 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1569,3 +1569,86 @@ DROP USER regressuser4; DROP USER regressuser5; DROP USER regressuser6; ERROR: role "regressuser6" does not exist +-- permissions with LOCK TABLE +CREATE USER locktable_user; +CREATE TABLE lock_table (a int); +-- LOCK TABLE and SELECT permission +GRANT SELECT ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +\c +REVOKE SELECT ON lock_table FROM locktable_user; +-- LOCK TABLE and INSERT permission +GRANT INSERT ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +\c +REVOKE INSERT ON lock_table FROM locktable_user; +-- LOCK TABLE and UPDATE permission +GRANT UPDATE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE UPDATE ON lock_table FROM locktable_user; +-- LOCK TABLE and DELETE permission +GRANT DELETE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE DELETE ON lock_table FROM locktable_user; +-- LOCK TABLE and TRUNCATE permission +GRANT TRUNCATE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ERROR: permission denied for relation lock_table +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE TRUNCATE ON lock_table FROM locktable_user; +-- clean up +DROP TABLE lock_table; +DROP USER locktable_user; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 22b54a2..c1837c4 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -975,3 +975,87 @@ DROP USER regressuser3; DROP USER regressuser4; DROP USER regressuser5; DROP USER regressuser6; + + +-- permissions with LOCK TABLE +CREATE USER locktable_user; +CREATE TABLE lock_table (a int); + +-- LOCK TABLE and SELECT permission +GRANT SELECT ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail +ROLLBACK; +\c +REVOKE SELECT ON lock_table FROM locktable_user; + +-- LOCK TABLE and INSERT permission +GRANT INSERT ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail +ROLLBACK; +\c +REVOKE INSERT ON lock_table FROM locktable_user; + +-- LOCK TABLE and UPDATE permission +GRANT UPDATE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE UPDATE ON lock_table FROM locktable_user; + +-- LOCK TABLE and DELETE permission +GRANT DELETE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE DELETE ON lock_table FROM locktable_user; + +-- LOCK TABLE and TRUNCATE permission +GRANT TRUNCATE ON lock_table TO locktable_user; +SET SESSION AUTHORIZATION locktable_user; +BEGIN; +LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass +COMMIT; +BEGIN; +LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail +ROLLBACK; +BEGIN; +LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass +COMMIT; +\c +REVOKE TRUNCATE ON lock_table FROM locktable_user; + +-- clean up +DROP TABLE lock_table; +DROP USER locktable_user;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
