🌐 AI搜索 & 代理 主页
Skip to content

Commit c44f633

Browse files
jeff-davisjeff-davis
committed
Simplify permissions for LOCK TABLE.
The prior behavior was confusing and hard to document. For instance, if you had UPDATE privileges, you could lock a table in any lock mode except ACCESS SHARE mode. Now, if granted a privilege to lock at a given mode, one also has privileges to lock at a less-conflicting mode. MAINTAIN, UPDATE, DELETE, and TRUNCATE privileges allow any lock mode. INSERT privileges allow ROW EXCLUSIVE (or below). SELECT privileges allow ACCESS SHARE. Reviewed-by: Nathan Bossart Discussion: https://postgr.es/m/9550c76535404a83156252b25a11babb4792ea1e.camel%40j-davis.com
1 parent b7ae039 commit c44f633

File tree

4 files changed

+79
-91
lines changed

4 files changed

+79
-91
lines changed

‎doc/src/sgml/ref/lock.sgml‎

Lines changed: 13 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -165,18 +165,19 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
165165
<title>Notes</title>
166166

167167
<para>
168-
To lock a table, one must ordinarily have the <literal>MAINTAIN</literal>
169-
privilege on the table or be the table's owner, a superuser, or a role
170-
with privileges of the
171-
<link linkend="predefined-roles-table"><literal>pg_maintain</literal></link>
172-
role. <literal>LOCK TABLE ... IN ACCESS SHARE MODE</literal> is allowed
173-
with <literal>SELECT</literal> privileges on the target
174-
table. <literal>LOCK TABLE ... IN ROW EXCLUSIVE MODE</literal> is allowed
175-
with <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
176-
or <literal>TRUNCATE</literal> privileges on the target table. All other
177-
forms of <command>LOCK</command> are allowed with
178-
table-level <literal>UPDATE</literal>, <literal>DELETE</literal>,
179-
or <literal>TRUNCATE</literal> privileges.
168+
To lock a table, the user must have the right privilege for the specified
169+
<replaceable class="parameter">lockmode</replaceable>, or be the table's
170+
owner, a superuser, or a role with privileges of the <link
171+
linkend="predefined-roles-table"><literal>pg_maintain</literal></link>
172+
role. If the user has <literal>MAINTAIN</literal>,
173+
<literal>UPDATE</literal>, <literal>DELETE</literal>, or
174+
<literal>TRUNCATE</literal> privileges on the table, any <replaceable
175+
class="parameter">lockmode</replaceable> is permitted. If the user has
176+
<literal>INSERT</literal> privileges on the table, <literal>ROW EXCLUSIVE
177+
MODE</literal> (or a less-conflicting mode as described in <xref
178+
linkend="explicit-locking"/>) is permitted. If a user has
179+
<literal>SELECT</literal> privileges on the table, <literal>ACCESS SHARE
180+
MODE</literal> is permitted.
180181
</para>
181182

182183
<para>

‎src/backend/commands/lockcmds.c‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -292,16 +292,16 @@ LockTableAclCheck(Oid reloid, LOCKMODE lockmode, Oid userid)
292292
AclResult aclresult;
293293
AclMode aclmask;
294294

295-
/* Verify adequate privilege */
296-
if (lockmode == AccessShareLock)
297-
aclmask = ACL_SELECT;
298-
else if (lockmode == RowExclusiveLock)
299-
aclmask = ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE;
300-
else
301-
aclmask = ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE;
295+
/* any of these privileges permit any lock mode */
296+
aclmask = ACL_MAINTAIN | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE;
297+
298+
/* SELECT privileges also permit ACCESS SHARE and below */
299+
if (lockmode <= AccessShareLock)
300+
aclmask |= ACL_SELECT;
302301

303-
/* MAINTAIN privilege allows all lock modes */
304-
aclmask |= ACL_MAINTAIN;
302+
/* INSERT privileges also permit ROW EXCLUSIVE and below */
303+
if (lockmode <= RowExclusiveLock)
304+
aclmask |= ACL_INSERT;
305305

306306
aclresult = pg_class_aclcheck(reloid, userid, aclmask);
307307

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

