Re: Read-only connection mode for AI workflows.

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