Greetings,
There's no shortage of requests and responses regarding how to have a
'read all of the data' role in PG, with various hacks involving "GRANT
ALL" and "ALTER DEFAULT PRIVILEGES" to "solve" this, neither of which
really works long term ("GRANT ALL" is one-time, and "ALTER DEFAULT"
only helps for the roles that exist today).Now that we have the default role system, we can provide a proper solution to this oft-requested capability. This patch adds a default role to meet specifically that use-case, in the long-term, by explicitly allowing SELECT rights on all relations, and USAGE rights on all schemas, for roles who are members of the new 'pg_read_all_data' role. No effort is made to prevent a user who has this role from writing data- that's up to the admin, but this will allow someone to use pg_dump or pg_dumpall in a much more reliable manner to make sure that the entire database is able to be exported for the purpose of backups, upgrades, or other common use-cases, without having to have that same user be a PG superuser. This role is given the Bypass RLS right, though to use it effectively, a user would need to pass '--role=pg_read_all_data' to pg_dump/pg_dumpall, since role attributes are not checked as part of role membership. Thoughts? Will add to the September CF. Thanks, Stephen
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 829decd883..09b3cd6cb8 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -517,6 +517,12 @@ DROP ROLE doomed_role;
</row>
</thead>
<tbody>
+ <row>
+ <entry>pg_read_all_data</entry>
+ <entry>Read all data (tables, sequences), even without having explicit
+ GRANT rights to the object. Implicitly includes USAGE rights on all
+ schemas.</entry>
+ </row>
<row>
<entry>pg_read_all_settings</entry>
<entry>Read all configuration variables, even those normally visible only to
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index c626161408..aca1deeca8 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3849,6 +3849,15 @@ pg_class_aclmask(Oid table_oid, Oid roleid,
ReleaseSysCache(tuple);
+ /*
+ * Check if ACL_SELECT is being checked and, if so, and not set already
+ * as part of the result, then check if the user is a member of the
+ * pg_read_all_data role, which allows read access to all relations.
+ */
+ if (mask & ACL_SELECT && !(result & ACL_SELECT) &&
+ has_privs_of_role(roleid, DEFAULT_ROLE_READ_ALL_DATA))
+ result |= ACL_SELECT;
+
return result;
}
@@ -4175,6 +4184,14 @@ pg_namespace_aclmask(Oid nsp_oid, Oid roleid,
ReleaseSysCache(tuple);
+ /*
+ * Check if ACL_USAGE is being checked and, if so, and not set already
+ * as part of the result, then check if the user is a member of the
+ * pg_read_all_data role, which allows usage access to all schemas.
+ */
+ if (mask & ACL_USAGE && !(result & ACL_USAGE) &&
+ has_privs_of_role(roleid, DEFAULT_ROLE_READ_ALL_DATA))
+ result |= ACL_USAGE;
return result;
}
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 7c08851550..0dc4b2baec 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -25,6 +25,11 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '3435', oid_symbol => 'DEFAULT_ROLE_READ_ALL_DATA',
+ rolname => 'pg_read_all_data', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 't', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
{ oid => '3374', oid_symbol => 'DEFAULT_ROLE_READ_ALL_SETTINGS',
rolname => 'pg_read_all_settings', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 3ec22c20ea..411525bcd1 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -24,8 +24,10 @@ CREATE USER regress_priv_user2;
CREATE USER regress_priv_user3;
CREATE USER regress_priv_user4;
CREATE USER regress_priv_user5;
+CREATE USER regress_priv_user6;
CREATE USER regress_priv_user5; -- duplicate
ERROR: role "regress_priv_user5" already exists
+GRANT pg_read_all_data TO regress_priv_user6;
CREATE GROUP regress_priv_group1;
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
@@ -129,6 +131,21 @@ SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
------+------
(0 rows)
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT * FROM atest1; -- ok
+ a | b
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2
+------+------
+(0 rows)
+
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR: permission denied for table atest2
SET SESSION AUTHORIZATION regress_priv_user3;
SELECT session_user, current_user;
session_user | current_user
@@ -1674,6 +1691,12 @@ SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
t
(1 row)
+SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
+ has_schema_privilege
+----------------------
+ t
+(1 row)
+
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
has_schema_privilege
----------------------
@@ -2045,7 +2068,8 @@ DROP USER regress_priv_user3;
DROP USER regress_priv_user4;
DROP USER regress_priv_user5;
DROP USER regress_priv_user6;
-ERROR: role "regress_priv_user6" does not exist
+DROP USER regress_priv_user7; -- does not exist
+ERROR: role "regress_priv_user7" does not exist
-- permissions with LOCK TABLE
CREATE USER regress_locktable_user;
CREATE TABLE lock_table (a int);
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 3550f61587..74f6f62c33 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -28,8 +28,11 @@ CREATE USER regress_priv_user2;
CREATE USER regress_priv_user3;
CREATE USER regress_priv_user4;
CREATE USER regress_priv_user5;
+CREATE USER regress_priv_user6;
CREATE USER regress_priv_user5; -- duplicate
+GRANT pg_read_all_data TO regress_priv_user6;
+
CREATE GROUP regress_priv_group1;
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
@@ -94,6 +97,10 @@ GRANT ALL ON atest1 TO PUBLIC; -- fail
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT * FROM atest1; -- ok
+SELECT * FROM atest2; -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
SET SESSION AUTHORIZATION regress_priv_user3;
SELECT session_user, current_user;
@@ -988,6 +995,7 @@ ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
CREATE SCHEMA testns2;
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
+SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
@@ -1211,6 +1219,7 @@ DROP USER regress_priv_user3;
DROP USER regress_priv_user4;
DROP USER regress_priv_user5;
DROP USER regress_priv_user6;
+DROP USER regress_priv_user7; -- does not exist
-- permissions with LOCK TABLE
signature.asc
Description: PGP signature
