Skip to content

Commit 6c3ffd6

Browse files
sfrostsfrost
committed
Add pg_read_all_data and pg_write_all_data roles
A commonly requested use-case is to have a role who can run an unfettered pg_dump without having to explicitly GRANT that user access to all tables, schemas, et al, without that role being a superuser. This address that by adding a "pg_read_all_data" role which implicitly gives any member of this role SELECT rights on all tables, views and sequences, and USAGE rights on all schemas. As there may be cases where it's also useful to have a role who has write access to all objects, pg_write_all_data is also introduced and gives users implicit INSERT, UPDATE and DELETE rights on all tables, views and sequences. These roles can not be logged into directly but instead should be GRANT'd to a role which is able to log in. As noted in the documentation, if RLS is being used then an administrator may (or may not) wish to set BYPASSRLS on the login role which these predefined roles are GRANT'd to. Reviewed-by: Georgios Kokolatos Discussion: https://postgr.es/m/20200828003023.GU29590@tamriel.snowman.net
1 parent ad8b674 commit 6c3ffd6

File tree

6 files changed

+129
-2
lines changed

6 files changed

+129
-2
lines changed

‎doc/src/sgml/user-manag.sgml

+18
Original file line numberDiff line numberDiff line change
@@ -518,6 +518,24 @@ DROP ROLE doomed_role;
518518
</row>
519519
</thead>
520520
<tbody>
521+
<row>
522+
<entry>pg_read_all_data</entry>
523+
<entry>Read all data (tables, views, sequences), as if having SELECT
524+
rights on those objects, and USAGE rights on all schemas, even without
525+
having it explicitly. This role does not have the role attribute
526+
<literal>BYPASSRLS</literal> set. If RLS is being used, an administrator
527+
may wish to set <literal>BYPASSRLS</literal> on roles which this role is
528+
GRANTed to.</entry>
529+
</row>
530+
<row>
531+
<entry>pg_write_all_data</entry>
532+
<entry>Write all data (tables, views, sequences), as if having INSERT,
533+
UPDATE, and DELETE rights on those objects, and USAGE rights on all
534+
schemas, even without having it explicitly. This role does not have the
535+
role attribute <literal>BYPASSRLS</literal> set. If RLS is being used,
536+
an administrator may wish to set <literal>BYPASSRLS</literal> on roles
537+
which this role is GRANTed to.</entry>
538+
</row>
521539
<row>
522540
<entry>pg_read_all_settings</entry>
523541
<entry>Read all configuration variables, even those normally visible only to

‎src/backend/catalog/aclchk.c

+31
Original file line numberDiff line numberDiff line change
@@ -3925,6 +3925,27 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
39253925

39263926
ReleaseSysCache(tuple);
39273927

3928+
/*
3929+
* Check if ACL_SELECT is being checked and, if so, and not set already
3930+
* as part of the result, then check if the user is a member of the
3931+
* pg_read_all_data role, which allows read access to all relations.
3932+
*/
3933+
if (mask & ACL_SELECT && !(result & ACL_SELECT) &&
3934+
has_privs_of_role(roleid, ROLE_READ_ALL_DATA))
3935+
result |= ACL_SELECT;
3936+
3937+
/*
3938+
* Check if ACL_INSERT, ACL_UPDATE, or ACL_DELETE is being checked
3939+
* and, if so, and not set already as part of the result, then check
3940+
* if the user is a member of the pg_write_all_data role, which
3941+
* allows INSERT/UPDATE/DELETE access to all relations (except
3942+
* system catalogs, which requires superuser, see above).
3943+
*/
3944+
if (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE) &&
3945+
!(result & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
3946+
has_privs_of_role(roleid, ROLE_WRITE_ALL_DATA))
3947+
result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
3948+
39283949
return result;
39293950
}
39303951

@@ -4251,6 +4272,16 @@ pg_namespace_aclmask(Oid nsp_oid, Oid roleid,
42514272

42524273
ReleaseSysCache(tuple);
42534274

4275+
/*
4276+
* Check if ACL_USAGE is being checked and, if so, and not set already
4277+
* as part of the result, then check if the user is a member of the
4278+
* pg_read_all_data or pg_write_all_data roles, which allow usage
4279+
* access to all schemas.
4280+
*/
4281+
if (mask & ACL_USAGE && !(result & ACL_USAGE) &&
4282+
(has_privs_of_role(roleid, ROLE_READ_ALL_DATA) ||
4283+
has_privs_of_role(roleid, ROLE_WRITE_ALL_DATA)))
4284+
result |= ACL_USAGE;
42544285
return result;
42554286
}
42564287

‎src/include/catalog/catversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202104011
56+
#define CATALOG_VERSION_NO 202104051
5757

5858
#endif

‎src/include/catalog/pg_authid.dat

+10
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,16 @@
2929
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
3030
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
3131
rolpassword => '_null_', rolvaliduntil => '_null_' },
32+
{ oid => '9274', oid_symbol => 'ROLE_READ_ALL_DATA',
33+
rolname => 'pg_read_all_data', rolsuper => 'f', rolinherit => 't',
34+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
35+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
36+
rolpassword => '_null_', rolvaliduntil => '_null_' },
37+
{ oid => '9275', oid_symbol => 'ROLE_WRITE_ALL_DATA',
38+
rolname => 'pg_write_all_data', rolsuper => 'f', rolinherit => 't',
39+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
40+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
41+
rolpassword => '_null_', rolvaliduntil => '_null_' },
3242
{ oid => '3373', oid_symbol => 'ROLE_PG_MONITOR',
3343
rolname => 'pg_monitor', rolsuper => 'f', rolinherit => 't',
3444
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',

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

+44-1
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ DROP ROLE IF EXISTS regress_priv_user3;
1212
DROP ROLE IF EXISTS regress_priv_user4;
1313
DROP ROLE IF EXISTS regress_priv_user5;
1414
DROP ROLE IF EXISTS regress_priv_user6;
15+
DROP ROLE IF EXISTS regress_priv_user7;
1516
SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
1617
lo_unlink
1718
-----------
@@ -26,6 +27,10 @@ CREATE USER regress_priv_user4;
2627
CREATE USER regress_priv_user5;
2728
CREATE USER regress_priv_user5; -- duplicate
2829
ERROR: role "regress_priv_user5" already exists
30+
CREATE USER regress_priv_user6;
31+
CREATE USER regress_priv_user7;
32+
GRANT pg_read_all_data TO regress_priv_user6;
33+
GRANT pg_write_all_data TO regress_priv_user7;
2934
CREATE GROUP regress_priv_group1;
3035
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
3136
ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
@@ -131,6 +136,36 @@ SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
131136
------+------
132137
(0 rows)
133138

139+
SET SESSION AUTHORIZATION regress_priv_user6;
140+
SELECT * FROM atest1; -- ok
141+
a | b
142+
---+-----
143+
1 | two
144+
1 | two
145+
(2 rows)
146+
147+
SELECT * FROM atest2; -- ok
148+
col1 | col2
149+
------+------
150+
(0 rows)
151+
152+
INSERT INTO atest2 VALUES ('foo', true); -- fail
153+
ERROR: permission denied for table atest2
154+
SET SESSION AUTHORIZATION regress_priv_user7;
155+
SELECT * FROM atest1; -- fail
156+
ERROR: permission denied for table atest1
157+
SELECT * FROM atest2; -- fail
158+
ERROR: permission denied for table atest2
159+
INSERT INTO atest2 VALUES ('foo', true); -- ok
160+
UPDATE atest2 SET col2 = true; -- ok
161+
DELETE FROM atest2; -- ok
162+
-- Make sure we are not able to modify system catalogs
163+
UPDATE pg_catalog.pg_class SET relname = '123'; -- fail
164+
ERROR: permission denied for table pg_class
165+
DELETE FROM pg_catalog.pg_class; -- fail
166+
ERROR: permission denied for table pg_class
167+
UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail
168+
ERROR: permission denied for table pg_toast_1213
134169
SET SESSION AUTHORIZATION regress_priv_user3;
135170
SELECT session_user, current_user;
136171
session_user | current_user
@@ -1884,6 +1919,12 @@ SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
18841919
t
18851920
(1 row)
18861921

1922+
SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
1923+
has_schema_privilege
1924+
----------------------
1925+
t
1926+
(1 row)
1927+
18871928
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
18881929
has_schema_privilege
18891930
----------------------
@@ -2284,7 +2325,9 @@ DROP USER regress_priv_user3;
22842325
DROP USER regress_priv_user4;
22852326
DROP USER regress_priv_user5;
22862327
DROP USER regress_priv_user6;
2287-
ERROR: role "regress_priv_user6" does not exist
2328+
DROP USER regress_priv_user7;
2329+
DROP USER regress_priv_user8; -- does not exist
2330+
ERROR: role "regress_priv_user8" does not exist
22882331
-- permissions with LOCK TABLE
22892332
CREATE USER regress_locktable_user;
22902333
CREATE TABLE lock_table (a int);

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

+25
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ DROP ROLE IF EXISTS regress_priv_user3;
1616
DROP ROLE IF EXISTS regress_priv_user4;
1717
DROP ROLE IF EXISTS regress_priv_user5;
1818
DROP ROLE IF EXISTS regress_priv_user6;
19+
DROP ROLE IF EXISTS regress_priv_user7;
1920

2021
SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
2122

@@ -29,6 +30,11 @@ CREATE USER regress_priv_user3;
2930
CREATE USER regress_priv_user4;
3031
CREATE USER regress_priv_user5;
3132
CREATE USER regress_priv_user5; -- duplicate
33+
CREATE USER regress_priv_user6;
34+
CREATE USER regress_priv_user7;
35+
36+
GRANT pg_read_all_data TO regress_priv_user6;
37+
GRANT pg_write_all_data TO regress_priv_user7;
3238

3339
CREATE GROUP regress_priv_group1;
3440
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
@@ -96,6 +102,22 @@ GRANT ALL ON atest1 TO PUBLIC; -- fail
96102
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
97103
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
98104

105+
SET SESSION AUTHORIZATION regress_priv_user6;
106+
SELECT * FROM atest1; -- ok
107+
SELECT * FROM atest2; -- ok
108+
INSERT INTO atest2 VALUES ('foo', true); -- fail
109+
110+
SET SESSION AUTHORIZATION regress_priv_user7;
111+
SELECT * FROM atest1; -- fail
112+
SELECT * FROM atest2; -- fail
113+
INSERT INTO atest2 VALUES ('foo', true); -- ok
114+
UPDATE atest2 SET col2 = true; -- ok
115+
DELETE FROM atest2; -- ok
116+
117+
-- Make sure we are not able to modify system catalogs
118+
UPDATE pg_catalog.pg_class SET relname = '123'; -- fail
119+
DELETE FROM pg_catalog.pg_class; -- fail
120+
UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail
99121

100122
SET SESSION AUTHORIZATION regress_priv_user3;
101123
SELECT session_user, current_user;
@@ -1121,6 +1143,7 @@ ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
11211143
CREATE SCHEMA testns2;
11221144

11231145
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
1146+
SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
11241147
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
11251148

11261149
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
@@ -1364,6 +1387,8 @@ DROP USER regress_priv_user3;
13641387
DROP USER regress_priv_user4;
13651388
DROP USER regress_priv_user5;
13661389
DROP USER regress_priv_user6;
1390+
DROP USER regress_priv_user7;
1391+
DROP USER regress_priv_user8; -- does not exist
13671392

13681393

13691394
-- permissions with LOCK TABLE

0 commit comments

Comments
 (0)