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