Обсуждение: Read-only connection mode for AI workflows.

On 7/8/25 18:46, Mat Arye wrote:
> 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).

I believe the pg_readonly [1] extension does what you're looking for, so 
you might want to give it a try.

Could you share a bit more about your situation? For example, are you 
thinking of making specific users read-only, or do you want all database 
connections to be read-only? 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.

[1] https://github.com/pierreforstmann/pg_readonly

-- 
regards, Andrei Lepikhov,
pgEdge

I believe the pg_readonly [1] extension does what you're looking for, so 

you might want to give it a try.

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.

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.

On 16/3/26 20:28, Jack Bonatakis wrote:
> 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. 

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

On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > 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.
> 
...
> > 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.

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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

On 16/3/26 22:25, Bruce Momjian wrote:
> On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
>>> 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.
> 
> 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?

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?

-- 
regards, Andrei Lepikhov,
pgEdge

On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
> On 16/3/26 22:25, Bruce Momjian wrote:
> > On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > > 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.
> > 
> > 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?
> 
> 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?

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

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

On 17/3/26 14:52, Bruce Momjian wrote:
> On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
>> On 16/3/26 22:25, Bruce Momjian wrote:
>>> On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
>>>>> 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.
>>>
>>> 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?
>>
>> 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?
> 
> 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?

-- 
regards, Andrei Lepikhov,
pgEdge

On Tue, Mar 17, 2026 at 03:05:08PM +0100, Andrei Lepikhov wrote:
> On 17/3/26 14:52, Bruce Momjian wrote:
> > On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
> > > On 16/3/26 22:25, Bruce Momjian wrote:
> > > > On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > > > > 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.
> > > > 
> > > > 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?
> > > 
> > > 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?
> > 
> > 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.

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.

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

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

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

On Tue, Mar 17, 2026 at 09:52:24AM -0400, Bruce Momjian wrote:
> On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
> > On 16/3/26 22:25, Bruce Momjian wrote:
> > > On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > > > 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.
> > > 
> > > 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?
> > 
> > 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?
> 
> 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.

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

On 17.03.26 15:05, Andrei Lepikhov wrote:
> On 17/3/26 14:52, Bruce Momjian wrote:
>> On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
>>> On 16/3/26 22:25, Bruce Momjian wrote:
>>>> On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
>>>>>> 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.
>>>>
>>>> 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?
>>>
>>> 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?
>>
>> 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?

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.

Hi,

On 2026-03-18 14:48:41 +0100, Peter Eisentraut wrote:
> On 17.03.26 15:05, Andrei Lepikhov wrote:
> > On 17/3/26 14:52, Bruce Momjian wrote:
> > > On Tue, Mar 17, 2026 at 11:04:25AM +0100, Andrei Lepikhov wrote:
> > > > On 16/3/26 22:25, Bruce Momjian wrote:
> > > > > On Mon, Mar 16, 2026 at 10:01:22PM +0100, Andrei Lepikhov wrote:
> > > > > > > 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.
> > > > >
> > > > > 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?
> > > >
> > > > 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?
> > >
> > > 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?
>
> 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.

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

On 18/3/26 15:26, Andres Freund wrote:
> 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 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?

-- 
regards, Andrei Lepikhov,
pgEdge

On 16/3/26 22:01, Andrei Lepikhov wrote:
> On 16/3/26 20:28, Jack Bonatakis wrote:
>> 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.
>> Please correct me if I am mistaken, but it looks like pg_readonly 
>> 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

-- 
regards, Andrei Lepikhov,
pgEdge

On 19/3/26 08:53, Pavel Stehule wrote:
>     We could improve it by restricting manual calls to specific utility
>     operations, such as VACUUM or REINDEX. However, we would need some
>     specifications first.
> 
> 
> It doesn't cover  possibility to set GUC by set_config function

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.

-- 
regards, Andrei Lepikhov,
pgEdge

On 19.03.26 08:44, Andrei Lepikhov wrote:
> On 16/3/26 22:01, Andrei Lepikhov wrote:
>> On 16/3/26 20:28, Jack Bonatakis wrote:
>>> 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.
>>> Please correct me if I am mistaken, but it looks like pg_readonly 
>>> 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.

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

Maybe that could be reactivated, but the reason it stalled was that it 
was hard.

On Thu, Mar 19, 2026 at 12:39 PM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
> +1 to scenarios Andres' mentioned. Additional cases where a cluster‑wide setting is helpful include disk‑full events
andpolicy 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

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'tsee the need for it when we have an existing roles/permissions system that gets the job done. You want your
untrustedagent to read from your database? Create a specific role for that. If our existing per-role access controls
arenot 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

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.