public inbox for [email protected]help / color / mirror / Atom feed
Read-only connection mode for AI workflows. 20+ messages / 12 participants [nested] [flat]
* Read-only connection mode for AI workflows. @ 2025-08-07 16:46 Mat Arye <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Mat Arye @ 2025-08-07 16:46 UTC (permalink / raw) To: pgsql-hackers 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-16 19:28 Jack Bonatakis <[email protected]> parent: Mat Arye <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Jack Bonatakis @ 2026-03-16 19:28 UTC (permalink / raw) To: pgsql-hackers --5f522b20fbcb4e73b5ce299be30dc959 Content-Type: text/plain Content-Transfer-Encoding: 7bit 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. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-16 21:01 Andrei Lepikhov <[email protected]> parent: Jack Bonatakis <[email protected]> 0 siblings, 2 replies; 20+ messages in thread From: Andrei Lepikhov @ 2026-03-16 21:01 UTC (permalink / raw) To: Jack Bonatakis <[email protected]>; pgsql-hackers 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-16 21:25 Bruce Momjian <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: Bruce Momjian @ 2026-03-16 21:25 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-17 10:04 Andrei Lepikhov <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Andrei Lepikhov @ 2026-03-17 10:04 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-17 13:52 Bruce Momjian <[email protected]> parent: Andrei Lepikhov <[email protected]> 0 siblings, 2 replies; 20+ messages in thread From: Bruce Momjian @ 2026-03-17 13:52 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-17 14:05 Andrei Lepikhov <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 2 replies; 20+ messages in thread From: Andrei Lepikhov @ 2026-03-17 14:05 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-17 14:11 Bruce Momjian <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 0 replies; 20+ messages in thread From: Bruce Momjian @ 2026-03-17 14:11 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-17 16:31 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 0 replies; 20+ messages in thread From: Bruce Momjian @ 2026-03-17 16:31 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-18 13:48 Peter Eisentraut <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: Peter Eisentraut @ 2026-03-18 13:48 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; Bruce Momjian <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers 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. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-18 14:26 Andres Freund <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Andres Freund @ 2026-03-18 14:26 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Bruce Momjian <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-19 07:44 Andrei Lepikhov <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 2 replies; 20+ messages in thread From: Andrei Lepikhov @ 2026-03-19 07:44 UTC (permalink / raw) To: Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-19 07:53 Pavel Stehule <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: Pavel Stehule @ 2026-03-19 07:53 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> Hi čt 19. 3. 2026 v 8:44 odesílatel Andrei Lepikhov <[email protected]> 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 > > > ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-19 09:49 Pavel Stehule <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 0 replies; 20+ messages in thread From: Pavel Stehule @ 2026-03-19 09:49 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> Hi čt 19. 3. 2026 v 9:40 odesílatel Andrei Lepikhov <[email protected]> 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 > ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-19 19:39 SATYANARAYANA NARLAPURAM <[email protected]> parent: Andres Freund <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: SATYANARAYANA NARLAPURAM @ 2026-03-19 19:39 UTC (permalink / raw) To: Andrei Lepikhov <[email protected]>; +Cc: Andres Freund <[email protected]>; Peter Eisentraut <[email protected]>; Bruce Momjian <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers Hi, On Wed, Mar 18, 2026 at 7:36 AM Andrei Lepikhov <[email protected]> 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-19 21:59 Jacob Champion <[email protected]> parent: SATYANARAYANA NARLAPURAM <[email protected]> 0 siblings, 0 replies; 20+ messages in thread From: Jacob Champion @ 2026-03-19 21:59 UTC (permalink / raw) To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Andres Freund <[email protected]>; Peter Eisentraut <[email protected]>; Bruce Momjian <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers On Thu, Mar 19, 2026 at 12:39 PM SATYANARAYANA NARLAPURAM <[email protected]> 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-20 12:32 Greg Sabino Mullane <[email protected]> parent: Andrei Lepikhov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: Greg Sabino Mullane @ 2026-03-20 12:32 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> On Thu, Mar 19, 2026 at 6:09 AM Peter Eisentraut <[email protected]> wrote: > Here is a stalled project to implement ALTER SYSTEM READ ONLY: > > https://www.postgresql.org/message-id/flat/CAAJ_b97KZzdJsffwRK7w0XU5HnXkcgKgTR69t8cOZztsyXjkQw%40mai... 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 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-23 09:10 Jelte Fennema-Nio <[email protected]> parent: Greg Sabino Mullane <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Jelte Fennema-Nio @ 2026-03-23 09:10 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Andrei Lepikhov <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <[email protected]> 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%40m... ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-23 11:04 Isaac Morland <[email protected]> parent: Jelte Fennema-Nio <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Isaac Morland @ 2026-03-23 11:04 UTC (permalink / raw) To: Jelte Fennema-Nio <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Peter Eisentraut <[email protected]>; Andrei Lepikhov <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers; Bruce Momjian <[email protected]>; Andres Freund <[email protected]> On Mon, 23 Mar 2026 at 05:10, Jelte Fennema-Nio <[email protected]> wrote: > On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <[email protected]> > 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. > ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Read-only connection mode for AI workflows. @ 2026-03-23 17:43 Bruce Momjian <[email protected]> parent: Isaac Morland <[email protected]> 0 siblings, 0 replies; 20+ messages in thread From: Bruce Momjian @ 2026-03-23 17:43 UTC (permalink / raw) To: Isaac Morland <[email protected]>; +Cc: Jelte Fennema-Nio <[email protected]>; Greg Sabino Mullane <[email protected]>; Peter Eisentraut <[email protected]>; Andrei Lepikhov <[email protected]>; Jack Bonatakis <[email protected]>; pgsql-hackers; Andres Freund <[email protected]> On Mon, Mar 23, 2026 at 07:04:18AM -0400, Isaac Morland wrote: > On Mon, 23 Mar 2026 at 05:10, Jelte Fennema-Nio <[email protected]> wrote: > > On Fri, 20 Mar 2026 at 13:33, Greg Sabino Mullane <[email protected]> > 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. ^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2026-03-23 17:43 UTC | newest] Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-07 16:46 Read-only connection mode for AI workflows. Mat Arye <[email protected]> 2026-03-16 19:28 ` Jack Bonatakis <[email protected]> 2026-03-16 21:01 ` Andrei Lepikhov <[email protected]> 2026-03-16 21:25 ` Bruce Momjian <[email protected]> 2026-03-17 10:04 ` Andrei Lepikhov <[email protected]> 2026-03-17 13:52 ` Bruce Momjian <[email protected]> 2026-03-17 14:05 ` Andrei Lepikhov <[email protected]> 2026-03-17 14:11 ` Bruce Momjian <[email protected]> 2026-03-18 13:48 ` Peter Eisentraut <[email protected]> 2026-03-18 14:26 ` Andres Freund <[email protected]> 2026-03-19 19:39 ` SATYANARAYANA NARLAPURAM <[email protected]> 2026-03-19 21:59 ` Jacob Champion <[email protected]> 2026-03-17 16:31 ` Bruce Momjian <[email protected]> 2026-03-19 07:44 ` Andrei Lepikhov <[email protected]> 2026-03-19 07:53 ` Pavel Stehule <[email protected]> 2026-03-19 09:49 ` Pavel Stehule <[email protected]> 2026-03-20 12:32 ` Greg Sabino Mullane <[email protected]> 2026-03-23 09:10 ` Jelte Fennema-Nio <[email protected]> 2026-03-23 11:04 ` Isaac Morland <[email protected]> 2026-03-23 17:43 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox