public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bruce Momjian <[email protected]>
To: Isaac Morland <[email protected]>
Cc: Jelte Fennema-Nio <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: Jack Bonatakis <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Subject: Re: Read-only connection mode for AI workflows.
Date: Mon, 23 Mar 2026 13:43:31 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMsGm5fuHSvjCYuR3qyFWkbQGMjT2AS7ZJ8rrU+MjT7YzO2e_A@mail.gmail.com>
References: <CADsUR0B9bcJQKYHyUMnWcODGzF5+AdeToawULkkTKfrq32Z-8w@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAKAnmmKgYqavU6xUPKgeOwOY0P9EycCmm339+PLaL5f4AQ9fNQ@mail.gmail.com>
	<CAGECzQSmRUw9qfpeJFApnhywH_FmLRCHKt4Gncn7zC-MDffchQ@mail.gmail.com>
	<CAMsGm5fuHSvjCYuR3qyFWkbQGMjT2AS7ZJ8rrU+MjT7YzO2e_A@mail.gmail.com>

On Mon, Mar 23, 2026 at 07:04:18AM -0400, Isaac Morland wrote:
> On Mon, 23 Mar 2026 at 05:10, Jelte Fennema-Nio <[email protected]> wrote:
> 
>     On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <[email protected]>
>     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  <[email protected]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

view thread (20+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Read-only connection mode for AI workflows.
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox