How to Configure Aurora Postgres Column Level Security
You set up aurora postgres column level security by granting read rights on chosen columns. First, you remove full table rights from the user role and assign individual column rules. Many engineering teams start by giving broad read rights on whole tables. This approach lets analysts and developers run queries without delay. Still, broad table permissions expose sensitive fields like emails, phone numbers, and card numbers. One wide query can pull private user data onto a developer laptop. Narr
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 aurora postgres column level security by granting read rights on chosen columns. First, you remove full table rights from the user role and assign individual column rules.
Many engineering teams start by giving broad read rights on whole tables. This approach lets analysts and developers run queries without delay. Still, broad table permissions expose sensitive fields like emails, phone numbers, and card numbers. One wide query can pull private user data onto a developer laptop. Narrowing those rights down to safe columns reduces that risk right away.
How aurora postgres column level security works
The database engine lets you grant read access on single columns of a table. This same rule also applies to views and materialized views. When you grant this privilege, the role can also export those allowed columns. Before you add specific column grants, you must check existing table permissions. If a role already holds read access on the whole table, column rules do nothing. You must revoke the table-level read grant first.
Next, you must check the special group called PUBLIC. Every role on the system belongs to PUBLIC by default. New databases give PUBLIC the right to connect and make temporary tables. It also gets the right to run functions and procedures. The object owner can revoke these default grants at any time. If PUBLIC holds read access on a table, every user can read every column by default. Always clear out broad grants from PUBLIC before setting up strict column rules.
You should also check who can create new objects in the public schema. In version 15 and newer, regular users cannot create objects there by default. Older databases, or upgraded ones, may still allow this action. An admin fixes this by revoking the create right on the public schema from PUBLIC.
Managing group roles at scale
Assigning column grants to single users breaks down when your team grows. Instead, you can set up group roles to manage permissions for many people. You grant the allowed column list to the group role once. Then you add individual user accounts as members of that group role. When a new engineer joins, you just add them to the group.
Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Even with group roles, daily schema changes create heavy maintenance work. Every time an engineer adds a safe column, an admin must update the grants. If an engineer runs a wildcard query, the database rejects the whole statement. The user must type out every allowed column name by hand. This behavior breaks many database tools and reporting apps that expect full table structures.
What native column grants leave open
Native column grants control which fields a role can request after logging in. They operate as a binary switch at the schema level. A user can either read the entire column or get an error message. Native grants cannot inspect what individual queries do in flight. They also cannot rewrite sensitive values like national IDs or card numbers in results.
Also, teams often share a single role for debugging or reporting tasks. Once a user logs in with that role, they get full access to allowed columns. You cannot require peer approval for a single risky query using native grants alone. You also cannot mask phone numbers or emails on the fly while keeping queries working.
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 read all columns after I add column grants by default?
Column grants add permissions rather than subtracting them. If the user or PUBLIC still holds table-level read rights, they can read everything. You must revoke the table-level read right first.
What happens when a user runs a wildcard query on a restricted table?
The database engine blocks the entire query and returns a permission error. The user must list only the specific columns they have rights to read.
How should we assign column permissions for a large engineering team?
Create a group role and grant the specific column permissions to that role. Then grant membership in that group role to each team member.
Related guides
In this post