How to Configure RDS Postgres Row Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up rds postgres row level security by turning it on for a table and adding filter rules. These rules check the active database role before returning or changing any rows. Teams often start by giving one broad role to all apps and users. That wide permission lets anyone read every row in a shared table by default. If a bug or bad query runs, it exposes data across all tenants. How rds postgres row level security works with roles Postgres checks table privileges first, and then it check

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 row level security by turning it on for a table and adding filter rules. These rules check the active database role before returning or changing any rows. Teams often start by giving one broad role to all apps and users. That wide permission lets anyone read every row in a shared table by default. If a bug or bad query runs, it exposes data across all tenants.

How rds postgres row level security works with roles

Postgres checks table privileges first, and then it checks row rules. You can set up group roles to manage privileges when you have many users. You then grant each user membership in the right group role. Next, you grant the read privilege on the table or on specific columns. This privilege allows reading from a table, view, or materialized view. It also allows copying data out of the table.

Be careful with the special role named PUBLIC on your database instance. This special role grants a privilege to every role on the system. By default, every role gets rights to connect and make temporary tables. Every role also gets default rights to run functions and procedures. Object owners can revoke these default rights to lock down the system. In newer versions, the default setup blocks users from creating objects in the public schema. On older or upgraded databases, an admin must revoke that creation right from PUBLIC.

Once basic role rights are clean, you turn on row rules for your table. If you turn on row level security without a rule, the table hides all rows. You must write a rule that matches the user role or session variable to a column. For example, a rule can check if the tenant column matches the current role name. When a user runs a read or write query, the database filters the rows. The user only touches the rows that pass the rule check.

What native row rules solve and what they miss

Native row rules are great for multi-tenant apps and shared analytics tables. They keep different customer records apart inside the exact same table. You do not need to build dozens of custom views for every single team. The database engine enforces the row filter on every read and write query.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, native rules only check which database role logs in to run the query. Most engineering teams and apps share a few pooled database roles to save resources. When twenty engineers share one read role, row rules treat them all as one person. You cannot tell which actual human ran a heavy query from the database logs. Also, row rules only filter whole rows, not sensitive fields inside an allowed row. If a support engineer needs to read an order row, they also see the card numbers. Row rules cannot rewrite emails, phone numbers, or national IDs inside the query result. Finally, table owners and admin roles bypass row rules by default unless you force them. Managing thousands of database roles for every human user becomes hard to maintain over time.

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

What happens when you enable row level security without a policy?

Postgres uses a default deny rule when you turn on row level security. No regular user can read or write any rows until you create a permissive rule. Table owners and superusers still bypass this block unless you force row rules for owners.

How do group roles help with row level security?

Group roles let you manage table privileges and row rules for many users at once. Instead of writing a rule for each person, you write one rule for the group role. You then add or remove users from that group role as your team changes.

Does row level security hide sensitive columns inside a valid row?

No, row level security only decides which rows a role can read or change. To restrict columns, you must grant read rights on specific columns only, or mask results in flight.

The query runs. The emails stay hidden.