Feedback-ID: iecce486f:Fastmail
MIME-Version: 1.0
Date: Mon, 16 Mar 2026 15:28:06 -0400
From: "Jack Bonatakis" <jack@bonatak.is>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Message-Id: <dde00623-b8e0-421b-a3f7-b149768894ef@app.fastmail.com>
In-Reply-To: <64f1c69a-ceff-4b17-8298-58f255d075fc@gmail.com>
References: 
 <CADsUR0B9bcJQKYHyUMnWcODGzF5+AdeToawULkkTKfrq32Z-8w@mail.gmail.com>
 <64f1c69a-ceff-4b17-8298-58f255d075fc@gmail.com>
Subject: Re: Read-only connection mode for AI workflows.
Content-Type: multipart/alternative;
 boundary=5f522b20fbcb4e73b5ce299be30dc959
Archived-At: 
 <https://www.postgresql.org/message-id/dde00623-b8e0-421b-a3f7-b149768894ef%40app.fastmail.com>
Precedence: bulk

--5f522b20fbcb4e73b5ce299be30dc959
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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. 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.

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.