How to Configure Azure Postgres Read Only User
You set up an azure postgres read only user by creating a database role. Then you grant that role select rights on your target schemas and tables. Many engineering teams start by sharing one broad admin account for quick debugging. That habit creates high risk because one bad query can drop production tables. Broad roles also expose private customer records to anyone running a quick check. Locking down accounts to read rights is a vital first step for safety. How an azure postgres read only us
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 an azure postgres read only user by creating a database role. Then you grant that role select rights on your target schemas and tables. Many engineering teams start by sharing one broad admin account for quick debugging. That habit creates high risk because one bad query can drop production tables. Broad roles also expose private customer records to anyone running a quick check. Locking down accounts to read rights is a vital first step for safety.
How an azure postgres read only user works
PostgreSQL manages database access through roles that can act as single users or groups. Instead of adding rules to each person, you should build a group role. You assign read permissions to that group and add individual logins as members. This setup keeps your permission model clean as your engineering team grows larger.
To read data, a role needs permission to connect to the target database. It also needs usage rights on the schema and select rights on tables. The select privilege lets a user read from a table or a view. It also lets the user export table data out through the copy command. You can grant select rights on a whole table or specific columns.
Every database also has a special built-in group called the public role. Every login on the system belongs to this public group at all times. By default, this group gets connect rights and can create temporary tables. It also gets permission to run functions and stored procedures on the server. In older versions, the public group could even create objects inside the default schema. Database owners must revoke these extra defaults so a read account cannot write.
Table grants only cover the tables that exist when you run the grant. When a migration role creates a new table later, the read role fails. You must alter default privileges for the role that runs your schema changes. That ensures future tables give select rights to your read group right away.
What native database roles solve and what they leave open
Native database roles stop accidental writes, drops, and schema changes from happening. They draw a clear line between app accounts that write and people who read. When an analyst logs in with a read account, the engine blocks updates. This setup solves the basic problem of unauthorized changes to your stored records.
Still, database roles only check who logs in at the start of a session. They do not inspect or control what individual queries do while in flight. A read role with table access returns every row and every sensitive column by default. If a table holds emails, phone numbers, or card numbers, queries return them. Maintaining column grants across hundreds of changing tables takes too much manual work. Most teams give up on column grants and grant full table reads instead.
Read-Only Root Filesystem + Azure RBAC: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also leave you with long-lived passwords or cloud tokens to rotate. Anyone with the password can run huge table exports from their local machine. Native role checks cannot rewrite sensitive fields like national IDs in query results. They also cannot pause a risky query for peer approval before it runs.
Managing group roles and schema defaults over time
Keeping read accounts clean requires strict rules around who creates new database objects. If three different deployment roles run migrations, you must set defaults for all three. If a developer creates a table by hand, the read group misses out. Teams must audit schema ownership often to keep read permissions working without gaps.
You also need a clear plan to check which functions a role can run. Because the public group can execute functions by default, custom functions pose risks. A poorly written function might change data even when a read user calls it. Revoking execute rights from the public role closes this hidden write path.
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 sits inline in the Postgres protocol beside Postgres, RDS, or Aurora as a sidecar for your Postgres. On every production query from an engineer or AI agent, hoop.dev decides whether to pass, mask, block, or hold it for approval.
First, hoop.dev guardrails refuse disallowed drops or deletes before they run, and risky statements can pause for human review. Next, hoop.dev data masking rewrites sensitive fields in returned rows in memory. 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
Why can my read-only role still create tables in the public schema?
Databases upgraded from older versions before version 15 allow public schema object creation. You must revoke create rights on that schema from the built-in public group.
Why does my read user get permission denied on newly created tables?
Standard select grants only apply to tables that exist at that exact moment. You must set default privileges for the specific role that creates new tables.
Can a read-only user still export full tables to a local file?
Yes, the standard select privilege also allows copying data out of the database. Native permissions cannot distinguish between a small check and a full data export.
Related guides
In this post