From: Nathan Bossart <nathandbossart@gmail.com>
To: Nitin Motiani <nitinmotiani@google.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCH] Support reading large objects with pg_read_all_data
Date: Mon, 23 Feb 2026 11:07:21 -0600
Message-ID: <aZyJSW9pZ3c3i0FG@nathan> (raw)
In-Reply-To: <aY9cqDhR2UB-d1oW@nathan>
References: <CAH5HC96dxAEvP78s1-JK_nDABH5c4w2MDfyx4vEWxBEfofGWsw@mail.gmail.com>
<aYS68rra7T4r8w2M@nathan>
<CAH5HC97B=bm+XHqQL2UO7aUNTqnkgC1vyLrJwjG8V2gDjZjOXA@mail.gmail.com>
<CAFiTN-uS4j--2md37JoArziGG9onBMUPw4e6kx4GXDWujQcb3Q@mail.gmail.com>
<CAH5HC97AJmSGqrusJLcBU0evubhm+Q139PdAhwdr2c_=D9r2ZA@mail.gmail.com>
<aY5EcqjJjmeNLG4Y@nathan>
<CAH5HC97zMa-ku8B-J4BDSedyad5FT3uRqPLYwnXPHgOWfeGW-A@mail.gmail.com>
<aY9cqDhR2UB-d1oW@nathan>
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
Attachments:v5-0001-Allow-pg_-read-write-_all_data-to-access-large-ob.patch (text/plain, 7.1K) download
From fc9378f93191ee6579f95bed6034304516f15a8b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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 <nitinmotiani@google.com>
Co-authored-by: Nathan Bossart <nathandbossart@gmail.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
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.sgmlindex 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.cindex 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.outindex 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.sqlindex 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, nitinmotiani@google.com, dilipbalaut@gmail.com
Subject: Re: [PATCH] Support reading large objects with pg_read_all_data
In-Reply-To: <aZyJSW9pZ3c3i0FG@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox