Date: Mon, 23 Mar 2026 13:43:31 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Isaac Morland <isaac.morland@gmail.com>
Cc: Jelte Fennema-Nio <postgres@jeltef.nl>,
	Greg Sabino Mullane <htamfids@gmail.com>,
	Peter Eisentraut <peter@eisentraut.org>,
	Andrei Lepikhov <lepihov@gmail.com>,
	Jack Bonatakis <jack@bonatak.is>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Andres Freund <andres@anarazel.de>
Subject: Re: Read-only connection mode for AI workflows.
Message-ID: <acF7wwPFWaH7n7LL@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>
 <7f6e0ff9-05e9-4664-9c71-d9dd744362b9@gmail.com>
 <138cfd8c-b305-4303-9700-bc53ff4fb926@eisentraut.org>
 <CAKAnmmKgYqavU6xUPKgeOwOY0P9EycCmm339+PLaL5f4AQ9fNQ@mail.gmail.com>
 <CAGECzQSmRUw9qfpeJFApnhywH_FmLRCHKt4Gncn7zC-MDffchQ@mail.gmail.com>
 <CAMsGm5fuHSvjCYuR3qyFWkbQGMjT2AS7ZJ8rrU+MjT7YzO2e_A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAMsGm5fuHSvjCYuR3qyFWkbQGMjT2AS7ZJ8rrU+MjT7YzO2e_A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/acF7wwPFWaH7n7LL%40momjian.us>
Precedence: bulk

On Mon, Mar 23, 2026 at 07:04:18AM -0400, Isaac Morland wrote:
> On Mon, 23 Mar 2026 at 05:10, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> 
>     On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <htamfids@gmail.com>
>     wrote:
>     > I'm a +1 to the cluster-wide change, and a -1 to the per-connection idea
>     that started this thread, because I still don't see the need for it when we
>     have an existing roles/permissions system that gets the job done. You want
>     your untrusted agent to read from your database? Create a specific role for
>     that. If our existing per-role access controls are not sufficient, improve
>     them.
> 
>     I think they are insufficient for two reasons:
>     1. Afaik there's no simple way to take an existing role and create a
>     new role from it that only has the read permissions of the original
>     role. Especially if you want those permissions to stay in sync between
>     the roles.
> 
> 
> I don't think it's possible even in principle. As soon as the supposedly
> read-only role calls a security definer function, the session is no longer
> operating with the permissions of the supposedly read-only role.
> 
> I think what is wanted is, in effect, very close to the ability to pretend that
> one is connected to a replica rather than the primary, What is requested
> already exists in a sense through the use of replication, but only at the
> entire instance level, not one session. In other words, what you suggest below,
> although it might be interesting to think about whether there are any other
> settings that would be useful to lock down in this fashion:
> 
> 
>     I think the best way to address this thread is to have a way to "lock"
>     settings down, like discussed in this thread[1]. Then a user could
>     simply run the sql to lock down the transaction_read_only and get a
>     read-only connection that it could give to the LLM.

So, we have two possible features here.  First, cluster-wide read-only
mode, at least read-only from the client perspective, not necessarily
preventing WAL or vacuum.

Second, using per-user permissions does sound like the right level of
control for read-only sessions, and I am not too worried about having to
create the user and set permissions --- seems reasonable.  I do question
if the user is read-only enough, e.g., should they be able to create
temp tables and call security-definer functions.  At this point we have
assumed any defined user should have a minimum amount of trust, but with
MCP, we have to assume the user has no trust but read-only access, and I
don't know if our user permission system is limiting enough for such use
cases.

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