Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd5f551a

Browse files
committed
Improve regression test coverage of table lock modes vs permissions.
Test the interactions with permissions and LOCK TABLE. SpecificallyROW EXCLUSIVE, ACCESS SHARE, and ACCESS EXCLUSIVE modes againstSELECT, INSERT, UPDATE, DELETE, and TRUNCATE permissions. Discussedby Stephen Frost and Michael Paquier, patch by the latter. Backpatchto 9.5 where matching behavior was first committed.
1 parentde184e5 commitd5f551a

File tree

2 files changed

+167
-0
lines changed

2 files changed

+167
-0
lines changed

‎src/test/regress/expected/privileges.out

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1569,3 +1569,86 @@ DROP USER regressuser4;
15691569
DROP USER regressuser5;
15701570
DROP USER regressuser6;
15711571
ERROR: role "regressuser6" does not exist
1572+
-- permissions with LOCK TABLE
1573+
CREATE USER locktable_user;
1574+
CREATE TABLE lock_table (a int);
1575+
-- LOCK TABLE and SELECT permission
1576+
GRANT SELECT ON lock_table TO locktable_user;
1577+
SET SESSION AUTHORIZATION locktable_user;
1578+
BEGIN;
1579+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
1580+
ERROR: permission denied for relation lock_table
1581+
ROLLBACK;
1582+
BEGIN;
1583+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1584+
COMMIT;
1585+
BEGIN;
1586+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
1587+
ERROR: permission denied for relation lock_table
1588+
ROLLBACK;
1589+
\c
1590+
REVOKE SELECT ON lock_table FROM locktable_user;
1591+
-- LOCK TABLE and INSERT permission
1592+
GRANT INSERT ON lock_table TO locktable_user;
1593+
SET SESSION AUTHORIZATION locktable_user;
1594+
BEGIN;
1595+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1596+
COMMIT;
1597+
BEGIN;
1598+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1599+
ERROR: permission denied for relation lock_table
1600+
ROLLBACK;
1601+
BEGIN;
1602+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
1603+
ERROR: permission denied for relation lock_table
1604+
ROLLBACK;
1605+
\c
1606+
REVOKE INSERT ON lock_table FROM locktable_user;
1607+
-- LOCK TABLE and UPDATE permission
1608+
GRANT UPDATE ON lock_table TO locktable_user;
1609+
SET SESSION AUTHORIZATION locktable_user;
1610+
BEGIN;
1611+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1612+
COMMIT;
1613+
BEGIN;
1614+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1615+
ERROR: permission denied for relation lock_table
1616+
ROLLBACK;
1617+
BEGIN;
1618+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1619+
COMMIT;
1620+
\c
1621+
REVOKE UPDATE ON lock_table FROM locktable_user;
1622+
-- LOCK TABLE and DELETE permission
1623+
GRANT DELETE ON lock_table TO locktable_user;
1624+
SET SESSION AUTHORIZATION locktable_user;
1625+
BEGIN;
1626+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1627+
COMMIT;
1628+
BEGIN;
1629+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1630+
ERROR: permission denied for relation lock_table
1631+
ROLLBACK;
1632+
BEGIN;
1633+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1634+
COMMIT;
1635+
\c
1636+
REVOKE DELETE ON lock_table FROM locktable_user;
1637+
-- LOCK TABLE and TRUNCATE permission
1638+
GRANT TRUNCATE ON lock_table TO locktable_user;
1639+
SET SESSION AUTHORIZATION locktable_user;
1640+
BEGIN;
1641+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1642+
COMMIT;
1643+
BEGIN;
1644+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1645+
ERROR: permission denied for relation lock_table
1646+
ROLLBACK;
1647+
BEGIN;
1648+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1649+
COMMIT;
1650+
\c
1651+
REVOKE TRUNCATE ON lock_table FROM locktable_user;
1652+
-- clean up
1653+
DROP TABLE lock_table;
1654+
DROP USER locktable_user;

