Thanks Nathan for the feedback. On Fri, Feb 13, 2026 at 2:51 AM Nathan Bossart <[email protected]> wrote: > > Thanks for the new patch. Please create a commitfest entry so that this > patch 1) isn't forgotten and 2) gets tested by cfbot. >
I have created the commitfest entry https://commitfest.postgresql.org/patch/6485/. > nitpick: I usually try to make small doc updates in a way that doesn't > disturb the surrounding lines so that we retain as much git history as > possible. > Changed the formatting in the doc. > > I'd suggest simplifying this a bit by borrowing some lines from the > surrounding tests. Changed the test based on your suggestions. Attaching the latest patch. Regards, Nitin Motiani Google
From 7943be732780b56e0c693986a3eabcb87023ed95 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> 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
