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

From 1080d492c7b2d813874790ea8351fbadf16d681f Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
Date: Thu, 5 Feb 2026 09:08:57 +0000
Subject: [PATCH v3] 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             |  5 ++-
 src/backend/catalog/aclchk.c             | 11 +++++
 src/test/regress/expected/privileges.out | 55 +++++++++++++++++++++++-
 src/test/regress/sql/privileges.sql      | 27 +++++++++++-
 4 files changed, 93 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index ed18704a9c2..b57f6e16f78 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -713,8 +713,9 @@ 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
-       those objects and <literal>USAGE</literal> rights on all schemas.  This
+       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
        roles which this role is granted to.
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 daafaa94fde..03714cea7a6 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2149,6 +2149,57 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
            0
 (1 row)
 
+\c -
+-- confirm pg_read_all_data implies read access to large objects
+SELECT lowrite(lo_open(1002, x'20000'::int), 'hello world');
+ lowrite 
+---------
+      11
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT has_largeobject_privilege(1002, 'SELECT'); -- true
+ has_largeobject_privilege 
+---------------------------
+ t
+(1 row)
+
+SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false
+ has_largeobject_privilege 
+---------------------------
+ f
+(1 row)
+
+SELECT lo_get(1002); -- ok
+          lo_get          
+--------------------------
+ \x68656c6c6f20776f726c64
+(1 row)
+
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- fail
+ERROR:  permission denied for large object 1002
+do $$
+declare
+  fd int;
+begin
+  fd := lo_open(1002, x'40000'::int);
+  perform lo_lseek(fd, 6, 0);
+  raise notice 'position after lseek: %', lo_tell(fd);
+  raise notice 'data read: %', loread(fd, 5);
+  raise notice 'position after loread: %', lo_tell(fd);
+  perform lo_close(fd);
+end;
+$$;
+NOTICE:  position after lseek: 6
+NOTICE:  data read: \x776f726c64
+NOTICE:  position after loread: 11
+\c -
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 0);	-- ok
+ lo_truncate 
+-------------
+           0
+(1 row)
+
 -- has_largeobject_privilege function
 -- superuser
 \c -
@@ -2701,10 +2752,10 @@ 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 
 ---------------------------
- f
+ t
 (1 row)
 
 SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 96eff1104d2..a368ec85506 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1366,6 +1366,31 @@ 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 -
+-- confirm pg_read_all_data implies read access to large objects
+SELECT lowrite(lo_open(1002, x'20000'::int), 'hello world');
+
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT has_largeobject_privilege(1002, 'SELECT'); -- true
+SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false
+SELECT lo_get(1002); -- ok
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- fail
+do $$
+declare
+  fd int;
+begin
+  fd := lo_open(1002, x'40000'::int);
+  perform lo_lseek(fd, 6, 0);
+  raise notice 'position after lseek: %', lo_tell(fd);
+  raise notice 'data read: %', loread(fd, 5);
+  raise notice 'position after loread: %', lo_tell(fd);
+  perform lo_close(fd);
+end;
+$$;
+
+\c -
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 0);	-- ok
+
 -- has_largeobject_privilege function
 
 -- superuser
@@ -1601,7 +1626,7 @@ 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_user2', 1008, 'UPDATE'); -- no
 
 ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
-- 
2.53.0.rc2.204.g2597b5adb4-goog