How to Configure RDS Postgres Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up rds postgres column level security by revoking table read rights first. Then you grant the read privilege on specific safe columns to a database role. Teams often grant broad table rights to speed up daily database work. Those wide rights expose sensitive fields like emails, phone numbers, and card numbers. Fixing this requires a clear plan for database roles and column grants. How rds postgres column level security works In PostgreSQL, the read privilege applies to whole tables or

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 column level security by revoking table read rights first. Then you grant the read privilege on specific safe columns to a database role. Teams often grant broad table rights to speed up daily database work. Those wide rights expose sensitive fields like emails, phone numbers, and card numbers. Fixing this requires a clear plan for database roles and column grants.

How rds postgres column level security works

In PostgreSQL, the read privilege applies to whole tables or specific columns. It also works on views, materialized views, and other table-like objects. This same privilege lets a user export data from those allowed columns. To restrict a table, an owner first removes table-level read grants from roles. Next, the owner grants read rights only on the non-sensitive columns.

Database admins must also check the special system role that represents all users. By default, that public role gets rights to log in and make temporary tables. It also gets rights to run functions and procedures across the database. Object owners can revoke these default grants at any time to tighten access. In PostgreSQL 15 and later, regular users cannot create objects in the default schema. Older databases, or upgraded ones, still need an admin to revoke that create right.

Managing column grants for one user at a time creates too much work. Instead, teams create group roles to hold the specific column privileges. Admins then add individual user accounts as members of those group roles. When a new engineer joins, they join the group and get the right columns.

Why native column grants are hard to maintain

Native column privileges block a user from reading restricted fields directly. Still, they cause friction for daily app queries and support work. If a user runs a broad select query on a restricted table, it fails. The database blocks the whole query if one unapproved column is in the request. Engineers must rewrite every query to name only the allowed columns.

Schema changes also add heavy upkeep for the database team. Every new table or column needs fresh privilege grants for each group role. Default privilege rules only apply to objects created by the specific target role. If a migration tool runs under a different role, new columns miss those rules.

Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native column privileges leave open

Native database roles decide who logs in and which columns they can read. They do not check what individual queries do in flight once approved. A role with access to emails or national IDs can still pull every row by default. Native grants cannot rewrite matched fields in results when a user runs a query.

Shared app roles and break-glass accounts also bypass column rules during incidents. When an engineer uses an admin role to fix a bug, all columns open up by default. The database cannot ask for peer approval on a single risky statement. It also cannot mask card numbers or phone numbers on the fly for that session.

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 happens when a user queries a whole table with restricted columns?

PostgreSQL rejects the entire query and returns a permission error. The user must write out the exact allowed column names in the query.

Do column privileges stop users from exporting allowed data?

No, the read privilege on specific columns also allows data exports for those columns. Users can still copy all rows from the columns they have rights to read by default.

How should teams assign column permissions to many engineers?

Admins should create a group role and grant the specific column rights to it. Then they add each engineer's database user account into that group role.

The query runs. The emails stay hidden.