Thread

1. Read-only connection mode for AI workflows.

   Mat Arye <mat@tigerdata.com> — 2025-08-07T16:46:47Z

   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
   

2. Re: Read-only connection mode for AI workflows.

   Andrei Lepikhov <lepihov@gmail.com> — 2026-03-16T18:08:30Z

   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
   
   
   
   

3. Re: Read-only connection mode for AI workflows.

   Jack Bonatakis <jack@bonatak.is> — 2026-03-16T19:28:06Z

   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

4. Re: Read-only connection mode for AI workflows.

   Andrei Lepikhov <lepihov@gmail.com> — 2026-03-16T21:01:22Z

   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
   
   
   
   

5. Re: Read-only connection mode for AI workflows.

   Bruce Momjian <bruce@momjian.us> — 2026-03-16T21:25:00Z

   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.
   
   
   
   

6. Re: Read-only connection mode for AI workflows.

   Andrei Lepikhov <lepihov@gmail.com> — 2026-03-17T10:04:25Z

   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
   
   
   
   

7. Re: Read-only connection mode for AI workflows.

   Bruce Momjian <bruce@momjian.us> — 2026-03-17T13:52:24Z

   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.
   
   
   
   

8. Re: Read-only connection mode for AI workflows.

   Andrei Lepikhov <lepihov@gmail.com> — 2026-03-17T14:05:08Z

   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
   
   
   
   

9. Re: Read-only connection mode for AI workflows.

   Bruce Momjian <bruce@momjian.us> — 2026-03-17T14:11:20Z

   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.
   
   
   
   

10. Re: Read-only connection mode for AI workflows.

    Bruce Momjian <bruce@momjian.us> — 2026-03-17T16:31:56Z

    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.
    
    
    
    

11. Re: Read-only connection mode for AI workflows.

    Peter Eisentraut <peter@eisentraut.org> — 2026-03-18T13:48:41Z

    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.
    
    
    
    
    

12. Re: Read-only connection mode for AI workflows.

    Andres Freund <andres@anarazel.de> — 2026-03-18T14:26:26Z

    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
    
    
    
    

13. Re: Read-only connection mode for AI workflows.

    Andrei Lepikhov <lepihov@gmail.com> — 2026-03-18T14:36:31Z

    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
    
    
    
    

14. Re: Read-only connection mode for AI workflows.

    Andrei Lepikhov <lepihov@gmail.com> — 2026-03-19T07:44:15Z

    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
    
    
    
    

15. Re: Read-only connection mode for AI workflows.

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-19T07:53:13Z

    Hi
    
    čt 19. 3. 2026 v 8:44 odesílatel Andrei Lepikhov <lepihov@gmail.com> napsal:
    
    > 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.
    >
    
    It doesn't cover  possibility to set GUC by set_config function
    
    Regards
    
    Pavel
    
    
    > [1] https://github.com/danolivo/safesession/
    > [2] https://github.com/pierreforstmann/pg_readonly
    >
    > --
    > regards, Andrei Lepikhov,
    > pgEdge
    >
    >
    >
    

16. Re: Read-only connection mode for AI workflows.

    Andrei Lepikhov <lepihov@gmail.com> — 2026-03-19T08:40:15Z

    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
    
    
    
    

17. Re: Read-only connection mode for AI workflows.

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-19T09:49:24Z

    Hi
    
    čt 19. 3. 2026 v 9:40 odesílatel Andrei Lepikhov <lepihov@gmail.com> napsal:
    
    > 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.
    >
    
    I am sorry. I missed so you used standard_ExecutorStart
    
    Regards
    
    Pavel
    
    
    >
    > --
    > regards, Andrei Lepikhov,
    > pgEdge
    >
    

18. Re: Read-only connection mode for AI workflows.

    Peter Eisentraut <peter@eisentraut.org> — 2026-03-19T10:09:26Z

    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.
    
    
    
    
    

19. Re: Read-only connection mode for AI workflows.

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-03-19T19:39:18Z

    Hi,
    
    On Wed, Mar 18, 2026 at 7:36 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
    
    > 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?
    >
    
    +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
    

20. Re: Read-only connection mode for AI workflows.

    Jacob Champion <jacob.champion@enterprisedb.com> — 2026-03-19T21:59:07Z

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

21. Re: Read-only connection mode for AI workflows.

    Greg Sabino Mullane <htamfids@gmail.com> — 2026-03-20T12:32:24Z

    On Thu, Mar 19, 2026 at 6:09 AM Peter Eisentraut <peter@eisentraut.org>
    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
    

22. Re: Read-only connection mode for AI workflows.

    Jelte Fennema <postgres@jeltef.nl> — 2026-03-23T09:10:14Z

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

23. Re: Read-only connection mode for AI workflows.

    Isaac Morland <isaac.morland@gmail.com> — 2026-03-23T11:04:18Z

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

24. Re: Read-only connection mode for AI workflows.

    Bruce Momjian <bruce@momjian.us> — 2026-03-23T17:43:31Z

    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.