Re: Read-only connection mode for AI workflows. - Mailing list pgsql-hackers

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