pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH] Support reading large objects with pg_read_all_data
11+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-02-23 d981976027 Allow pg_{read,write}_all_data to access large objects.
* [PATCH] Support reading large objects with pg_read_all_data
@ 2026-02-05 09:56 Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nitin Motiani @ 2026-02-05 09:56 UTC (permalink / raw)
To: pgsql-hackers
Hi Hackers,
It was reported in [1] that pg_dump for a user with pg_read_all_data
fails as pg_read_all_data doesn't have the permission to read large
objects. The discussion on the same thread suggested that this was an
oversight as the goal of pg_read_all_data was to allow running pg_dump
[2].
This patch proposes to fill that gap by modifying
pg_largeobject_aclmask_snapshot to provide ACL_SELECT for the role
PG_READ_ALL_DATA. Note that the patch doesn't make an equivalent
change for PG_WRITE_ALL_DATA as it would effectively provide
pg_write_all_data write access to a system catalog which is explicitly
avoided for system catalogs
Please take a look and let me know what you folks think. If this
approach makes sense, I will update the corresponding docs in the
patch.
Thanks & Regards,
Nitin Motiani
Google
[1] https://www.postgresql.org/message-id/19379-089536632927293f%40postgresql.org
[2] https://www.postgresql.org/message-id/r5a3aqlrrqen2snktdmx5tjeoakp3hmbektlqmeqhij3fqqez4%40zmx3bdsci...
Attachments:
v1-0001-Support-large-object-functions-with-pg_read_all_d.patch (application/x-patch, 1.6K) download
From a10d85986c9ec1038fe80affea73d740c19c731e Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
Date: Thu, 5 Feb 2026 09:08:57 +0000
Subject: [PATCH v1] 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.
---
src/backend/catalog/aclchk.c | 11 +++++++++++
1 file changed, 11 insertions(+)
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;
}
--
2.53.0.rc2.204.g2597b5adb4-goog
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
@ 2026-02-05 15:44 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nathan Bossart @ 2026-02-05 15:44 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: pgsql-hackers
On Thu, Feb 05, 2026 at 03:26:02PM +0530, Nitin Motiani wrote:
> Please take a look and let me know what you folks think. If this
> approach makes sense, I will update the corresponding docs in the
> patch.
At a glance, it looks generally reasonable to me. In addition to updating
the documentation, I'd recommend adding tests.
--
nathan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
@ 2026-02-09 11:55 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nitin Motiani @ 2026-02-09 11:55 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers
On Thu, Feb 5, 2026 at 9:14 PM Nathan Bossart <nathandbossart@gmail.com> 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
Attachments:
v2-0001-Support-large-object-functions-with-pg_read_all_d.patch (application/x-patch, 7.0K) download
From 15577cb383c91b762fffb6a1299861569a7a58e4 Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
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
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
@ 2026-02-10 08:47 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 11+ messages in thread
From: Dilip Kumar @ 2026-02-10 08:47 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
On Mon, Feb 9, 2026 at 5:25 PM Nitin Motiani <nitinmotiani@google.com> wrote:
>
> On Thu, Feb 5, 2026 at 9:14 PM Nathan Bossart <nathandbossart@gmail.com> 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.
Hi Nitin, Your patch looks good to me except for some minor
suggestions/questions.
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
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.
2. +SELECT lo_get(1002); -- ok
+ lo_get
+--------------------------
+ \x68656c6c6f20776f726c64
+(1 row)
+
+SELECT lo_get(1002, 6, 5); -- ok
+ lo_get
+--------------
+ \x776f726c64
+(1 row)
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?
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-02-10 13:09 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nitin Motiani @ 2026-02-10 13:09 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
On Tue, Feb 10, 2026 at 2:17 PM Dilip Kumar <dilipbalaut@gmail.com> 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
Attachments:
v3-0001-Support-large-object-functions-with-pg_read_all_d.patch (application/x-patch, 6.7K) download
From 1080d492c7b2d813874790ea8351fbadf16d681f Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
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
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
@ 2026-02-12 21:21 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nathan Bossart @ 2026-02-12 21:21 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
On Tue, Feb 10, 2026 at 06:39:18PM +0530, Nitin Motiani wrote:
> I updated the commit message according to this suggestion.
Thanks for the new patch. Please create a commitfest entry so that this
patch 1) isn't forgotten and 2) gets tested by cfbot.
<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
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. For example, in this case, I would've probably done something
like:
<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
Of course, this isn't always possible, and opinions may vary...
+\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
I'd suggest simplifying this a bit by borrowing some lines from the
surrounding tests. I don't think we need to test lo_lseek and friends
because (IIUC) those don't need to do ACL checks. Here's a rough idea of
what I'm thinking:
+\c -
+-- confirm member 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_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
Later on, there's also this change:
-SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no
+SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- yes
I think the point of this test is to have a negative case, so we should
probably switch it to another role that doesn't have privileges on the
large object. But it wouldn't hurt to also verify
has_largeobject_privilege() works as expected for members of
pg_read_all_data.
--
nathan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
@ 2026-02-13 12:05 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-13 17:17 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nitin Motiani @ 2026-02-13 12:05 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
Thanks Nathan for the feedback.
On Fri, Feb 13, 2026 at 2:51 AM Nathan Bossart <nathandbossart@gmail.com> 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
Attachments:
v4-0001-Support-large-object-functions-with-pg_read_all_d.patch (application/x-patch, 6.5K) download
From 7943be732780b56e0c693986a3eabcb87023ed95 Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
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
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
@ 2026-02-13 17:17 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 17:07 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nathan Bossart @ 2026-02-13 17:17 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
On Fri, Feb 13, 2026 at 05:35:14PM +0530, Nitin Motiani wrote:
> Attaching the latest patch.
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.
--
nathan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-13 17:17 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
@ 2026-02-23 17:07 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 21:00 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-24 02:06 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
0 siblings, 2 replies; 11+ messages in thread
From: Nathan Bossart @ 2026-02-23 17:07 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
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.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)
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-13 17:17 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 17:07 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
@ 2026-02-23 21:00 ` Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Nathan Bossart @ 2026-02-23 21:00 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
Committed.
--
nathan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Support reading large objects with pg_read_all_data
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Re: [PATCH] Support reading large objects with pg_read_all_data Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-13 17:17 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 17:07 ` Re: [PATCH] Support reading large objects with pg_read_all_data Nathan Bossart <nathandbossart@gmail.com>
@ 2026-02-24 02:06 ` Nitin Motiani <nitinmotiani@google.com>
1 sibling, 0 replies; 11+ messages in thread
From: Nitin Motiani @ 2026-02-24 02:06 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; pgsql-hackers
> 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.
Thanks Nathan. My thinking behind this was that even without these
changes, the 'select *' on the large object table worked for
pg_read_all_data so providing access to functions like lo_get seemed
consistent with that behaviour. But for pg_write_all_data, that wasn't
the case so I thought it might be safer not to provide access.
> 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.
>
Thanks for the explanation and taking care of this.
Regards,
Nitin Motiani
Google
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-02-24 02:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-05 09:56 [PATCH] Support reading large objects with pg_read_all_data Nitin Motiani <nitinmotiani@google.com>
2026-02-05 15:44 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-09 11:55 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-10 08:47 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-02-10 13:09 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-12 21:21 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-13 12:05 ` Nitin Motiani <nitinmotiani@google.com>
2026-02-13 17:17 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 17:07 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-23 21:00 ` Nathan Bossart <nathandbossart@gmail.com>
2026-02-24 02:06 ` Nitin Motiani <nitinmotiani@google.com>
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