Row Level Security: 7 Checks for Safer PostgreSQL Tenant Access

Row level security controls which database rows a user or application role may read or change. It adds a data access rule inside the database, close to the records themselves. In a shared business application, that can help stop one customer from seeing another customer’s data when a query misses a filter.

This guide focuses on row level security in PostgreSQL. It explains grants, policies, trusted user context, writes, privileged roles, and tests. A worked invoice example shows where this control helps. It also shows which checks the app still needs.

1. Define the access rule before writing a policy

Start with the business relationship between a user and a record. A staff member may belong to one tenant, several branches, or a project team. Those are different models. A simple tenant match will not express every case safely.

Write a small access matrix in plain language. For each user type, state whether they may read, create, edit, or delete each kind of record. Include suspended users, former members, and support staff. Then review that matrix with the people who own the data.

Row level security is not the same as a role check. A role might let someone view invoices, while a row policy limits which invoices they can view. Our guide to role based access control explains the broader role model. Use both where the application needs both kinds of rule.

Also list records that cross boundaries. Shared product catalogs, group accounts, and transfers between branches need deliberate treatment. A policy may assume each row belongs to one tenant. If a new feature shares rows, review the model first. Avoid adding a broad exception just to make it work.

2. Understand how grants and policies work together

A grant gives a database role permission to perform an operation on a table. A row policy limits the rows that operation may affect. These controls work together. A policy alone does not grant table access. Likewise, a table grant does not express a tenant rule.

The PostgreSQL row security documentation explains that enabling row security without an applicable policy gives normal access a default deny result. It also describes important bypass cases. Owners normally bypass policies, while superusers and roles with BYPASSRLS do so as well.

Therefore, do not test only through a table owner account. The live app should use a role with the least access it needs. Keep migrations and routine queries under separate roles. Where appropriate, FORCE ROW LEVEL SECURITY can subject a table owner to policies, but it does not remove superuser or BYPASSRLS privileges.

Audit grants alongside policies. Whole table operations such as TRUNCATE do not follow ordinary row filtering. A role that can wipe the whole table has more power than a row policy suggests. Remove powers the application does not need.

3. Carry tenant context through a trusted path

A policy needs a trustworthy way to identify the current user or tenant. That might come from a verified session and a server side lookup. The database must not simply trust a tenant value that a browser sends. A user could change that value.

In a custom application, a server may set tenant context for a database transaction. In that case, define who may set it and how the server validates membership. But what if a client can run its own SQL under that role? It could change the context. In that case, the setting alone does not prove who may access the tenant.

Connection pools make scope especially important. A connection can serve many requests over time. Tenant context from one request must never carry into the next. Use the database and pool’s supported transaction handling, and test missing context as a deny case.

Our guide to connection pooling covers why request scope and connection lifetime differ. Review background jobs too. A worker that processes several tenants needs an explicit, tested switch for each unit of work.

Check the identity source, not just the policy text

If access relies on token claims, know how those claims change. A removed team member may still hold a token with old membership data. Choose an expiry or server check that fits the risk. A correct policy over stale facts can still grant unwanted access.

Keep service credentials off the browser and out of public code. Supabase’s row level security guide explains its role and policy model, including privileged service access. Follow your platform’s rules for sign-in and trusted claims. PostgreSQL hosts do not all use the same setup.

4. Protect writes as carefully as reads

A read rule limits visible rows. A write rule must stop users from adding rows to a space they do not own. It must also stop them from moving rows there. In PostgreSQL policies, USING concerns access to existing rows, while WITH CHECK constrains new row values for relevant writes.

For example, a user may read invoices for tenant A. If an edit can change the tenant reference to B, the policy needs to reject that new value. Otherwise, a valid edit route could become a way to move records across tenant boundaries.

Set deliberate rules for each operation. Read, insert, update, and delete may have different needs. Remember that an update can also need visibility of the existing row. Test the actual queries your data library generates, not only a hand written example.