Lines changed: 28 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -2706,13 +2706,13 @@ CREATE TABLE lock_table (a int);
27062706
GRANT SELECT ON lock_table TO regress_locktable_user;
27072707
SET SESSION AUTHORIZATION regress_locktable_user;
27082708
BEGIN;
2709+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2710+
COMMIT;
2711+
BEGIN;
27092712
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
27102713
ERROR: permission denied for table lock_table
27112714
ROLLBACK;
27122715
BEGIN;
2713-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2714-
COMMIT;
2715-
BEGIN;
27162716
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
27172717
ERROR: permission denied for table lock_table
27182718
ROLLBACK;
@@ -2722,13 +2722,12 @@ REVOKE SELECT ON lock_table FROM regress_locktable_user;
27222722
GRANT INSERT ON lock_table TO regress_locktable_user;
27232723
SET SESSION AUTHORIZATION regress_locktable_user;
27242724
BEGIN;
2725+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2726+
ROLLBACK;
2727+
BEGIN;
27252728
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
27262729
COMMIT;
27272730
BEGIN;
2728-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
2729-
ERROR: permission denied for table lock_table
2730-
ROLLBACK;
2731-
BEGIN;
27322731
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
27332732
ERROR: permission denied for table lock_table
27342733
ROLLBACK;
@@ -2738,13 +2737,12 @@ REVOKE INSERT ON lock_table FROM regress_locktable_user;
27382737
GRANT UPDATE ON lock_table TO regress_locktable_user;
27392738
SET SESSION AUTHORIZATION regress_locktable_user;
27402739
BEGIN;
2740+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2741+
ROLLBACK;
2742+
BEGIN;
27412743
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
27422744
COMMIT;
27432745
BEGIN;
2744-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
2745-
ERROR: permission denied for table lock_table
2746-
ROLLBACK;
2747-
BEGIN;
27482746
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
27492747
COMMIT;
27502748
\c
@@ -2753,13 +2751,12 @@ REVOKE UPDATE ON lock_table FROM regress_locktable_user;
27532751
GRANT DELETE ON lock_table TO regress_locktable_user;
27542752
SET SESSION AUTHORIZATION regress_locktable_user;
27552753
BEGIN;
2754+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2755+
ROLLBACK;
2756+
BEGIN;
27562757
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
27572758
COMMIT;
27582759
BEGIN;
2759-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
2760-
ERROR: permission denied for table lock_table
2761-
ROLLBACK;
2762-
BEGIN;
27632760
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
27642761
COMMIT;
27652762
\c
@@ -2768,17 +2765,30 @@ REVOKE DELETE ON lock_table FROM regress_locktable_user;
27682765
GRANT TRUNCATE ON lock_table TO regress_locktable_user;
27692766
SET SESSION AUTHORIZATION regress_locktable_user;
27702767
BEGIN;
2768+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
2769+
ROLLBACK;
2770+
BEGIN;
27712771
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
27722772
COMMIT;
27732773
BEGIN;
2774-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
2775-
ERROR: permission denied for table lock_table
2774+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
2775+
COMMIT;
2776+
\c
2777+
REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
2778+
-- LOCK TABLE and MAINTAIN permission
2779+
GRANT MAINTAIN ON lock_table TO regress_locktable_user;
2780+
SET SESSION AUTHORIZATION regress_locktable_user;
2781+
BEGIN;
2782+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
27762783
ROLLBACK;
27772784
BEGIN;
2785+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
2786+
COMMIT;
2787+
BEGIN;
27782788
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
27792789
COMMIT;
27802790
\c
2781-
REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
2791+
REVOKE MAINTAIN ON lock_table FROM regress_locktable_user;
27822792
-- clean up
27832793
DROP TABLE lock_table;
27842794
DROP USER regress_locktable_user;
@@ -2920,14 +2930,6 @@ REINDEX INDEX maintain_test_a_idx;
29202930
ERROR: must be owner of index maintain_test_a_idx
29212931
REINDEX SCHEMA reindex_test;
29222932
ERROR: must be owner of schema reindex_test
2923-
BEGIN;
2924-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
2925-
ERROR: permission denied for table maintain_test
2926-
COMMIT;
2927-
BEGIN;
2928-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
2929-
ERROR: permission denied for table maintain_test
2930-
COMMIT;
29312933
RESET ROLE;
29322934
SET ROLE regress_maintain;
29332935
VACUUM maintain_test;
@@ -2939,12 +2941,6 @@ REINDEX TABLE maintain_test;
29392941
REINDEX INDEX maintain_test_a_idx;
29402942
REINDEX SCHEMA reindex_test;
29412943
ERROR: must be owner of schema reindex_test
2942-
BEGIN;
2943-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
2944-
COMMIT;
2945-
BEGIN;
2946-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
2947-
COMMIT;
29482944
RESET ROLE;
29492945
SET ROLE regress_maintain_all;
29502946
VACUUM maintain_test;
@@ -2955,12 +2951,6 @@ REFRESH MATERIALIZED VIEW refresh_test;
29552951
REINDEX TABLE maintain_test;
29562952
REINDEX INDEX maintain_test_a_idx;
29572953
REINDEX SCHEMA reindex_test;
2958-
BEGIN;
2959-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
2960-
COMMIT;
2961-
BEGIN;
2962-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
2963-
COMMIT;
29642954
RESET ROLE;
29652955
DROP TABLE maintain_test;
29662956
DROP MATERIALIZED VIEW refresh_test;

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

