How to Configure Copilot Studio Postgres Read Only Access
You set up copilot studio postgres read only access by creating a restricted database role. You then give that role only SELECT permissions before linking your bot. Many teams start by reusing an existing app account to save time. That choice gives an AI bot the power to change or delete live rows. Overly broad roles also let the bot read every table in your database. A safe setup requires strict database privileges and careful schema rules from day one. How to set up copilot studio postgres r
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up copilot studio postgres read only access by creating a restricted database role. You then give that role only SELECT permissions before linking your bot. Many teams start by reusing an existing app account to save time. That choice gives an AI bot the power to change or delete live rows. Overly broad roles also let the bot read every table in your database. A safe setup requires strict database privileges and careful schema rules from day one.
How to set up copilot studio postgres read only access
Start by creating a group role in PostgreSQL for your read-only bots. Group roles let you manage privileges in one place when you have many users. You then create a specific login user for the bot and add it to that group. Next, grant the group role the SELECT privilege on only the tables it needs. The SELECT privilege lets a role read from a whole table or view. It also lets you limit reading to specific columns on that table. Granting only safe columns stops the bot from pulling secret fields into its context. Keep in mind that SELECT also allows the role to export table data.
You must also check the default rights that PostgreSQL gives to every user. A special role called PUBLIC grants basic rights to every role on the system. By default, PUBLIC gets the right to log in and make temporary tables. It also gets the right to run functions and stored procedures in the database. If a function changes rows, a read-only user could still trigger it through PUBLIC. The database owner should revoke those default PUBLIC rights on sensitive functions and databases. In older PostgreSQL versions before version 15, PUBLIC could also create objects in the public schema. If you run an older or upgraded database, revoke that create right from PUBLIC.
What native database roles solve and what they miss
A restricted database role solves the problem of accidental data writes and schema drops. When the bot logs in with a read-only role, the database blocks write statements. The bot cannot insert, update, or delete rows in your tables. Column permissions also block queries that try to read restricted columns directly.
Still, native database roles only check who logs in and which objects they touch. They do not inspect what individual queries do while running in flight. A bot with SELECT rights on a customer table can still pull every row at once. If the table holds emails, phone numbers, or card numbers, those values return in full. Native roles do not rewrite or mask matched fields in query results on the fly. A bad prompt can also trick the bot into running a massive join. That query can lock up CPU and memory on your database server. Plus, shared bot credentials make it hard to see which person triggered a specific query.
Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Managing ongoing schema changes and bot permissions
Database schemas change often as engineering teams ship new features and tables. When a team creates a new table, your read-only group does not get access right away. You have to grant SELECT rights on new tables or set up default privilege rules. At the same time, someone might add a new column with national IDs to an old table. If the bot has table-level SELECT rights, it can read that new column right away. Teams must review table grants often or use column-level grants on every single table. Keeping these grants clean takes steady work from your database admins as the app grows.
Run guardrails and masking on the Postgres connection
Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.
On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.
Frequently asked questions
Can a PostgreSQL read-only role still change data through functions?
Yes, the PUBLIC role lets all users run functions by default. If a function runs with owner rights and edits rows, the bot can trigger it. You must revoke function execution rights from PUBLIC to block this path.
How do I limit which columns the bot can read in a table?
PostgreSQL lets you grant the SELECT privilege on specific columns instead of the whole table. If the bot queries a column outside that list, the database rejects the query.
Does revoking write access stop a bot from reading emails or card numbers?
No, a standard read-only grant still returns raw text for every allowed column. To rewrite emails, phone numbers, or card numbers in results, you need dynamic data masking.
Related guides
In this post