How to Configure RDS Postgres Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up rds postgres least privilege by revoking broad public defaults first. Then you grant specific read or write rights to group roles. Many teams start by giving developers one shared admin account for the database. This wide role lets anyone drop tables or read private user records by mistake. Overly broad roles also make audits hard when multiple people share one login. How rds postgres least privilege works in practice A good permission model starts with the built-in public role. Po

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 least privilege by revoking broad public defaults first. Then you grant specific read or write rights to group roles. Many teams start by giving developers one shared admin account for the database. This wide role lets anyone drop tables or read private user records by mistake. Overly broad roles also make audits hard when multiple people share one login.

How rds postgres least privilege works in practice

A good permission model starts with the built-in public role. Postgres uses a special role called PUBLIC that includes every user on the system. By default, PUBLIC gets the right to enter databases and make temporary tables. It also gets the right to run functions and procedures. The database owner can revoke these default rights to lock down the system.

Schema rules also need close review during setup. In Postgres 15 and newer, regular users cannot make objects in the public schema. Older databases, or ones upgraded from older versions, still allow this behavior. Admins must revoke create rights on the public schema from PUBLIC on those systems.

Once defaults are tight, admins create group roles for different job duties. Group roles hold the exact rights needed for a task, like reading tables. Individual user accounts then join these group roles to get their permissions. For tighter control, Postgres lets you grant read rights on specific columns alone. A user can read safe columns of a table or view without full table access. That same read right also allows the user to export those specific columns.

Why native roles are not enough for live queries

Native database roles do a great job of controlling who can log in. They also define which tables or columns a role can read or change. Still, static grants only check permissions when a session starts or a query begins. They do not inspect the actual data flowing back to the user in flight.

Least Privilege Principle + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Consider an engineer who needs to check a customer table to fix a bug. Even with column grants, that engineer might still need to query an email column. A native read grant returns every email, phone number, or card number in clear text. Also, managing column grants across hundreds of tables takes hours of manual work. Every time a team adds a new table, someone must update the role grants.

Static roles also lack approval steps for one-off fixes in production. If a developer needs to run one update query, an admin must alter their role. If the admin forgets to remove that write right later, the permission keeps the session active. Teams need a way to inspect individual statements and rewrite sensitive fields in results.

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

What does the PUBLIC role do by default in Postgres?

The special PUBLIC role grants rights to every user on the database system. By default, it allows users to enter databases, make temporary tables, and run functions. Database owners can revoke these default rights to tighten security.

Can I limit read rights to specific columns in Postgres?

Yes, Postgres lets you grant read rights on individual columns of a table or view. This grant also allows the user to copy data out from those chosen columns. It helps when a table mixes public info with private columns.

How did Postgres 15 change public schema permissions?

Postgres 15 and later versions stop regular users from creating objects in the public schema. If you run an older version or upgraded from one, this rule is not active. You must revoke create rights on the public schema from the PUBLIC role yourself.

The query runs. The emails stay hidden.