Lines changed: 29 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1707,12 +1707,12 @@ CREATE TABLE lock_table (a int);
17071707
GRANT SELECT ON lock_table TO regress_locktable_user;
17081708
SET SESSION AUTHORIZATION regress_locktable_user;
17091709
BEGIN;
1710-
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
1711-
ROLLBACK;
1712-
BEGIN;
17131710
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
17141711
COMMIT;
17151712
BEGIN;
1713+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
1714+
ROLLBACK;
1715+
BEGIN;
17161716
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
17171717
ROLLBACK;
17181718
\c
@@ -1722,12 +1722,12 @@ REVOKE SELECT ON lock_table FROM regress_locktable_user;
17221722
GRANT INSERT ON lock_table TO regress_locktable_user;
17231723
SET SESSION AUTHORIZATION regress_locktable_user;
17241724
BEGIN;
1725+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1726+
ROLLBACK;
1727+
BEGIN;
17251728
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
17261729
COMMIT;
17271730
BEGIN;
1728-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1729-
ROLLBACK;
1730-
BEGIN;
17311731
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
17321732
ROLLBACK;
17331733
\c
@@ -1737,12 +1737,12 @@ REVOKE INSERT ON lock_table FROM regress_locktable_user;
17371737
GRANT UPDATE ON lock_table TO regress_locktable_user;
17381738
SET SESSION AUTHORIZATION regress_locktable_user;
17391739
BEGIN;
1740+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1741+
ROLLBACK;
1742+
BEGIN;
17401743
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
17411744
COMMIT;
17421745
BEGIN;
1743-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1744-
ROLLBACK;
1745-
BEGIN;
17461746
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
17471747
COMMIT;
17481748
\c
@@ -1752,12 +1752,12 @@ REVOKE UPDATE ON lock_table FROM regress_locktable_user;
17521752
GRANT DELETE ON lock_table TO regress_locktable_user;
17531753
SET SESSION AUTHORIZATION regress_locktable_user;
17541754
BEGIN;
1755+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1756+
ROLLBACK;
1757+
BEGIN;
17551758
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
17561759
COMMIT;
17571760
BEGIN;
1758-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1759-
ROLLBACK;
1760-
BEGIN;
17611761
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
17621762
COMMIT;
17631763
\c
@@ -1767,16 +1767,31 @@ REVOKE DELETE ON lock_table FROM regress_locktable_user;
17671767
GRANT TRUNCATE ON lock_table TO regress_locktable_user;
17681768
SET SESSION AUTHORIZATION regress_locktable_user;
17691769
BEGIN;
1770+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1771+
ROLLBACK;
1772+
BEGIN;
17701773
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
17711774
COMMIT;
17721775
BEGIN;
1773-
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1776+
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1777+
COMMIT;
1778+
\c
1779+
REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
1780+
1781+
-- LOCK TABLE and MAINTAIN permission
1782+
GRANT MAINTAIN ON lock_table TO regress_locktable_user;
1783+
SET SESSION AUTHORIZATION regress_locktable_user;
1784+
BEGIN;
1785+
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
17741786
ROLLBACK;
17751787
BEGIN;
1788+
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1789+
COMMIT;
1790+
BEGIN;
17761791
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
17771792
COMMIT;
17781793
\c
1779-
REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
1794+
REVOKE MAINTAIN ON lock_table FROM regress_locktable_user;
17801795

17811796
-- clean up
17821797
DROP TABLE lock_table;
@@ -1884,12 +1899,6 @@ REFRESH MATERIALIZED VIEW refresh_test;
18841899
REINDEX TABLE maintain_test;
18851900
REINDEX INDEX maintain_test_a_idx;
18861901
REINDEX SCHEMA reindex_test;
1887-
BEGIN;
1888-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
1889-
COMMIT;
1890-
BEGIN;
1891-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
1892-
COMMIT;
18931902
RESET ROLE;
18941903

18951904
SET ROLE regress_maintain;
@@ -1901,12 +1910,6 @@ REFRESH MATERIALIZED VIEW refresh_test;
19011910
REINDEX TABLE maintain_test;
19021911
REINDEX INDEX maintain_test_a_idx;
19031912
REINDEX SCHEMA reindex_test;
1904-
BEGIN;
1905-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
1906-
COMMIT;
1907-
BEGIN;
1908-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
1909-
COMMIT;
19101913
RESET ROLE;
19111914

19121915
SET ROLE regress_maintain_all;
@@ -1918,12 +1921,6 @@ REFRESH MATERIALIZED VIEW refresh_test;
19181921
REINDEX TABLE maintain_test;
19191922
REINDEX INDEX maintain_test_a_idx;
19201923
REINDEX SCHEMA reindex_test;
1921-
BEGIN;
1922-
LOCK TABLE maintain_test IN ACCESS SHARE MODE;
1923-
COMMIT;
1924-
BEGIN;
1925-
LOCK TABLE maintain_test IN ACCESS EXCLUSIVE MODE;
1926-
COMMIT;
19271924
RESET ROLE;
19281925

19291926
DROP TABLE maintain_test;

0 commit comments

Comments
 (0)