"Safe database agent"

[thin patrol]

I want to build an assistant which can create either READ ONLY queries on my postgres data, or create some tables for caching/computation, but for safety I want to restrict it to a TEMPORARY SCHEMA that is auto-deleted once the agent is done, also I'd like to limit the total size of those tables, and the total running time of the queries. How would that work?

here are a few ideas: https://chatgpt.com/share/67dbf1b6-915c-800b-803c-52c3824bb444

also https://neon.tech/postgresql/postgresql-tutorial/postgresql-temporary-table

[hollow pike]

or.... instead of building all those guardrails so the agent can't potentially corrupt any production data, you could create a Dagger pipeline that either branches (if supported) or exports a dump of your production data and just create a new DB where the agent can freely do what it needs.

At the end, you could get a diff from the source and then apply the changes you see fit.

[thin patrol]

could work too, but I guess exporting prod data might be quite heavy (depending on the size)

[hollow pike]

yes, it really depends on what the agent should help with.

having said that, creating a special "agent" user in the DB that has read-only access to tables and can create temp tables that should also work

[thin patrol]

yes it seemed to me a bit more lightweight that way

[hollow pike]

another thing to look into is to assign that user less priorities on its queries so it doesn't compete with the prod applications: https://wiki.postgresql.org/wiki/Priorities#:~:text=PostgreSQL has no facilities to,what limited prioritization is possible.