Subject: Read-only connection mode for AI workflows.

From: Mat Arye <[email-masked]>
Date: Thu, 7 Aug 2025 12:46:47 -0400

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

---

From: "Jack Bonatakis" <[email-masked]>
Date: Mon, 16 Mar 2026 15:28:06 -0400

On Mon, Mar 16, 2026, at 2:08 PM, Andrei Lepikhov wrote:
> I believe the pg_readonly [1] extension does what you're looking for, so 
> you might want to give it a try.

Hi Andrei,

Please correct me if I am mistaken, but it looks like pg_readonly operates at the database or cluster level. If I understand Mat's proposal correctly, and based on my own experience integrating LLM-based tools with databases, one might desire to set a particular connection to be read-only while leaving the rest of the connections to operate normally (read/write). Now, I would hope that someone building an AI integration that is not intended to write to or manage the system would be doing so off of a read-replica where pg_readonly would make more sense, but I would wager that this will not always be the case. 

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

Additionally, I believe this is the key point. Setting read-only at the connection level alleviates any concern about an AI agent exploiting misconfigured permissions to escalate its privileges (e.g. `select unset_cluster_readonly(); drop table users;`). 

> Also, which commands do you want to restrict? For instance, vacuum isn't a DML command, but it can still change the state of table pages and pg_catalog.

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 16 Mar 2026 22:01:22 +0100

On 16/3/26 20:28, Jack Bonatakis wrote:
> Please correct me if I am mistaken, but it looks like pg_readonly 
> operates at the database or cluster level. 

Exactly. It works cluster-wide at the moment. But it is very simple to 
allow it to establish a read-only mode in a backend. That's exactly why 
I requested a full picture.

> If I understand Mat's 
> proposal correctly, and based on my own experience integrating LLM-based 
> tools with databases, one might desire to set a particular connection to 
> be read-only while leaving the rest of the connections to operate 
> normally (read/write). Now, I would hope that someone building an AI 
> integration that is not intended to write to or manage the system would 
> be doing so off of a read-replica where pg_readonly would make more 
> sense, but I would wager that this will not always be the case.
> 
>> 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.

