PostgreSQL RLS and Column Permissions

If you're relying on PostgreSQL Row Level Security (RLS) to protect your data, there's an important detail you need to understand:
RLS controls which rows a user can access, but it does not automatically control which columns they can read.
That distinction can become a serious security issue, especially when a public or anonymous role has SELECT access to more columns than it actually needs.

RLS Protects Rows, Not Columns​

RLS is a powerful security feature in PostgreSQL. It allows you to define policies that control which rows a role can access.
For example, you might have a marketplace where some profiles are publicly visible.
Your RLS policy could correctly allow anonymous users to see only profiles that are approved and public.

But there's another question:
What columns can that anonymous user actually read from those rows?
RLS and column privileges solve different problems.

Security layer	Controls
RLS	Which rows a role can access
Column privileges	Which columns a role can access
Application logic	How the application uses the returned data

If the public role has SELECT access to every column in the table, making a row visible through RLS can potentially expose more information than intended.

---

A Real-World Example​

Imagine a profile table containing both public and internal information:

• Name
• Profile picture
• Location
• Phone number
• Internal notes
• Approval status
• Suspension status

Your RLS policy might correctly expose only profiles that are supposed to be public.
But if the anon role also has SELECT privileges on every column, the row-level policy alone doesn't define which fields should be visible.

This becomes especially important in applications such as marketplaces, dashboards, and APIs where a public client may have direct access to database-backed data.
A public API key being visible in browser-side code doesn't automatically make the database insecure. The security boundary still depends on the permissions and policies attached to the role behind that key.

The important question is:
What can that role actually read? 👀

---

The Dangerous Part: Removing Columns Too Aggressively​

At first, the solution seems simple:
Give the public role access only to the columns that the API needs.
That's a good security principle.
But there's a subtle PostgreSQL behavior you need to account for.
A query can reference columns that aren't included in its final result.
For example, imagine your application has a shared filter that checks whether a profile is suspended:
is_suspended
The application might use that column inside a WHERE condition without ever returning it to the client.
If you remove SELECT permission on that column simply because it isn't part of the API response, a query that references it can fail with a permission error.
The same concept can matter for columns referenced by:

• WHERE conditions
• ORDER BY
• Subqueries
• Other expressions used by the query

That's why looking only at the final API response can give you an incomplete picture of the permissions your database queries actually require.

---

The Allowlist Should Follow the Query​

This is the key lesson:
Don't build your column allowlist only from the columns returned by the API.
Build it from the columns that the actual SQL operations need to reference.
Consider a query that logically does something like this:

SELECT id, name, avatar_url
FROM profiles
WHERE is_suspended = false
AND approval_status = 'approved';

The client may receive only:

• id
• name
• avatar_url

But the query also references:

• is_suspended
• approval_status

Those columns are part of the query even though they aren't part of the returned result.
So when designing column-level permissions, you need to consider what the database operation references, not only what the user ultimately sees.

---

Why This Can Break After Deployment​

One particularly nasty part of this problem is that changing column permissions can break existing application queries.
A shared filter might be used by:

• Profile pages
• Search
• Image listings
• Marketplace results
• API endpoints
• Other database-backed features

If those queries reference columns that no longer have the required permissions, the application can start failing after a permissions change.
And not every failure is necessarily obvious from the frontend.
Depending on the application and its error handling, you might see:

• Empty results
• Failed requests
• Cached empty responses
• Permission errors in backend logs
• Features that appear to work while returning incorrect results

That's why column-permission changes should be tested against the actual query paths used by the application.

---

A Better Way to Think About RLS​

RLS shouldn't be treated as the entire database security model.
Think about the layers separately:
1. Row security
Which records is this role allowed to access?
2. Column privileges
Which fields can this role access?
3. Query behavior
Which columns does the SQL actually reference?
4. Application behavior
What does the API or frontend do with the returned data?
5. Least privilege
Does the role have more access than it actually needs?
This layered approach makes it much easier to reason about database security.

---

Don't Build Permissions From the API Response Alone​

A common mistake is to inspect an API response and conclude:

"These are the only columns the user can see, so these are the only columns the role needs."

That's not necessarily true.
The SQL query may depend on additional columns for filtering, sorting, joins, expressions, or other operations.
So the safer workflow is:

1. Identify the queries used by the role.
2. Identify every column those queries reference.
3. Separate columns that need to be returned from columns that are only used internally.
4. Review the required privileges carefully.
5. Test the queries using the same role that the application uses.
6. Verify both successful access and denied access.

This helps prevent a security improvement from accidentally breaking application functionality. 🛡️

---

RLS Is Not a Complete Security Boundary​

RLS is extremely useful, but row-level filtering and column-level access are different security controls.
A policy can correctly determine that a user is allowed to access a particular row while the role's column privileges determine whether that role can actually read specific fields.
That's why relying on RLS alone can leave an important part of the database security model unexamined.
The goal isn't simply:
"Can this user access the row?"
You should also ask:
"Which parts of that row can this role access, and which columns does the query actually need?" 🔎

---

Final Takeaway​

RLS is a strong security feature, but it shouldn't be treated as the only layer protecting database data.
Rows and columns represent two different permission boundaries.
When you're designing an allowlist for a public or restricted database role, don't base it only on the fields visible in the API response.
Look at the actual SQL operations and the columns they reference.
A column that never appears in the final response may still be required by a WHERE condition, ORDER BY, subquery, or another part of the query.
And when you're tightening permissions, test the real application queries afterward.
Because sometimes the security bug isn't that your database gave someone access to the wrong row.
It's that the right row contained far more information than that role should have been able to read. 🔐

---

Frequently Asked Questions​

-----------------

Does PostgreSQL RLS control columns?​

No. RLS is designed to control access to rows. Column-level access is handled separately through PostgreSQL privileges.

Can a query use a column without returning it?​

Yes. A column can be referenced by parts of a query such as WHERE, ORDER BY, or subqueries even when that column isn't included in the final result.

Should I remove SELECT access from every column that isn't returned by my API?​

Not blindly. First determine which columns the actual queries need to reference, then design the privileges around those database operations.

Is RLS alone enough to secure a database?​

RLS is an important security layer, but database security generally requires multiple controls, including appropriate privileges, least-privilege roles, authentication, authorization, and careful application design.