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

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

* The large objects aclchk doesn't check for pg_read_all_data.
  This patch adds that support.

* Currently pg_read_all_data is able to run 'select' on pg_largeobject
  table but not able to run functions like lo_put. This change
  makes that behaviour consistent.

* We don't add support for pg_write_all_data to update large objects
  as that would end up providing write access to a system catalog
  which we currently don't provide to pg_write_all_data.

* The patch also adds regress tests for loread, lo_get, lo_lseek, and lo_tell.

* The doc for pg_read_all_data is updated to include large objects access.
---
 doc/src/sgml/user-manag.sgml             |  5 +-
 src/backend/catalog/aclchk.c             | 11 ++++
 src/test/regress/expected/privileges.out | 73 +++++++++++++++++++++++-
 src/test/regress/sql/privileges.sql      | 30 +++++++++-
 4 files changed, 114 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..160f85ec5b2 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2149,6 +2149,75 @@ 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 loread(lo_open(1003, x'40000'::int), 32); -- ok
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT lo_get(1002); -- ok
+          lo_get          
+--------------------------
+ \x68656c6c6f20776f726c64
+(1 row)
+
+SELECT lo_get(1002, 6, 5); -- ok
+    lo_get    
+--------------
+ \x776f726c64
+(1 row)
+
+SELECT loread(lo_open(1002, x'40000'::int), 11); -- ok
+          loread          
+--------------------------
+ \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 +2770,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..d4785cab131 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1366,6 +1366,34 @@ 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 loread(lo_open(1003, x'40000'::int), 32); -- ok
+SELECT lo_get(1002); -- ok
+SELECT lo_get(1002, 6, 5); -- ok
+SELECT loread(lo_open(1002, x'40000'::int), 11); -- 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 +1629,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