Ok, such a mode will reduce minor pg_readonly overhead down to almost 
zero. The practical questions I need to know in advance:
1. Is it OK to call the LOAD command at the beginning of connection 
establishment (make it dynamically loadable and strictly 
connection-dependent)
2. Should it be able to change the mode inside such a read-only session 
(let's say, under a superuser).

> 
> Additionally, I believe this is the key point. Setting read-only at the 
> connection level alleviates any concern about an AI agent exploiting 
> misconfigured permissions to escalate its privileges (e.g. `select 
> unset_cluster_readonly(); drop table users;`).
> 
>> Also, which commands do you want to restrict? For instance, vacuum 
>> isn't a DML command, but it can still change the state of table pages 
>> and pg_catalog.

This functionality is now out of the Postgres core logic. It is not hard 
to add to the extension, though, let's say as a string GUC, where you 
may add any utility command you want to reject in read-only mode. So, 
depends on specific cases.

> 
>  From my perspective, many AI integrations would want to limit just 
> about anything that can change the state of the database. So yes, 
> vacuum, checkpoint, likely analyze (although I can see an argument for 
> allowing a read-only connection to run analyze), and other similar 
> commands, as well as of course traditional DML and DDL.
> 

As I've said, it is easy unless you want to suspend internal services as 
well (like autovacuum). It is also doable within (I envision) the SMGR 
plugin, but a little more dangerous; this feature just needs more design 
and coding effort for a certain answer.

> That said, once you start thinking about the precise scope of what 
> should be allowed or disallowed, the design space becomes quite large. 
> It may be worth clarifying the intended guarantees of such a feature 
> before discussing implementation details.

Right now as an extension pg_readonly guarantees standard core 
XactReadOnly behaviour.

> 
> I do think the underlying problem of safely exposing databases to 
> automated agents is becoming increasingly common, so it seems like a 
> useful area to explore.

Thanks for your profound feedback!

-- 
regards, Andrei Lepikhov,
pgEdge

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 16 Mar 2026 17:25:00 -0400

On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > isn't a DML command, but it can still change the state of table
> > > pages and pg_catalog.
> 
> This functionality is now out of the Postgres core logic. It is not hard to
> add to the extension, though, let's say as a string GUC, where you may add
> any utility command you want to reject in read-only mode. So, depends on
> specific cases.
> 
...
> Right now as an extension pg_readonly guarantees standard core XactReadOnly
> behaviour.
> 
> > 

I agree the need a read-only sessions is going to get more urgent with
MCP.  Why doesn't the community code have a read-only session option
that can't be changed?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 17 Mar 2026 11:04:25 +0100

> MCP.  Why doesn't the community code have a read-only session option

The pg_readonly project aims to answer this question: if it is easy and 
cheap to implement as an extension, why do we need to touch the core?

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 17 Mar 2026 09:52:24 -0400

> cheap to implement as an extension, why do we need to touch the core?

I think it is a fundamental feature the database should have by default.

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 17 Mar 2026 15:05:08 +0100

> I think it is a fundamental feature the database should have by default.

Why wasn’t read-only mode set up like this from the start? - I haven’t 
seen any other DBMSs, aside from SQLite, offer this kind of guarantee.
If we want to move forward, it makes sense to use a session parameter 
and add backend code to prevent violations.
Postgres architecture looks well-suited for this feature. However, the 
request is to block all backend changes, not just the usual XactReadOnly 
limitations, but also things like vacuum, etc (temporary tables?). 
Should we also consider cluster-wide restrictions?

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 17 Mar 2026 10:11:20 -0400

On Tue, Mar 17, 2026 at 03:05:08PM +0100, Andrei Lepikhov wrote:
> Why wasn’t read-only mode set up like this from the start? - I haven’t seen
> any other DBMSs, aside from SQLite, offer this kind of guarantee.

I have no idea why.  I guess there just wasn't much demand, but now
there clearly is with MCP.

> If we want to move forward, it makes sense to use a session parameter and
> add backend code to prevent violations.

Agreed.

> limitations, but also things like vacuum, etc (temporary tables?). Should we
> also consider cluster-wide restrictions?

No, I don't think cluster-wide is in demand, but I might be wrong.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 17 Mar 2026 12:31:56 -0400

> I think it is a fundamental feature the database should have by default.

I now see that pg_readonly is cluster-wide:

	https://github.com/pierreforstmann/pg_readonly

I agree we should have a per-session control that cannot be changed.

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 18 Mar 2026 14:48:41 +0100

> Should we also consider cluster-wide restrictions?

Read-only mode is a transaction property, not an access control system.

If you want to control who can read what, there is an access control 
system for that.  If it's insufficient, let's enhance it.  But let's 
keep these things separate.

---

From: Andres Freund <[email-masked]>
Date: Wed, 18 Mar 2026 10:26:26 -0400

Hi,

On 2026-03-18 14:48:41 +0100, Peter Eisentraut wrote:
> If you want to control who can read what, there is an access control system
> for that.  If it's insufficient, let's enhance it.  But let's keep these
> things separate.

I don't agree that this need can be entirely addressed by access control.

Regardless of the AI angle it's quite useful to be able to put a server into
read only mode, e.g. in preparation for a planned failover where you can
continue to allow reads but don't want any more writes. Or in preparation for
a shutdown you want to prevent further writes (so the shutdown checkpoint is
quick), but you do want to allow further reads (to reduce the scope of the
downtime, by allowing reads while doing a CHECKPOINT before the actual
shutdown).

It doesn't make sense to implement stuff like that by changing all the access
controls of the system, because it'll often be a temporary thing. So you have
to figure out all the DDL to temporarily revoke permissions, have to somehow
wait till those changes have taken hold for everything, then you have to
figure out DDL to revert all those changes.

Greetings,

Andres

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 19 Mar 2026 08:44:15 +0100

>> operates at the database or cluster level. 

Take a look at the [1] project. It's a simpler version of [2] that 
always switches to read-only mode.
To use it, just have your connection pooler load the 'safesession' 
module. This will keep the session in read-only mode until it ends. 
There are no GUCs, and there is no way to change the mode, even for a 
superuser. Does this seem safe enough?

We could improve it by restricting manual calls to specific utility 
operations, such as VACUUM or REINDEX. However, we would need some 
specifications first.

[1] https://github.com/danolivo/safesession/
[2] https://github.com/pierreforstmann/pg_readonly

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 19 Mar 2026 08:53:13 +0100

Hi

> operations, such as VACUUM or REINDEX. However, we would need some

It doesn't cover  possibility to set GUC by set_config function

Regards

Pavel

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 19 Mar 2026 10:49:24 +0100

Hi

čt 19. 3. 2026 v 9:40 odesílatel Andrei Lepikhov <[email-masked]> napsal:

> On 19/3/26 08:53, Pavel Stehule wrote:
> Can you explain it? I added a test for the set_config() call.
> This extension is so tiny because it exploits the rule: no ro -> rw
> switch after a snapshot has been taken (but rw -> ro is possible). The
> set_config can’t overcome this rule.
>

I am sorry. I missed so you used standard_ExecutorStart

Regards

Pavel

---

From: SATYANARAYANA NARLAPURAM <[email-masked]>
Date: Thu, 19 Mar 2026 12:39:18 -0700

Hi,

On Wed, Mar 18, 2026 at 7:36 AM Andrei Lepikhov <[email-masked]> wrote:

> On 18/3/26 15:26, Andres Freund wrote:
> It returns us to the question about cluster-wide V/S session-wide
> read-only mode. Should we design one of them or consider both? What do
> you think?
>

+1 to scenarios Andres' mentioned. Additional cases where a cluster‑wide
setting is helpful include disk‑full events and policy enforcement, where
write access is revoked but read access is preserved for data exfiltration.
Session level is helpful for the AI use cases or to provide controlled user
access. I see value in supporting both.

 Thanks,
Satya

---

From: Jacob Champion <[email-masked]>
Date: Thu, 19 Mar 2026 14:59:07 -0700

On Thu, Mar 19, 2026 at 12:39 PM SATYANARAYANA NARLAPURAM
<[email-masked]> wrote:
> +1 to scenarios Andres' mentioned. Additional cases where a cluster‑wide setting is helpful include disk‑full events and policy enforcement, where write access is revoked but read access is preserved for data exfiltration.

I've additionally wanted this during orchestrated pg_upgrade
scenarios, to apply to the old cluster.

--Jacob

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 20 Mar 2026 08:32:24 -0400

On Thu, Mar 19, 2026 at 6:09 AM Peter Eisentraut <[email-masked]>
wrote:

> Here is a stalled project to implement ALTER SYSTEM READ ONLY:
>
> https://www.postgresql.org/message-id/flat/CAAJ_b97KZzdJsffwRK7w0XU5HnXkcgKgTR69t8cOZztsyXjkQw%40mail.gmail.com

I think the scope of this request is much smaller than that one, so should
be more doable. That one, IIUC, is more of a ALTER SYSTEM
STOP_ALL_ACTIVITY_EVEN_WAL but we are looking for more of a "stop any overt
changes to our data via any non-select command" while still allowing all
sorts of background/maintenance activity to continue on. Basically,
anything that would cause a pg_dump to be different.

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.

Cheers,
Greg

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 23 Mar 2026 10:10:14 +0100

On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <[email-masked]> 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.
2. The user that would want to do this, often lacks the create role
permissions. So you effectively need admin access to the server to
downgrade your permissions to read only.

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.

[1]: https://www.postgresql.org/message-id/flat/CACA6kxh4MfRCHuY%2BuC2ZvXRQUP63LqumNtxtLsDF-mJswAJR5w%40mail.gmail.com

---

From: Isaac Morland <[email-masked]>
Date: Mon, 23 Mar 2026 07:04:18 -0400

> role. Especially if you want those permissions to stay in sync between

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:

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 23 Mar 2026 13:43:31 -0400

On Mon, Mar 23, 2026 at 07:04:18AM -0400, Isaac Morland wrote:
> 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:
> 
> 

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.