Read-only connection mode for AI workflows.

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