Date: Tue, 17 Mar 2026 10:11:20 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Jack Bonatakis <jack@bonatak.is>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Read-only connection mode for AI workflows.
Message-ID: <ablhCDfqCgkvDrEO@momjian.us>
References: 
 <CADsUR0B9bcJQKYHyUMnWcODGzF5+AdeToawULkkTKfrq32Z-8w@mail.gmail.com>
 <64f1c69a-ceff-4b17-8298-58f255d075fc@gmail.com>
 <dde00623-b8e0-421b-a3f7-b149768894ef@app.fastmail.com>
 <fab6a5f3-df9f-4bd7-b2ac-434a17303547@gmail.com>
 <abh1LGa98D2Bpv-n@momjian.us>
 <cbcefd92-4f43-46ef-8c9f-0c45aa8dca77@gmail.com>
 <ablcmDxJ2sqdnf_L@momjian.us>
 <a17ffd88-10c1-41cf-bf40-ca5e99dcba2f@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <a17ffd88-10c1-41cf-bf40-ca5e99dcba2f@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ablhCDfqCgkvDrEO%40momjian.us>
Precedence: bulk

On Tue, Mar 17, 2026 at 03:05:08PM +0100, Andrei Lepikhov wrote:
> On 17/3/26 14:52, Bruce Momjian wrote:
> > On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
> > > On 16/3/26 22:25, Bruce Momjian wrote:
> > > > On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > > > > 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.
> > > > 
> > > > I agree the need a read-only sessions is going to get more urgent with
> > > > MCP.  Why doesn't the community code have a read-only session option
> > > > that can't be changed?
> > > 
> > > The pg_readonly project aims to answer this question: if it is easy and
> > > cheap to implement as an extension, why do we need to touch the core?
> > 
> > I think it is a fundamental feature the database should have by default.
> > 
> 
> Why wasn’t read-only mode set up like this from the start? - I haven’t seen
> any other DBMSs, aside from SQLite, offer this kind of guarantee.

I have no idea why.  I guess there just wasn't much demand, but now
there clearly is with MCP.

> If we want to move forward, it makes sense to use a session parameter and
> add backend code to prevent violations.

Agreed.

> Postgres architecture looks well-suited for this feature. However, the
> request is to block all backend changes, not just the usual XactReadOnly
> limitations, but also things like vacuum, etc (temporary tables?). Should we
> also consider cluster-wide restrictions?

No, I don't think cluster-wide is in demand, but I might be wrong.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.