On Tue, Feb 10, 2026 at 2:17 PM Dilip Kumar <[email protected]> wrote: > > Hi Nitin, Your patch looks good to me except for some minor > suggestions/questions. >
Thanks Dilip for the feedback. > 1. I think we can change the commit message slightly, and also removed > the part which says added doc/test > Suggestion: > Support large object functions with pg_read_all_data > I updated the commit message according to this suggestion. > Isn't it sufficient to just have second lo_get test i.e. SELECT > lo_get(1002, 6, 5);, is there anything extra we are checking with the > first test or is it just testing the same? > > Check other tests as well for loread(), seems there are multiple > loread() tests that are testing the same functionality? > I have removed the redundant tests in the latest patch. The original rationale was to test these functions with different arguments and empty objects. But on reflection those are unrelated to the acl check. So I'm only keeping one test per function. Regards, Nitin Motiani Google
From 1080d492c7b2d813874790ea8351fbadf16d681f Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> 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
