How to Configure Azure Postgres Grant Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up azure postgres grant permissions, you assign database and table rights to specific roles. You then map those roles to individual users or group roles so they can read or write data. Most teams start by giving developers broad database roles to speed up daily work. Over time, those wide roles let users read private customer data or drop key tables by mistake. How azure postgres grant permissions work in practice PostgreSQL manages all database access through roles. A role can act as

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 azure postgres grant permissions, you assign database and table rights to specific roles. You then map those roles to individual users or group roles so they can read or write data. Most teams start by giving developers broad database roles to speed up daily work. Over time, those wide roles let users read private customer data or drop key tables by mistake.

How azure postgres grant permissions work in practice

PostgreSQL manages all database access through roles. A role can act as a single user or as a group role for many users. When you manage a large team, you set up group roles first. You then grant specific rights to the group role and add user roles as members.

Access starts at the database and schema levels. Every database has a special role named PUBLIC that includes every role on the system. By default, PUBLIC gets the right to connect to databases and create temporary tables. It also gets the right to run functions and procedures. An object owner can take away these default rights at any time.

In PostgreSQL 15 and newer, users cannot create objects in the public schema by default. Older databases, or ones upgraded from older versions, still allow this behavior. On those databases, an admin must revoke the create right on the public schema from PUBLIC.

After locking down schemas, you grant rights on tables, views, and materialized views. The select right lets a role read all columns or just specific columns of a table by default. It also lets the role export data with the copy command. You can grant insert, update, or delete rights on tables that need data changes.

Why static database roles leave a security gap

Native database roles do a good job of sorting out who can log in. They also set clear base rules for which tables a role can read or change. This native setup stops unauthorized users from entering the database at the start of a session.

Still, static roles do not check what individual queries do after a user logs in. Once a role has select rights on a table, every query can read every row by default. A developer with read rights can pull emails, phone numbers, card numbers, or national IDs. Because select rights also allow data exports, a user can dump a whole table at once.

Azure RBAC + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Column permissions help a little, but they break many apps and slow down debugging. If you hide a column through native grants, queries that select all columns will fail by default. Teams also struggle to manage standing write rights during production incidents. When an engineer needs to fix one bad row, they often get full table write rights. If that permission stays active, a bad script can wipe out production rows days later. Native roles also lack a built-in step to approve risky commands before they run.

How to keep role management clean over time

You can reduce risk by keeping role grants small and easy to read. Never grant table rights straight to a single user account. Always attach permissions to group roles that match clear job duties, like read-only support or app writer.

Check the PUBLIC role on every new database before you load private data. Revoke default connect and execute rights from PUBLIC if all users should not have them. Set default privileges so new tables get the right group grants when a migration runs. Finally, review role members each month and remove users who changed teams.

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 sits inline in the wire protocol beside your database as a sidecar for your Postgres. For every plain or prepared query an agent or human runs against production, hoop.dev decides to pass, mask, block, or escalate it for review.

When a tool or agent sends SQL, hoop.dev Postgres controls block banned statements before execution or hold risky ones for human approval via an AI Analyzer you configure. 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 grant read access to only a few columns in a table?

Yes, PostgreSQL lets you grant the select privilege on specific columns of a table or view. Users with that grant can query only the named columns and will get an error on others.

Why can new users still run functions When I granted them no roles?

Every role inherits permissions from the special PUBLIC role by default. PUBLIC includes the right to run functions and connect to databases unless an owner revokes those rights.

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

PostgreSQL 15 and later block this by default. On older or upgraded databases, an admin must revoke the create privilege on the public schema from PUBLIC.

The query runs. The emails stay hidden.