MIME-Version: 1.0
From: Mat Arye <mat@tigerdata.com>
Date: Thu, 7 Aug 2025 12:46:47 -0400
Message-ID: 
 <CADsUR0B9bcJQKYHyUMnWcODGzF5+AdeToawULkkTKfrq32Z-8w@mail.gmail.com>
Subject: Read-only connection mode for AI workflows.
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a454aa063bc934ea"
Archived-At: 
 <https://www.postgresql.org/message-id/CADsUR0B9bcJQKYHyUMnWcODGzF5%2BAdeToawULkkTKfrq32Z-8w%40mail.gmail.com>
Precedence: bulk

--000000000000a454aa063bc934ea
Content-Type: text/plain; charset="UTF-8"

Hi All,

A common desire for AI agents accessing the database is to have read-only
access for the agents. You can create special roles with explicit
assignment or use pg_read_all_data but this is cumbersome and not very
ergonomic. Often, people want to use an existing role but as part of a
connection that is marked "read-only". This can be done with
the transaction_read_only GUC, however the AI could unset that in all kinds
of clever ways by executing SQL commands.

For example, a popular MCP server (I am NOT affiliated with) goes through
all kinds of hoops to avoid this: https://github.com/crystaldba/postgres-mcp

It would be nice to force a connection into read-only mode. Connection
setup is usually not AI controlled while the SQL executed sometimes is.
That's why being able to control read-only mode on the connection level
would be useful.

I'd be happy to submit a patch if there is interest in this feature
(especially if I get some pointers to where people would like to see this
implemented).

Thanks,
Mat
TigerData

--000000000000a454aa063bc934ea
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi All,<div><br></div><div>A common desire for AI agents a=
ccessing the database is to have read-only access for the agents. You can c=
reate special roles with explicit assignment or use=C2=A0pg_read_all_data b=
ut this is cumbersome=C2=A0and not very ergonomic. Often, people want to us=
e an existing role but as part of a connection that is marked &quot;read-on=
ly&quot;. This can be done with the=C2=A0transaction_read_only GUC, however=
 the AI could unset that in all kinds of clever ways by executing SQL comma=
nds.=C2=A0</div><div><br></div><div>For=C2=A0example, a popular MCP server =
(I am NOT affiliated with) goes through all kinds of hoops to avoid this:=
=C2=A0<a href=3D"https://github.com/crystaldba/postgres-mcp">https://github=
.com/crystaldba/postgres-mcp</a></div><div><br></div><div>It would be nice =
to force a connection into read-only mode. Connection setup is usually=C2=
=A0not AI controlled while the SQL executed sometimes is. That&#39;s why be=
ing able to control read-only mode on the connection level would be useful.=
</div><div><br></div><div>I&#39;d be happy to submit a patch if there is in=
terest in this feature (especially if I get some pointers to where people w=
ould like to see this implemented).</div><div><br></div><div>Thanks,</div><=
div>Mat</div><div>TigerData</div></div>

--000000000000a454aa063bc934ea--