How to Configure Aurora Postgres Read Only User
You set up an aurora postgres read only user by granting select rights on target schemas. You also revoke default write rights from the built-in public group role. Many engineering teams start by sharing one broad database role across multiple developers. When permissions are too broad, a simple typo can delete production tables or expose sensitive records. How an aurora postgres read only user works PostgreSQL manages database permissions through roles. A role can act as a single user or 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.
You set up an aurora postgres read only user by granting select rights on target schemas. You also revoke default write rights from the built-in public group role. Many engineering teams start by sharing one broad database role across multiple developers. When permissions are too broad, a simple typo can delete production tables or expose sensitive records.
How an aurora postgres read only user works
PostgreSQL manages database permissions through roles. A role can act as a single user or as a group of users. When your team has many engineers, you should build a group role first. You attach read privileges to that group role. Then you add individual user roles into the group.
To let a role read data, you grant the select privilege on your tables. This privilege lets a user read from all columns or specific columns of a table by default. It also works on views and materialized views. Note that the select privilege also lets a user copy table data out.
Next, you must check default privileges for future tables. When an app migration creates a new table, your read role needs rights on it too. You set default privileges so the role gets select rights on new tables created by your migration user.
Why you must check the built-in public role
Granting select rights is only half of the job. Every database includes a special group role named PUBLIC. Every user on the system belongs to this PUBLIC role by default. By default, PUBLIC gets the right to connect to databases and make temporary tables. It also gets the right to run functions and stored procedures.
In PostgreSQL 15 and newer versions, regular users cannot create objects in the public schema. In older versions or upgraded databases, the PUBLIC role still holds create rights there. An admin must revoke create rights on the public schema from the PUBLIC role. The database owner should also revoke default function execution rights from PUBLIC. Otherwise, a read-only user might run a custom function that writes or deletes data.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Where native database roles fall short in daily work
Native database roles solve the problem of who can log in and modify tables. They block accidental table drops and unauthorized row updates at the storage engine level. Still, native roles only check permissions at login and table level. They do not inspect what individual queries return while a session is active.
A role with table-wide select rights can still dump full customer tables to a laptop. Column-level select grants exist in PostgreSQL, but they break most admin tools and standard queries. If a developer runs a broad select statement on a table with restricted columns, the query fails. Native roles also cannot rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in query results. Also, maintaining role grants across dozens of schemas and databases creates heavy manual work for database admins.
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
Can a read-only role still create tables in the public schema?
In PostgreSQL 15 and later, new databases block this by default. On older or upgraded databases, you must revoke create rights on the public schema from PUBLIC.
Does the select privilege allow bulk data exports?
Yes, the native select privilege allows a user to run copy commands that export table rows. It also lets users read every row in the permitted columns by default.
How do I limit a user to reading specific columns in a table?
PostgreSQL lets you grant the select privilege on individual columns instead of the whole table. Still, queries that ask for all columns will fail for that user by default.
Related guides
In this post