‎src/test/regress/sql/privileges.sql

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -975,3 +975,87 @@ DROP USER regressuser3;
975975
DROPUSER regressuser4;
976976
DROPUSER regressuser5;
977977
DROPUSER regressuser6;
978+
979+
980+
-- permissions with LOCK TABLE
981+
CREATEUSERlocktable_user;
982+
CREATETABLElock_table (aint);
983+
984+
-- LOCK TABLE and SELECT permission
985+
GRANTSELECTON lock_table TO locktable_user;
986+
SET SESSION AUTHORIZATION locktable_user;
987+
BEGIN;
988+
LOCK TABLE lock_tableIN ROW EXCLUSIVE MODE;-- should fail
989+
ROLLBACK;
990+
BEGIN;
991+
LOCK TABLE lock_tableIN ACCESS SHARE MODE;-- should pass
992+
COMMIT;
993+
BEGIN;
994+
LOCK TABLE lock_tableIN ACCESS EXCLUSIVE MODE;-- should fail
995+
ROLLBACK;
996+
\c
997+
REVOKESELECTON lock_tableFROM locktable_user;
998+
999+
-- LOCK TABLE and INSERT permission
1000+
GRANT INSERTON lock_table TO locktable_user;
1001+
SET SESSION AUTHORIZATION locktable_user;
1002+
BEGIN;
1003+
LOCK TABLE lock_tableIN ROW EXCLUSIVE MODE;-- should pass
1004+
COMMIT;
1005+
BEGIN;
1006+
LOCK TABLE lock_tableIN ACCESS SHARE MODE;-- should fail
1007+
ROLLBACK;
1008+
BEGIN;
1009+
LOCK TABLE lock_tableIN ACCESS EXCLUSIVE MODE;-- should fail
1010+
ROLLBACK;
1011+
\c
1012+
REVOKE INSERTON lock_tableFROM locktable_user;
1013+
1014+
-- LOCK TABLE and UPDATE permission
1015+
GRANTUPDATEON lock_table TO locktable_user;
1016+
SET SESSION AUTHORIZATION locktable_user;
1017+
BEGIN;
1018+
LOCK TABLE lock_tableIN ROW EXCLUSIVE MODE;-- should pass
1019+
COMMIT;
1020+
BEGIN;
1021+
LOCK TABLE lock_tableIN ACCESS SHARE MODE;-- should fail
1022+
ROLLBACK;
1023+
BEGIN;
1024+
LOCK TABLE lock_tableIN ACCESS EXCLUSIVE MODE;-- should pass
1025+
COMMIT;
1026+
\c
1027+
REVOKEUPDATEON lock_tableFROM locktable_user;
1028+
1029+
-- LOCK TABLE and DELETE permission
1030+
GRANTDELETEON lock_table TO locktable_user;
1031+
SET SESSION AUTHORIZATION locktable_user;
1032+
BEGIN;
1033+
LOCK TABLE lock_tableIN ROW EXCLUSIVE MODE;-- should pass
1034+
COMMIT;
1035+
BEGIN;
1036+
LOCK TABLE lock_tableIN ACCESS SHARE MODE;-- should fail
1037+
ROLLBACK;
1038+
BEGIN;
1039+
LOCK TABLE lock_tableIN ACCESS EXCLUSIVE MODE;-- should pass
1040+
COMMIT;
1041+
\c
1042+
REVOKEDELETEON lock_tableFROM locktable_user;
1043+
1044+
-- LOCK TABLE and TRUNCATE permission
1045+
GRANT TRUNCATEON lock_table TO locktable_user;
1046+
SET SESSION AUTHORIZATION locktable_user;
1047+
BEGIN;
1048+
LOCK TABLE lock_tableIN ROW EXCLUSIVE MODE;-- should pass
1049+
COMMIT;
1050+
BEGIN;
1051+
LOCK TABLE lock_tableIN ACCESS SHARE MODE;-- should fail
1052+
ROLLBACK;
1053+
BEGIN;
1054+
LOCK TABLE lock_tableIN ACCESS EXCLUSIVE MODE;-- should pass
1055+
COMMIT;
1056+
\c
1057+
REVOKE TRUNCATEON lock_tableFROM locktable_user;
1058+
1059+
-- clean up
1060+
DROPTABLE lock_table;
1061+
DROPUSER locktable_user;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp