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

Commit6c3ffd6

Browse files
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 anunfettered pg_dump without having to explicitly GRANT that user accessto all tables, schemas, et al, without that role being a superuser.This address that by adding a "pg_read_all_data" role which implicitlygives any member of this role SELECT rights on all tables, views andsequences, and USAGE rights on all schemas.As there may be cases where it's also useful to have a role who haswrite access to all objects, pg_write_all_data is also introduced andgives users implicit INSERT, UPDATE and DELETE rights on all tables,views and sequences.These roles can not be logged into directly but instead should beGRANT'd to a role which is able to log in. As noted in thedocumentation, if RLS is being used then an administrator may (or maynot) wish to set BYPASSRLS on the login role which these predefinedroles are GRANT'd to.Reviewed-by: Georgios KokolatosDiscussion:https://postgr.es/m/20200828003023.GU29590@tamriel.snowman.net
1 parentad8b674 commit6c3ffd6

File tree

6 files changed

+129
-2
lines changed

6 files changed

+129
-2
lines changed

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

Lines changed: 18 additions & 0 deletions
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

Lines changed: 31 additions & 0 deletions
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
returnresult;
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
returnresult;
42554286
}
42564287

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202104011
56+
#defineCATALOG_VERSION_NO202104051
5757

5858
#endif

‎src/include/catalog/pg_authid.dat

Lines changed: 10 additions & 0 deletions
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

Lines changed: 44 additions & 1 deletion
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

Lines changed: 25 additions & 0 deletions
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_metadataWHEREoid>=1000ANDoid<3000ORDER BYoid;
2122

@@ -29,6 +30,11 @@ CREATE USER regress_priv_user3;
2930
CREATEUSERregress_priv_user4;
3031
CREATEUSERregress_priv_user5;
3132
CREATEUSERregress_priv_user5;-- duplicate
33+
CREATEUSERregress_priv_user6;
34+
CREATEUSERregress_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
CREATEGROUPregress_priv_group1;
3440
CREATEGROUPregress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
@@ -96,6 +102,22 @@ GRANT ALL ON atest1 TO PUBLIC; -- fail
96102
SELECT*FROM atest1WHERE ( bIN (SELECT col1FROM atest2 ) );
97103
SELECT*FROM atest2WHERE ( col1IN (SELECT bFROM atest1 ) );
98104

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

100122
SET SESSION AUTHORIZATION regress_priv_user3;
101123
SELECTsession_user,current_user;
@@ -1121,6 +1143,7 @@ ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
11211143
CREATESCHEMAtestns2;
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 PRIVILEGESREVOKE USAGEON SCHEMASFROM regress_priv_user2;
@@ -1364,6 +1387,8 @@ DROP USER regress_priv_user3;
13641387
DROPUSER regress_priv_user4;
13651388
DROPUSER regress_priv_user5;
13661389
DROPUSER regress_priv_user6;
1390+
DROPUSER regress_priv_user7;
1391+
DROPUSER regress_priv_user8;-- does not exist
13671392

13681393

13691394
-- permissions with LOCK TABLE

0 commit comments

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