Multiple policies can combine in ways that widen access. Permissive policies normally combine with OR, while restrictive policies add AND constraints. Review the full set for the table. A narrow new policy does not cancel an older broad allowance.

5. Test joins, indirect paths, and privileged tools

A tenant invoice table may be secure while its line items remain exposed. Review every table that holds related data, including attachments, comments, and lookup records. A join does not automatically give an unprotected table the same policy as its parent.

Views, functions, and administrative tools also need review. Their execution context can affect whether policies apply. Avoid assuming a function is safe merely because its caller has limited rights. Check who owns the code and which rights it uses. Include any security definer setting in that review. Use the database version you plan to run.

Row level security does not hide every possible signal. Constraint errors or timing may reveal clues in some designs. Nor does it govern copies sent to logs, exports, or external search systems. Keep the threat model wider than one table.

Build these checks into application security testing. Test the routes staff actually use, including downloads and support views. A safe database query cannot repair an export that later goes to the wrong account.

6. Walk through a two-tenant invoice test

Consider an illustrative SaaS billing portal with tenants A and B. Each has users, invoices, and invoice lines. The team defines that normal users may read invoices only within active memberships. Only billing managers may edit drafts, and nobody may change an invoice’s tenant through the normal edit route.

First, the team creates test users with distinct memberships. It uses the real application role, not the owner role. A user from A can read A’s invoice but gets no B invoice through search, direct reference, or a joined line query.

Next, it tests writes. The user cannot create a B invoice or change an A invoice to B. A staff member with read only rights cannot edit a draft even within A. The application enforces that action rule, with database controls reflecting the intended design.

Then the team removes membership while a session remains active. It checks when access ends under the chosen identity model. It also runs two requests through the same pool connection in sequence. Missing or failed context setup must never reveal the prior tenant’s records.

Finally, the team tests support and batch jobs separately. Those paths may need broader access, but they get narrow credentials, clear approval rules, and logs. A privileged job should not quietly become the default route for normal users.

7. Roll out policies with a recovery plan

Before enabling row level security on a live table, inventory its callers. Include reports, scheduled jobs, migrations, and data support tools. A new default deny behavior may break a legitimate path that nobody listed. Test on a realistic staging copy with safe data first.

Check performance with realistic row counts and tenant sizes. A policy that performs a lookup for many rows may change the query cost. Use indexes and suitable policy expressions, then inspect the actual query plans. Do not trade a verified access rule for a faster but unsafe shortcut.

Plan how staff will detect an incorrect denial. An empty list may look like no data rather than an access fault. Use safe support signals to tell these cases apart. Keep private rows out of the user’s error message.

Review the policy as the product changes

New sharing features often challenge the original model. A group account, guest user, or cross-tenant report may need a new relationship. Update the access matrix and tests before adding an exception. Keep every broad permission tied to a clear business purpose.

Also review backup, restore, and migration paths. They may use roles that bypass row policies, which can be appropriate for their jobs. Protect those paths through separate access controls and operational checks. Row level security is one layer in that wider system.

A successful rollout makes normal data access safer and privileged access more visible. It should also leave the team able to explain why a user can see a row. If the answer requires guessing across several policies and hidden settings, simplify the model before expanding it.

Give support staff a safe way to explain an empty result

A user may report that an invoice has vanished. First, support should check the user’s account and the requested tenant. Then it can check a safe audit record for the action. It should not ask the user to try another tenant value until a row appears.

Keep a test record in each safe test tenant. Use it to check the normal read path after a release. A test with no rows cannot show whether the rule hid the right data. Both an allowed row and a denied row are useful evidence.

Next, check what happens when a user leaves a team. Close the session or refresh the trusted facts as the design requires. Then try the old direct link and a saved export request. The user should not keep access through a less common route.

Write a short guide for staff who need broader access. It should name the purpose, the approval, and the time limit. Keep that route distinct from a normal session. A support fix must not leave a lasting grant behind.

Finally, review new tables before they reach production. Ask who owns each row and which role will query it. Add the read and write tests at the same time. This small gate can stop a later feature from opening a gap beside well protected tables.