v4-0001-Support-large-object-functions-with-pg_read_all_d.patch

From 7943be732780b56e0c693986a3eabcb87023ed95 Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
Date: Thu, 5 Feb 2026 09:08:57 +0000
Subject: [PATCH v4] Support large object functions with pg_read_all_data

Allow members of the pg_read_all_data predefined role to access large
objects via the large object functional API (e.g., lo_get, loread).

Previously, while pg_read_all_data permitted direct SELECT queries
on the pg_largeobject catalog table, it did not grant the necessary
permissions to use the logical large object functions. This created an
inconsistency in how the role accessed data stored in large objects
versus standard relations.

This change updates the ACL check for large objects to recognize
pg_read_all_data as having SELECT privileges. Note that this
support is intentionally omitted for pg_write_all_data, as granting
write access would imply write permissions on a system catalog, a
privilege level that pg_write_all_data does not currently provide
for other objects.
---
 doc/src/sgml/user-manag.sgml             |  2 +-
 src/backend/catalog/aclchk.c             | 11 ++++++
 src/test/regress/expected/privileges.out | 44 +++++++++++++++++++++++-
 src/test/regress/sql/privileges.sql      | 21 ++++++++++-
 4 files changed, 75 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index ed18704a9c2..b268b7b046a 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -713,7 +713,7 @@ GRANT pg_signal_backend TO admin_user;
      <listitem>
       <para>
        <literal>pg_read_all_data</literal> allows reading all data (tables,
-       views, sequences), as if having <command>SELECT</command> rights on
+       views, sequences, and large objects), as if having <command>SELECT</command> rights on
        those objects and <literal>USAGE</literal> rights on all schemas.  This
        role does not bypass row-level security (RLS) policies.  If RLS is being
        used, an administrator may wish to set <literal>BYPASSRLS</literal> on
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index a431fc0926f..36d9207672f 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3598,6 +3598,17 @@ pg_largeobject_aclmask_snapshot(Oid lobj_oid, Oid roleid,
 
 	table_close(pg_lo_meta, AccessShareLock);
 
+	/*
+	 * 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. We
+	 * don't provide any write access to PG_WRITE_ALL_DATA as that would be
+	 * equivalent to providing write access to a system catalog.
+	 */
+	if (mask & ACL_SELECT && !(result & ACL_SELECT) &&
+		has_privs_of_role(roleid, ROLE_PG_READ_ALL_DATA))
+		result |= ACL_SELECT;
+
 	return result;
 }
 
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 84c1c1ca38d..f07b118f943 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2175,6 +2175,42 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
            0
 (1 row)
 
+\c -
+SELECT lowrite(lo_open(1002, x'20000'::int), 'hello world');
+ lowrite 
+---------
+      11
+(1 row)
+
+-- pg_read_all_data implies read access to large objects
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT loread(lo_open(1002, x'40000'::int), 32); -- ok
+          loread          
+--------------------------
+ \x68656c6c6f20776f726c64
+(1 row)
+
+SELECT lo_get(1002); -- ok
+          lo_get          
+--------------------------
+ \x68656c6c6f20776f726c64
+(1 row)
+
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');   -- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lo_unlink(1002);                                 -- to be denied
+ERROR:  must be owner of large object 1002
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);   -- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lo_put(1002, 1, 'abcd');                         -- to be denied
+ERROR:  permission denied for large object 1002
+RESET SESSION AUTHORIZATION;
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 0);	-- ok
+ lo_truncate 
+-------------
+           0
+(1 row)
+
 -- has_largeobject_privilege function
 -- superuser
 \c -
@@ -2727,7 +2763,13 @@ SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes
  t
 (1 row)
 
-SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no
+SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- yes
+ has_largeobject_privilege 
+---------------------------
+ t
+(1 row)
+
+SELECT has_largeobject_privilege('regress_priv_user3', 1008, 'SELECT'); -- no
  has_largeobject_privilege 
 ---------------------------
  f
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 66e06d91a41..cc1947ef7df 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1384,6 +1384,24 @@ SELECT loread(lo_open(1005, x'40000'::int), 32);
 SELECT lo_truncate(lo_open(1005, x'20000'::int), 10);	-- to be denied
 SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
 
+\c -
+
+SELECT lowrite(lo_open(1002, x'20000'::int), 'hello world');
+
+-- pg_read_all_data implies read access to large objects
+SET SESSION AUTHORIZATION regress_priv_user6;
+
+SELECT loread(lo_open(1002, x'40000'::int), 32); -- ok
+SELECT lo_get(1002); -- ok
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');   -- to be denied
+SELECT lo_unlink(1002);                                 -- to be denied
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);   -- to be denied
+SELECT lo_put(1002, 1, 'abcd');                         -- to be denied
+
+RESET SESSION AUTHORIZATION;
+
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 0);	-- ok
+
 -- has_largeobject_privilege function
 
 -- superuser
@@ -1619,7 +1637,8 @@ ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO regress_priv_user2;
 
 SELECT lo_create(1008);
 SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes
-SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no
+SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- yes
+SELECT has_largeobject_privilege('regress_priv_user3', 1008, 'SELECT'); -- no
 SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no
 
 ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
-- 
2.53.0.310.g728cabbaf7-goog