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