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
