On Fri, Feb 13, 2026 at 11:17:28AM -0600, Nathan Bossart wrote: > This looks pretty good to me. I'd like to let it sit on the lists a little > while longer in case anyone else has feedback or objections. Assuming > those don't materialize in the next week or so, I will proceed with > committing it.
Here's what I have staged for commit. I didn't understand the reasoning behind not giving pg_write_all_data privileges on large objects. Your commit message mentions that "granting write access would imply write permissions on a system catalog" (which I assume is referring to pg_largeobject), but if granting UPDATE on a large object is sufficient to allow updating portions of that catalog, then I see no reason to be so strict with pg_write_all_data. It still doesn't allow updating the catalog directly. -- nathan
>From fc9378f93191ee6579f95bed6034304516f15a8b Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 23 Feb 2026 10:54:59 -0600 Subject: [PATCH v5 1/1] Allow pg_{read,write}_all_data to access large objects. Since the initial goal of pg_read_all_data was to be able to run pg_dump as a non-superuser without explicitly granting access to every object, it follows that it should allow reading all large objects, too. For consistency, pg_write_all_data should allow writing all large objects as well. Author: Nitin Motiani <[email protected]> Co-authored-by: Nathan Bossart <[email protected]> Reviewed-by: Dilip Kumar <[email protected]> Discussion: https://postgr.es/m/CAH5HC96dxAEvP78s1-JK_nDABH5c4w2MDfyx4vEWxBEfofGWsw%40mail.gmail.com --- doc/src/sgml/user-manag.sgml | 4 +- src/backend/catalog/aclchk.c | 18 +++++++++ src/test/regress/expected/privileges.out | 49 +++++++++++++++++++++++- src/test/regress/sql/privileges.sql | 23 ++++++++++- 4 files changed, 90 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index ed18704a9c2..0ec32700bd4 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, 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 @@ -721,7 +721,7 @@ GRANT pg_signal_backend TO admin_user; </para> <para> <literal>pg_write_all_data</literal> allows writing all data (tables, - views, sequences), as if having <command>INSERT</command>, + views, sequences, large objects), as if having <command>INSERT</command>, <command>UPDATE</command>, and <command>DELETE</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 diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index aef855abccc..8811d41df24 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3598,6 +3598,24 @@ 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 has privileges of the + * pg_read_all_data role, which allows read access to all large objects. + */ + if (mask & ACL_SELECT && !(result & ACL_SELECT) && + has_privs_of_role(roleid, ROLE_PG_READ_ALL_DATA)) + result |= ACL_SELECT; + + /* + * Check if ACL_UPDATE is being checked and, if so, and not set already as + * part of the result, then check if the user has privileges of the + * pg_write_all_data role, which allows write access to all large objects. + */ + if (mask & ACL_UPDATE && !(result & ACL_UPDATE) && + has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA)) + result |= ACL_UPDATE; + return result; } diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 84c1c1ca38d..7bc274566c3 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -2175,6 +2175,53 @@ SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 0 (1 row) +\c - +-- confirm role with privileges of pg_read_all_data can read large objects +SET SESSION AUTHORIZATION regress_priv_user6; +SELECT loread(lo_open(1002, x'40000'::int), 32); + loread +-------- + \x +(1 row) + +SELECT lo_get(1002); + lo_get +-------- + \x +(1 row) + +SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- 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 +SELECT lo_truncate(lo_open(1002, x'20000'::int), 0); -- 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 +\c - +-- confirm role with privileges of pg_write_all_data can write large objects +GRANT SELECT ON LARGE OBJECT 1002 TO regress_priv_user7; +SET SESSION AUTHORIZATION regress_priv_user7; +SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); + lowrite +--------- + 4 +(1 row) + +SELECT lo_put(1002, 1, 'abcd'); + lo_put +-------- + +(1 row) + +SELECT lo_truncate(lo_open(1002, x'20000'::int), 0); + lo_truncate +------------- + 0 +(1 row) + +SELECT lo_unlink(1002); -- to be denied +ERROR: must be owner of large object 1002 -- has_largeobject_privilege function -- superuser \c - @@ -2727,7 +2774,7 @@ 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_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..540f73ea9b1 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -1384,6 +1384,27 @@ 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 role with privileges of pg_read_all_data can read large objects +SET SESSION AUTHORIZATION regress_priv_user6; + +SELECT loread(lo_open(1002, x'40000'::int), 32); +SELECT lo_get(1002); +SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied +SELECT lo_put(1002, 1, 'abcd'); -- to be denied +SELECT lo_truncate(lo_open(1002, x'20000'::int), 0); -- to be denied +SELECT lo_unlink(1002); -- to be denied + +\c - +-- confirm role with privileges of pg_write_all_data can write large objects +GRANT SELECT ON LARGE OBJECT 1002 TO regress_priv_user7; +SET SESSION AUTHORIZATION regress_priv_user7; + +SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); +SELECT lo_put(1002, 1, 'abcd'); +SELECT lo_truncate(lo_open(1002, x'20000'::int), 0); +SELECT lo_unlink(1002); -- to be denied + -- has_largeobject_privilege function -- superuser @@ -1619,7 +1640,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_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.50.1 (Apple Git-155)
