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
Appearance settings

Commit c44f633

Browse filesBrowse 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
Copy full SHA for c44f633

File tree

Expand file treeCollapse file tree

4 files changed

+79
-91
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+79
-91
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/lock.sgml
+13-12Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/lockcmds.c
+9-9Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/expected/privileges.out
+28-38Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/privileges.sql
+29-32Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.