How to Configure Azure Postgres Revoke Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up an azure postgres revoke access workflow, remove rights from users or group roles. You also need to clear default permissions on tables, schemas, and the special PUBLIC role. Many teams start by giving broad database roles to devs for quick fixes. Over time, those extra rights stack up and create big security risks. When a user leaves or changes jobs, removing their rights becomes a hard task. How azure postgres revoke access works in practice Postgres manages permissions through ro

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.

To set up an azure postgres revoke access workflow, remove rights from users or group roles. You also need to clear default permissions on tables, schemas, and the special PUBLIC role. Many teams start by giving broad database roles to devs for quick fixes. Over time, those extra rights stack up and create big security risks. When a user leaves or changes jobs, removing their rights becomes a hard task.

How azure postgres revoke access works in practice

Postgres manages permissions through roles, which can represent single users or groups. Teams often set up group roles to manage permissions when there are many users. You grant a user membership in a group role to give them rights. To take those rights away, you remove the user from the group role. You can also take away specific rights like read or write from the role itself.

A common trap involves the special role name called PUBLIC. This special role grants a privilege to every role on the system. By default, PUBLIC gets the right to connect to databases and make temp tables. It also gets the right to run functions and procedures. Object owners can take away these default rights from PUBLIC to lock things down.

Schema permissions also matter when you lock down a cloud database. In Postgres 15 and later, regular users cannot make objects in the public schema by default. Older databases, or ones upgraded from older versions, still allow this behavior. On those systems, an admin must remove the create right on the public schema from PUBLIC. You must also check default permissions for future tables so old roles stay locked out.

Managing group roles and default permissions

When you remove rights directly from a table, you only fix existing objects. Future tables might still grant rights if someone set up default rules earlier. Also, permissions depend on who owns the table or schema. If a user owns a table, taking away their read right does not stop them. You must change the table owner before you drop or lock that user role.

Read permissions can also be tricky to clean up across a large database. The select right allows reading from a full table or just specific columns. It also lets a user copy data out of the table. If you grant column-level rights, you must remember to remove them at that same level. Tracking down every single table, view, and column takes hours of manual work.

Customer Support Access to Production + Azure RBAC: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native permission cleanup leaves open

Native database roles decide who can log in and which objects they can touch. They do not control what individual queries do while a session is active. A user who still needs read rights for their job can pull full tables. That includes sensitive fields like emails, phone numbers, card numbers, and national IDs.

Also, static database roles stay active until an admin remembers to run a cleanup script. If someone forgets to remove a role after an incident, the user keeps full rights. Teams often share role passwords too, which makes it impossible to tie queries to one person. To fix this, you need controls that work on every query in real time. Dynamic data masking rewrites matched fields like emails or card numbers in query results. This lets devs do their work while keeping sensitive customer data safe.

Check every Postgres query on the wire

Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres, inline in the Postgres wire protocol. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review. Your apps and agents keep their login details and change only the host and port in the connection string their tool uses.

Before a query runs, hoop.dev guardrails refuse bad deletes or drops with a real Postgres error, or hold risky queries for approval. On the way back, hoop.dev data masking rewrites sensitive values in returned rows in memory, and every verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.

Frequently asked questions

Why can a user still connect after I remove their role rights?

The special PUBLIC role gives every user connect rights to the database by default. You must take away the connect right from PUBLIC to stop unauthorized logins.

How do I stop users from making tables in the public schema?

Postgres 15 and newer versions block this by default for standard users. On older or upgraded databases, remove the create right on the public schema from PUBLIC.

Can I remove read rights from just one column in a table?

Yes, Postgres lets you grant and remove select rights on specific columns of a table. You can also use dynamic data masking to rewrite emails or phone numbers in results.

The query runs. The emails stay hidden.