How to Configure RDS Postgres Read Only User
You set up an rds postgres read only user by granting select rights on schemas and tables. You also revoke write rights from default database roles to stop unwanted changes. Many teams start by sharing one broad database account across several developers. When permissions are too wide, one bad query can change or delete production records. A clean read-only setup prevents accidental writes and keeps data safer during fixing. How to set up an rds postgres read only user Database admins do not
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 rds postgres read only user by granting select rights on schemas and tables. You also revoke write rights from default database roles to stop unwanted changes. Many teams start by sharing one broad database account across several developers. When permissions are too wide, one bad query can change or delete production records. A clean read-only setup prevents accidental writes and keeps data safer during fixing.
How to set up an rds postgres read only user
Database admins do not assign table permissions to one person at a time. Instead, you can set up group roles to manage privileges for many users. You create one read-only group role and add individual user accounts as members. When a developer leaves the team, you remove their account from the group role.
Next, you must check what every account can do right out of the box. The special role name PUBLIC grants a privilege to every role on the system. By default, PUBLIC receives connect and temporary rights on databases. It also gets execute rights on functions and procedures. The object owner can revoke these default permissions to lock down the database.
You also need to check write permissions on your main schema. In PostgreSQL 15 and later, default settings block users from creating objects in the public schema. Older databases, or databases upgraded from earlier versions, still allow this behavior. On those older systems, an admin must revoke create rights on the public schema from PUBLIC.
After locking down defaults, you grant usage on the target schema to your read-only group. Then you grant the select privilege on the tables and views in that schema. The select privilege allows reading from any column or specific columns of a table or view. It also works on materialized views and other table-like objects. Keep in mind that the select privilege also allows exporting table data out. You should also set default privileges so new tables get these read rules too.
What native read-only roles solve and where they fall short
Native database roles do a great job of stopping accidental writes and table drops. Once a user logs in with a read-only role, the database blocks insert and delete statements. This setup solves the basic problem of separating people who read data from apps that write data.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, native database roles only check who logs in at the start of a session. They do not inspect what individual queries do while the session is active. If you grant select on a whole table, the user can read every row and column by default. That includes sensitive fields like emails, phone numbers, card numbers, and national IDs.
You can grant select on specific columns only, but that breaks many SQL queries. Developers often run broad queries during an incident and hit permission errors when columns are restricted. Maintaining column-level grants across hundreds of tables also takes a lot of manual work. Every time a migration adds a column, an admin must update the role grants.
Shared read-only accounts create another big gap for security and audit teams. When five engineers use the same read-only login, database logs show one role name. You cannot tell which person ran an expensive query or exported customer records. Rotation becomes painful too, because changing one shared password breaks scripts for the whole team.
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 rds postgres read only user still create tables?
Databases running versions older than PostgreSQL 15 let PUBLIC create objects in the public schema. Upgraded databases keep this old setting too. An admin must revoke create permissions on the public schema from the PUBLIC role.
Can I restrict a read-only role to specific columns in a table?
Yes, PostgreSQL lets you grant the select privilege on specific columns of a table or view. Still, queries that select all columns will fail unless the user specifies only the allowed columns.
Why can a new read-only user execute functions by default?
PostgreSQL grants execute permissions on functions and procedures to the special PUBLIC role by default. Since every user belongs to PUBLIC, the object owner must revoke those default rights to block execution.
Related guides
In this post