How to Configure RDS Postgres User Roles
You set up rds postgres user roles by creating group roles and assigning table rights. Then you add individual login accounts as members of those groups. Most teams start with one shared admin account for all database tasks. That shortcut gives every developer and app full power to change or delete data. When one person makes a mistake, the whole production database can go down. Splitting permissions into clear roles stops small errors from becoming big outages. How rds postgres user roles wor
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 rds postgres user roles by creating group roles and assigning table rights. Then you add individual login accounts as members of those groups. Most teams start with one shared admin account for all database tasks. That shortcut gives every developer and app full power to change or delete data. When one person makes a mistake, the whole production database can go down. Splitting permissions into clear roles stops small errors from becoming big outages.
How rds postgres user roles work in practice
PostgreSQL uses roles to handle both user logins and permission groups. Instead of granting rights to each person, you build group roles for job functions. You can make one group for reading data and another for writing data. When a new engineer joins the team, you add their account to the group. When they leave, you remove them from the group in one step.
Permissions flow from the database level down to schemas, tables, and columns. Every system also has a special role called PUBLIC that includes every user. By default, PUBLIC allows anyone to log in and create temporary tables. It also lets any user run functions and procedures unless the owner removes those rights. On PostgreSQL 15 and newer, regular users cannot build objects in the public schema. If you run an older database or upgraded from one, check the public schema. An admin must revoke creation rights on that schema from PUBLIC to lock it down.
You can also control read rights down to single columns on a table. The SELECT privilege lets a role read specific columns of a table or view. Note that granting SELECT also allows the user to export data out. For new tables, you set default privileges so future objects inherit the right rules. Remember that default rules only apply to tables made by the specific owner you name.
Why native roles are only half the security story
Setting up database roles solves the problem of who can log in. It also defines which tables and columns each group can touch. This native setup works well for static apps with fixed service accounts. Still, database roles cannot inspect what happens inside a live user session. Once a user logs in with a write role, every table change is allowed.
Static roles also create work when engineers only need temporary write power for a fix. Admins often leave elevated roles assigned for days because revoking them by hand takes time. Also, column grants are an all-or-nothing choice inside the database engine. If support staff need to check an orders table, they might see raw customer emails. You have to build complex views or maintain separate roles for every single column rule.
User Provisioning (SCIM) + Lambda Execution Roles: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Closing the gap between static roles and live queries
Database roles check identity at login, not intent during a session. A person with read rights can still dump a full customer table at once. They can pull phone numbers, card numbers, and national IDs in one query. Native database logs often just show the shared group or service role name. That makes it hard to tie a bad query back to a specific person.
To fix this, teams pair native database roles with query-level controls. Instead of giving out dozens of database passwords, you verify user identity before each query. You can require peer approval before someone runs an update or delete statement. Sensitive fields like emails or card numbers get rewritten in the query results on the fly. This keeps your internal database role tree simple while keeping live 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 new users still run functions in my RDS Postgres database?
PostgreSQL grants function execution rights to the special PUBLIC role by default. Every new user inherits rights from PUBLIC by default when you create their account. The object owner must revoke execute privileges from PUBLIC to stop this behavior.
Do default privileges apply to tables created by any admin user?
No, default privileges only apply to objects created by the specific role you target. If three different admin roles run migrations, you must set defaults for all three. Many teams have engineers switch into one shared build role before creating tables.
Can I restrict a read-only role from seeing sensitive columns?
Yes, PostgreSQL lets you grant the SELECT privilege on specific columns of a table. Still, any query that selects all columns with a star will fail for that user by default. Many teams prefer dynamic data masking so standard queries still run and rewrite sensitive fields.
Related guides
In this post