Recommended Free Tools
Use PostgreSQL Row-Level Security (RLS) as a database backstop, not as your only authorization check: verify a user’s tenant membership in server-side Next.js code, set that verified tenant as transaction-local database context, and run every protected query through the same transaction. Table policies can then restrict which rows that database role may see or change. The pattern depends on a restricted database role and correctly scoped transactions; RLS alone does not establish that a user is entitled to a tenant.
How the request-to-database path should work
Keep the trust boundary explicit: client-supplied tenant identifiers are requests to access a tenant, not proof of permission to do so. A tenant ID may arrive in a route segment, query string, form, header, or Server Action argument. Resolve it against the authenticated user’s allowed memberships on the server before using it as database context.
- Authenticate. Read the identity from trusted server-side session data.
- Authorize the tenant. In a server-only Data Access Layer (DAL), verify that the authenticated user may act for the requested tenant. Next.js recommends server-side authorization and returning safe, minimal DTOs from the DAL; it also says Server Actions should be treated as public endpoints and authorized independently. See Next.js Data Security and Next.js Authentication.
- Start a database transaction. Set the verified tenant ID as a transaction-local setting before issuing tenant-protected queries.
- Run protected queries through that transaction. Do not switch back to a global database client mid-operation: the setting is scoped to the transaction and its connection.
- Let table policies enforce row access. PostgreSQL applies RLS in addition to ordinary SQL privileges, so the role needs grants as well as an applicable policy.
This layering addresses two different questions. The DAL decides whether this user may perform this operation for this tenant. RLS limits what the database role can do to rows under the tenant context it received. If the context is set from an unchecked client value, the policy faithfully enforces the wrong tenant.
Set the tenant ID for RLS inside the active transaction
PostgreSQL’s set_config(setting_name, new_value, true) applies the setting only for the current transaction; the final true is what makes it local rather than session-wide. See the PostgreSQL 16 documentation for set_config. That scope matters with pooled or reused connections: a session-level tenant value can survive beyond the request that set it, while a transaction-local value ends with its transaction.
#1 Best Overall
For example, a DAL operation can set a custom setting and issue its protected query through the same Drizzle transaction. The names below are design choices, not PostgreSQL or Drizzle standards; this example uses an application setting named app.tenant_id and UUID tenant IDs.
const result = await db.transaction(async (tx) => {
await tx.execute(sql`select set_config('app.tenant_id', ${verifiedTenantId}, true)`);
return tx
.select()
.from(documents)
.where(eq(documents.id, documentId));
});verifiedTenantId must be the value already checked against the authenticated user’s membership, not an unchecked action argument. Validate its format before passing it to the database. Every tenant-protected statement in the operation must use tx; using the root db client for one query can put that query outside the transaction carrying the context. The exact transaction and execution APIs can vary with the Drizzle driver and provider, so verify the syntax against the project’s installed version.
If the transaction fails, its local setting and work roll back together. Do not split context setup and protected work into separate transactions: the second transaction will not inherit the first transaction’s local setting.
Rank #2
Write policies for existing rows and proposed row values
RLS policies are table-specific rules, not table grants. PostgreSQL uses USING to decide which existing rows are visible or eligible for modification, and WITH CHECK to validate row values being inserted or produced by an update. The distinction prevents an update from moving a row into a different tenant even when the original row was allowed. PostgreSQL’s Row Security Policies documentation describes these policy semantics and command scope.
For a table with a UUID tenant_id column and the custom context above, a fail-closed expression can treat a missing setting as no matching tenant:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY documents_tenant_isolation
ON documents
FOR ALL
TO app_runtime
USING (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
)
WITH CHECK (
tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
);The NULLIF makes a missing or empty setting evaluate to null rather than a valid tenant ID; equality with null does not pass the policy. A malformed non-empty value still fails the UUID cast, which is another reason to validate the trusted server-side value. Adapt the column type and expression to the schema, and test missing-context behavior as well as normal requests.
FOR ALL is concise when the same tenant condition is intended for reads, inserts, updates, and deletes. A more nuanced design can use separate policies by command—for example, a stricter insert check or no delete policy for a role that should not delete. With separate policies, ensure every command has the intended rule. An update needs both an existing-row condition and a check on the resulting row; a read-only policy is not enough to secure writes.
Understand policy composition and default deny
When RLS is enabled and no applicable policy exists, PostgreSQL uses default deny: normal row selection and modification affect no rows. As the PostgreSQL documentation puts it, “If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.” This does not revoke SQL privileges; it is the row-level outcome after privileges are considered.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →- Permissive policies combine with OR. A row allowed by any applicable permissive policy can pass that group of checks.
- Restrictive policies combine with AND. A restrictive policy adds a condition that must also pass, alongside a permissive policy that grants the row-level access.
Do not assume that multiple policies automatically intersect. Adding a permissive policy for a secondary use case can broaden access if it independently allows rows outside the tenant boundary. Review the policies that apply to each role and command as a combined expression, not just one policy at a time.
Use a restricted application database role
RLS does not protect against every database identity. PostgreSQL says superusers and roles with BYPASSRLS always bypass row security, and table owners normally bypass it too unless FORCE ROW LEVEL SECURITY is enabled. Avoid using any of those identities for ordinary tenant requests. The PostgreSQL RLS documentation also notes that RLS does not govern whole-table operations such as TRUNCATE or REFERENCES, and that referential-integrity checks bypass row security, which can have covert-channel implications.
Grant the runtime role only the SQL privileges it needs. Policies do not grant SELECT, INSERT, UPDATE, or DELETE; the applicable grants and policies both have to permit an operation. Keep migration ownership separate from the restricted runtime role where your deployment model allows it. Consider FORCE ROW LEVEL SECURITY when owner behavior must be constrained, while remembering it does not make superusers or BYPASSRLS roles subject to policies.
Keep Next.js authorization at every entry point
RLS is a valuable guard against an accidentally omitted tenant predicate, but it cannot determine whether the signed-in person is a member of the tenant unless the policy context represents a membership that the server has already verified. Use a server-only DAL for database access and keep its return values limited to fields the caller needs. Next.js recommends this server-side authorization and minimal-DTO approach in its Data Security guide.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- Authorize independently inside each Server Action and Route Handler, rather than assuming a page check protects later requests.
- Validate client-controlled IDs, filters, and mutation values; RLS tenant filtering does not validate business rules or every input.
- Keep database credentials and server-only modules out of client components.
- For updates, ensure user-controlled fields cannot change
tenant_idunless an explicitly authorized operation is designed to do so.
Choose the context and migration approach deliberately
| Design choice | What it means | Trade-off to evaluate |
|---|---|---|
| Shared application role plus tenant context | Requests use a common restricted database role; each transaction sets the verified tenant context used by policies. | Centralizes role management, but correctness depends on setting context for every protected transaction and using that same transaction for queries. |
| Database role per tenant | Database identity itself distinguishes tenants rather than relying on one shared role plus a context value. | Can make identity boundaries explicit, but increases role and connection-management complexity. The documentation does not establish a universal winner between these architectures. |
| Transaction-local context | The tenant setting ends when the transaction ends. | Fits pooled connections more safely than a session-level setting, provided every protected query stays inside the transaction. |
| Session-level setting | The value belongs to the database session rather than only the active transaction. | Requires reliable reset and connection lifecycle handling to avoid context leaking into later work on a reused connection. |
| Permissive policy | Combines with other applicable permissive policies using OR. | Convenient for additive access cases, but another permissive policy may widen access beyond the tenant condition. |
| Restrictive policy | Combines with other applicable restrictive policies using AND, while access still needs a permissive policy. | Useful for a mandatory additional constraint; review the full set of policies rather than treating it as a grant. |
| Drizzle-managed policy migration | Drizzle’s RLS API supports policy command, role, permissive/restrictive mode, USING, and WITH CHECK options. |
Keeps policy definitions near schema code where appropriate; confirm support and behavior for the chosen provider and migration/runtime setup. |
| Hand-authored SQL migration | Policies and RLS settings are expressed directly in SQL migrations. | Makes the database statements explicit and portable across application schema code, but requires disciplined coordination with Drizzle’s schema and migration history. |
Drizzle documents its RLS API in the contexts of supported providers including Neon and Supabase. That is not a guarantee that every policy feature or migration workflow behaves identically across providers; check the Drizzle RLS documentation against the project’s actual runtime and migration setup. ORM-managed definitions and SQL migrations are alternatives for expressing policy, not substitutes for understanding the policy PostgreSQL will enforce.
Test the boundary, not only the happy path
Exercise the database with the same restricted role used by the application. Tests performed only as a table owner or superuser can miss the very policy behavior tenant requests depend on.
- With tenant A in context, confirm its rows are visible and tenant B’s rows are not.
- With no tenant context, confirm protected reads and writes do not succeed.
- Try an insert whose
tenant_iddiffers from the context and an update that attempts to change a row’s tenant. - Check each command the role is allowed to use—select, insert, update, and delete—against the intended policies and SQL grants.
- Exercise failed transactions and reused connections to confirm transaction-local context does not bleed into subsequent work.
- Inspect effective roles and ownership so routine requests cannot silently run as an owner, superuser, or
BYPASSRLSrole.
These checks do not make RLS a complete authorization system. They verify that the database boundary, the transaction lifecycle, the role configuration, and the application’s membership check agree.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
