How to Configure Copilot Studio Postgres Permissions
You set up copilot studio postgres permissions by making a dedicated database role with strict read rights. Then you give that role only the tables and columns your bot needs. Many teams hurry this setup and reuse a broad app role for their bots. When roles are too broad, a bad prompt can read secret rows or change live tables. A good setup starts inside the database with tight role rules. How to set up copilot studio postgres permissions in the database Start by creating a group role in Post
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 permissions by making a dedicated database role with strict read rights. Then you give that role only the tables and columns your bot needs. Many teams hurry this setup and reuse a broad app role for their bots. When roles are too broad, a bad prompt can read secret rows or change live tables. A good setup starts inside the database with tight role rules.
How to set up copilot studio postgres permissions in the database
Start by creating a group role in Postgres for your bot traffic. Group roles help you manage rights when you have many users or services. You grant rights to the group role first. Then you add your specific bot login user as a member of that group.
Next, check the default rights that every user gets through the PUBLIC role. By default, Postgres gives the PUBLIC role connect rights and temporary table rights on databases. It also gives PUBLIC the right to run functions and procedures. Object owners can revoke these default rights so the bot cannot run extra code.
You should also check the public schema if you run an older database. In Postgres 15 and newer, regular users cannot create objects in the public schema by default. Older databases, or ones upgraded from older versions, still allow this unless an admin revokes it. Revoke create rights on the public schema from PUBLIC to keep the bot from making tables.
Finally, grant the select right only on the exact data the bot needs. In Postgres, you can grant select on a whole table, a view, or specific columns. Granting select on safe columns stops the bot from reading every column in a wide table. Keep in mind that the select right also allows the copy out command on those columns.
Why static database roles leave an operational gap
Native database roles solve the first big problem of access control. They decide who can log in and which objects that user can touch. A read-only role stops the bot from dropping tables or changing rows. Column grants also keep secret password hashes out of reach.
Still, static roles do not check what individual queries do while they run. Once the bot logs in, the database trusts every valid query from that user. If a table holds customer emails, phone numbers, or card numbers, the role reads them in plain text. If the bot has select rights on that column, every query returns the raw values.
AI Agent Permissions + Copilot Security Implications: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also share one login across many different chat users. The database log only shows the single bot role name for every query. Admins cannot see which human user asked the bot to run a specific query. Also, a read-only role cannot block a slow query that scans millions of rows at once. Teams need a way to inspect queries in flight and mask fields like national IDs in results.
How to keep bot database credentials and queries safe
Treat every bot login like an untrusted user from outside your network. Separate the roles used by human engineers from the roles used by automated bots. Rotate the bot password often and store it in a secure secret store. Review your table and column grants each time you add new tables to your schema.
When you alter default privileges for future tables, remember how Postgres applies those rules. Default privileges only apply to objects created by the specific role that you target in the rule. If a migration tool creates a new table, test your bot role to verify its rights.
Add query and result controls beside Postgres
Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev runs beside your database as a sidecar for your Postgres. It reads plain and prepared queries on the wire with no change to your users or drivers.
When a tool or agent sends SQL, hoop.dev Postgres controls check each statement against your deny rules. An AI Analyzer you configure can also score intent or hold a risky query for human review. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.
Frequently asked questions
Can I restrict a Postgres role to read only specific columns?
Yes, Postgres lets you grant the select privilege on specific columns of a table or view. The bot role can query only those named columns and will get an error on others.
Why can a new read-only role still run database functions?
Postgres grants execute rights on functions and procedures to the special PUBLIC role by default. Every new role gets PUBLIC rights unless the object owner revokes them from PUBLIC.
Does revoking create on the public schema affect Postgres 15 and newer?
Postgres 15 and later remove public schema create rights from PUBLIC by default. You only need to revoke it on older versions or databases upgraded from older versions.
Related guides
In this post