How to Configure RDS Postgres Revoke Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up an rds postgres revoke access workflow by removing permissions from roles and clearing default rights. You must also cut active sessions and remove users from group roles. Many teams grant broad database roles during early app builds to move fast. Over time, these broad roles let too many people read or change production tables. Cleaning up these rights takes a clear plan across databases, schemas, and tables. How rds postgres revoke access works at the role level PostgreSQL uses r

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 revoke access workflow by removing permissions from roles and clearing default rights. You must also cut active sessions and remove users from group roles. Many teams grant broad database roles during early app builds to move fast. Over time, these broad roles let too many people read or change production tables. Cleaning up these rights takes a clear plan across databases, schemas, and tables.

How rds postgres revoke access works at the role level

PostgreSQL uses roles to control who can log in and read or write data. Instead of changing single user accounts, teams set up group roles to manage permissions. When a user joins a group role, they get all the rights of that group. To take away permissions, an admin removes the user from the group role. The admin can also strip table, view, or schema rights from the group itself.

A common trap during this cleanup involves the special PUBLIC role. In PostgreSQL, the PUBLIC role represents every user on the system. By default, PUBLIC gets the right to connect to databases and create temporary tables. It also gets the right to run functions and procedures. Even if you strip rights from a user role, they keep whatever PUBLIC allows. The object owner must remove these default rights from PUBLIC to lock down the database.

Schema permissions also need close checks during a cleanup. In PostgreSQL 15 and newer, regular users cannot create objects in the public schema by default. Older databases, or ones upgraded from older versions, still allow this behavior. On those older systems, an admin must remove the create right on the public schema from PUBLIC.

Handling default privileges and column rules

Removing current table permissions does not always stop access to future tables. PostgreSQL lets admins set default privileges for objects that roles create later. Those default rules apply only to objects made by the target creator role. If an app role creates a new table tomorrow, old default rules might grant read rights again. Admins must update default privilege rules for every role that runs schema changes.

Sometimes a team wants to remove access to only a few sensitive fields. PostgreSQL allows admins to grant read rights on specific columns of a table or view. To tighten access, you revoke the table-level read right and grant it back on safe columns. Keep in mind that the read privilege also allows data exports from those allowed columns.

Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native permission cleanup solves and where gaps remain

Native role cleanup fixes the problem of permanent, broad access inside the database engine. It decides which accounts can log in and which tables or columns they can touch. When you remove group memberships and lock down PUBLIC, unapproved users cannot query restricted tables.

Still, static database roles only check who logs in and which objects they target. They do not inspect what individual queries do in flight during an active session. A developer with valid read rights for debugging can still pull entire tables of customer data. If a column holds emails, phone numbers, card numbers, or national IDs, role rules are all-or-nothing. Dynamic data masking solves this by rewriting matched fields in query results without changing database roles. Teams also need query-level controls to approve risky statements before they run.

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

Why can a user still connect after I revoke their database permissions?

The special PUBLIC role grants database connect rights to every user by default. You must revoke the connect right from PUBLIC on that database to block logins.

Does revoking permissions stop queries that are already running?

Removing role permissions does not drop open network sessions on the database server. An admin must end the active backend processes to cut off open user sessions right away.

How do I remove write access in the public schema on older databases?

Databases created before PostgreSQL 15 let all users create objects in the public schema. An admin fixes this by revoking the create right on the public schema from PUBLIC.

The query runs. The emails stay hidden.