Re: Read-only connection mode for AI workflows.

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(at)jeltef(dot)nl> wrote:
>
> On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <htamfids(at)gmail(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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