Re: Prevent writes on large objects in read-only transactions

On Tue, May 31, 2022 at 05:17:42PM -0400, Robert Haas wrote:
> On Tue, May 31, 2022 at 3:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What I'm wondering about is how far the principle of read-only-ness
>> ought to be expected to go. Should a read-only transaction fail
>> to execute adminpack's pg_file_write(), for example? Should it
>> refuse to execute random() on the grounds that that changes the
>> session's PRNG state? The latter seems obviously silly, but
>> I'm not very sure about pg_file_write(). Maybe the restriction
>> should be "no changes to database state that's visible to other
>> sessions", which would leave outside-the-DB changes out of the
>> discussion.
>
> Yeah, I think that's a pretty good idea. It's really pretty hard to
> imagine preventing outside-the-database writes in any kind of
> principled way. Somebody can install a C function that does anything,
> and we can do a pretty fair job preventing it from e.g. acquiring a
> transaction ID or writing WAL by making changes in PostgreSQL itself,
> but we can't prevent it from doing whatever it wants outside the
> database. Nor is it even a very clear concept definitionally. I
> wouldn't consider a function read-write solely on the basis that it
> can cause data to be written to the PostgreSQL log file, for instance,
> so it doesn't seem correct to suppose that a C function provided by an
> extension is read-write just because it calls write(2) -- not that we
> can detect that anyway, but even if we could.

Agreed. There are a couple of arguments in authorizing
pg_file_write() in a read-only state or writes as long as it does not
affect WAL or the data. For example, a change of configuration file
can be very useful at recovery if one wants to switch the
configuration (ALTER TABLE SET, etc.), so restricting functions that
perform writes outside the scope of WAL or the data does not make
sense to restrict. Not to count updates in the control file, but
that's different.

Now the LO handling is quite old, and I am not sure if this is worth
changing as we have seen no actual complains about that with read-only
transactions, even if I agree on that it is inconsistent. That could
cause more harm than the consistency benefit is worth :/
--
Michael