Date: Thu, 27 Aug 2020 20:30:23 -0400
From: Stephen Frost <sfrost@snowman.net>
To: pgsql-hackers@lists.postgresql.org
Subject: New default role- 'pg_read_all_data'
Message-ID: <20200828003023.GU29590@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="xKpirsHfz/KOvdg1"
Content-Disposition: inline
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--xKpirsHfz/KOvdg1
Content-Type: multipart/mixed; boundary="3I+10VLXuUqsCehj"
Content-Disposition: inline


--3I+10VLXuUqsCehj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--3I+10VLXuUqsCehj
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="pg_rorole_v1.patch"
Content-Transfer-Encoding: quoted-printable

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 expli=
cit
+       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 visibl=
e 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,
=20
 	ReleaseSysCache(tuple);
=20
+	/*
+	 * 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 |=3D ACL_SELECT;
+
 	return result;
 }
=20
@@ -4175,6 +4184,14 @@ pg_namespace_aclmask(Oid nsp_oid, Oid roleid,
=20
 	ReleaseSysCache(tuple);
=20
+	/*
+	 * 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 |=3D ACL_USAGE;
 	return result;
 }
=20
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_aut=
hid.dat
index 7c08851550..0dc4b2baec 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -25,6 +25,11 @@
   rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f',
   rolreplication =3D> 'f', rolbypassrls =3D> 'f', rolconnlimit =3D> '-1',
   rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' },
+{ oid =3D> '3435', oid_symbol =3D> 'DEFAULT_ROLE_READ_ALL_DATA',
+  rolname =3D> 'pg_read_all_data', rolsuper =3D> 'f', rolinherit =3D> 't',
+  rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f',
+  rolreplication =3D> 'f', rolbypassrls =3D> 't', rolconnlimit =3D> '-1',
+  rolpassword =3D> '_null_', rolvaliduntil =3D> '_null_' },
 { oid =3D> '3374', oid_symbol =3D> 'DEFAULT_ROLE_READ_ALL_SETTINGS',
   rolname =3D> 'pg_read_all_settings', rolsuper =3D> 'f', rolinherit =3D> =
't',
   rolcreaterole =3D> 'f', rolcreatedb =3D> 'f', rolcanlogin =3D> 'f',
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/ex=
pected/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_pri=
v_user2;
 ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
@@ -129,6 +131,21 @@ SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM a=
test1 ) );
 ------+------
 (0 rows)
=20
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT * FROM atest1; -- ok
+ a |  b =20
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2=20
+------+------
+(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   =20
@@ -1674,6 +1691,12 @@ SELECT has_schema_privilege('regress_priv_user2', 't=
estns2', 'USAGE'); -- yes
  t
 (1 row)
=20
+SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- =
yes
+ has_schema_privilege=20
+----------------------
+ t
+(1 row)
+
 SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); --=
 no
  has_schema_privilege=20
 ----------------------
@@ -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/pri=
vileges.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
=20
+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_pri=
v_user2;
=20
@@ -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 ) );
=20
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT * FROM atest1; -- ok
+SELECT * FROM atest2; -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
=20
 SET SESSION AUTHORIZATION regress_priv_user3;
 SELECT session_user, current_user;
@@ -988,6 +995,7 @@ ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regr=
ess_priv_user2;
 CREATE SCHEMA testns2;
=20
 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
=20
 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
=20
=20
 -- permissions with LOCK TABLE

--3I+10VLXuUqsCehj--

--xKpirsHfz/KOvdg1
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJfSFAeAAoJEO1sijiDR2RVTs8P/j6tIkI+xSAfj6UkcyDt8KdL
zeSdiJg9EDKid0eVWA2DQPbgmOdfR2ut3tHocTsh8Abt2TdGbEvUYzgzXfi9dQ6a
HwUICwA4FwuV9aT2iTEETm16C+UynfuuExA7zdDhEBmArOMt9nKMKphcju7L1y4f
qzhl3AZtnsvzvaXnV/SNmWmVu1mwvhh03skmhJNVvBRskhnvOu7X3moxp3CMSCYE
41p5CDmwNCWLNsAsb9cnlFLKxJMQVHSr9eZYzowocl5UHNlEZ1rnJFModmK1+p8S
i5NJRsfsafBhT9D4UbVBo6DBiXzLAhl8KZeeKxn16CIy1PtrBCX8zM8cm86Mt/Zc
WY8Kzg2aY2yPNpradN/qWjSttEXFf1MOUTU4/9Wk3yXjgJXwtuRh7grHVzr0B9CC
pIG8xD/Df+RFhDE5VwnyucJ6lmj+NktMHeB2gEPQynud2IQmC/ijInEH5YU1rEC1
jqIvENNUgkzYqm9JnDD/FIXv6VRPM5FWc5yB/x8HqC6v6fY7IvUIs13Ys5VbXWJb
jxKktA6ToVFK5SUc1o5lUbRq95F2xEmr8rARhXTisp9io357yYKo+QB9Q4wQ95ke
PAd75iclAKIK0dooKlQyzIHqiXk7taAxwxHrFTpD8vMH8B9zQLfzmTb2IaJdBObb
VEfCrDmO+5TN4NL96DU7
=VRAP
-----END PGP SIGNATURE-----

--xKpirsHfz/KOvdg1--