Read-only connection mode for AI workflows.
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
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]https://github.com/pierreforstmann/pg_readonly 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
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 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.
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.
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.
Jack
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