On 16/3/26 20:28, Jack Bonatakis wrote:
On Mon, Mar 16, 2026, at 2:08 PM, Andrei Lepikhov wrote:
I believe the pg_readonly [1] extension does what you're looking for, so
you might want to give it a try.
Hi Andrei,
Please correct me if I am mistaken, but it looks like pg_readonly
operates at the database or cluster level.
Exactly. It works cluster-wide at the moment. But it is very simple to
allow it to establish a read-only mode in a backend. That's exactly why
I requested a full picture.
If I understand Mat's
proposal correctly, and based on my own experience integrating LLM-based
tools with databases, one might desire to set a particular connection to
be read-only while leaving the rest of the connections to operate
normally (read/write). Now, I would hope that someone building an AI
integration that is not intended to write to or manage the system would
be doing so off of a read-replica where pg_readonly would make more
sense, but I would wager that this will not always be the case.
Connection setup is usually not AI controlled while the SQL executed
sometimes is. That's why being able to control read-only mode on the
connection level would be useful.
Ok, such a mode will reduce minor pg_readonly overhead down to almost
zero. The practical questions I need to know in advance:
1. Is it OK to call the LOAD command at the beginning of connection
establishment (make it dynamically loadable and strictly
connection-dependent)
2. Should it be able to change the mode inside such a read-only session
(let's say, under a superuser).
Additionally, I believe this is the key point. Setting read-only at the
connection level alleviates any concern about an AI agent exploiting
misconfigured permissions to escalate its privileges (e.g. `select
unset_cluster_readonly(); drop table users;`).
Also, which commands do you want to restrict? For instance, vacuum
isn't a DML command, but it can still change the state of table pages
and pg_catalog.
This functionality is now out of the Postgres core logic. It is not hard
to add to the extension, though, let's say as a string GUC, where you
may add any utility command you want to reject in read-only mode. So,
depends on specific cases.
From my perspective, many AI integrations would want to limit just
about anything that can change the state of the database. So yes,
vacuum, checkpoint, likely analyze (although I can see an argument for
allowing a read-only connection to run analyze), and other similar
commands, as well as of course traditional DML and DDL.
As I've said, it is easy unless you want to suspend internal services as
well (like autovacuum). It is also doable within (I envision) the SMGR
plugin, but a little more dangerous; this feature just needs more design
and coding effort for a certain answer.
That said, once you start thinking about the precise scope of what
should be allowed or disallowed, the design space becomes quite large.
It may be worth clarifying the intended guarantees of such a feature
before discussing implementation details.
Right now as an extension pg_readonly guarantees standard core
XactReadOnly behaviour.
I do think the underlying problem of safely exposing databases to
automated agents is becoming increasingly common, so it seems like a
useful area to explore.
Thanks for your profound feedback!
--
regards, Andrei Lepikhov,
pgEdge
