Date: Thu, 12 Feb 2026 15:21:54 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Nitin Motiani <nitinmotiani@google.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: [PATCH] Support reading large objects with pg_read_all_data
Message-ID: <aY5EcqjJjmeNLG4Y@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAH5HC97AJmSGqrusJLcBU0evubhm+Q139PdAhwdr2c_=D9r2ZA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aY5EcqjJjmeNLG4Y%40nathan>
Precedence: bulk

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