On Thu, Feb 5, 2026 at 9:14 PM Nathan Bossart <[email protected]> wrote: > At a glance, it looks generally reasonable to me. In addition to updating > the documentation, I'd recommend adding tests. >
Thanks Nathan. I'm attaching the patch with new tests and updated documentation. Please take a look. Regards, Nitin Motiani Google
From 15577cb383c91b762fffb6a1299861569a7a58e4 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> 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
