Row-level security for data mappings: what it is and when you need it
You have one customer table. The EU sales team should see only EU customers. The US team should see only US customers. The board sees everything.
Two ways to do this. Make three copies of the table, or apply row-level security. Most teams pick copies first. Most regret it within a quarter.
What row-level security actually is
Row-level security (RLS) is a rule that decides which rows of a table a user can see, based on the user and on attributes of the row itself. The data lives in one place. The filtering happens at read time, automatically, without the application asking for it.
In Postgres, it is CREATE POLICY. In other databases, it is views or application-layer filters or a built-in feature of the platform. The mechanic varies. The semantic does not. Same data, different views, enforced by the system.
Why copies are worse than they look
EU team gets customers_eu. US team gets customers_us. Board gets customers_all. It works on day one.
| Issue | Day one | Six months in |
|---|---|---|
| Single edit | "I will update all three" | Tables disagree; nobody knows which is canonical |
| New region | "Just clone the table" | The clone's schema drifts; reports break |
| User changes team | "I will adjust their permissions" | Permissions live in three places |
| Audit | "I can query each table" | Each table has its own log; the full picture takes a join |
RLS sidesteps every line of that table. One source of truth. Permissions become policies, not separate datasets.
Where RLS earns its keep
A few patterns where it is the right answer:
- Multi-region or multi-department teams. Same customer or product list, viewed differently by people in different geographies or business units.
- Tiered customer access. A B2B data product where free, paid, and enterprise tiers see different rows. One table, three policies.
- Partner portals. Several partners read the same registry; each sees only their own shipments or deals. No file exports.
- Overlapping internal views. Finance sees the customer with payment terms. Sales sees pipeline stage. Marketing sees campaign tags. Same row, different visible columns.
How RLS is usually implemented
| Approach | What it is | Best for | Tradeoff |
|---|---|---|---|
Postgres CREATE POLICY |
Database-layer rule | Most Postgres workloads | Postgres only |
| Views per role | Hand-coded views | Other databases | Multiplies fast |
| Application filtering | WHERE in your code |
Maximum flexibility | Easy to forget once |
| Platform-native | Built into the tool | Operational simplicity | Tied to that platform |
The right place to enforce RLS is as close to the data as possible. Application-layer filtering is fine when you control every reader. A single forgotten query is a leak.
How gridmap does it
gridmap has RLS on data registries. You attach permissions to specific column values or to user groups, and the Lookup API only ever returns the rows a caller is allowed to see. Permissions can be maintained inside gridmap or synced from a data warehouse table on a schedule, which is useful when your team membership already lives somewhere else. The same registry can serve a US analyst, an EU partner, and the board, without copies.
Common pitfalls
- Performance. A policy filtering on a non-indexed column will scan more than you want. Add the index you would add for any frequent
WHERE. - Audit gap. "Who saw what" is not the same as "who changed what". If you need a read log, add it explicitly.
- Bypass paths. Backups, ETL jobs, and analytical replicas often run as superuser and skip RLS. Walk the full data path before trusting the policy.
- Empty result confusion. A user with no permitted rows sees an empty list, which looks identical to "no data exists". Show a notice when the filter has emptied the view.
When RLS is the wrong tool
RLS solves the "same data, different visibility" problem. It does not solve:
- Different schemas per customer. That is multi-tenancy.
- Strict data residency. That is regional databases.
- Writes that should affect only one user's scope. RLS controls reads; writes need separate policy.
FAQ
Is RLS the same as multi-tenancy? No. Multi-tenancy isolates one tenant's data from another. RLS filters a shared dataset by user. You often use both, layered.
Can we add RLS to existing tables without downtime? In Postgres, yes. Attach a permissive policy first (a no-op), validate, then tighten. Roll through "default allow", "policy attached", "default deny" in stages.
How do we test RLS works? Connect as each role and verify the row count for known data. Automate it. Run it on every schema change.