PostgreSQL row-level security implementation

How PostgreSQL row-level security isolates tenants in shared tables

PostgreSQL row-level security (RLS) filters which rows a query can read or change, based on the role running the query and the policies attached to the table. Teams reach for it when many tenants share one set of tables in one database and the application must never return another tenant's rows, but the cost of running a separate schema or database per tenant is too high for the number of tenants, the operational load of managing them, or the migration effort involved. Separate schemas or databases give stronger physical separation, while RLS keeps one schema and enforces the boundary inside the table itself.

RLS is part of PostgreSQL itself and is released under the PostgreSQL License, a permissive license similar to the BSD and MIT licenses, so there is no separate product to license or version alongside the database. Because the database evaluates the policies on every query, the application cannot skip the check by forgetting a WHERE clause in one code path. That protection is only as strong as the setup around it, so the enforcement sits in the database while the decisions about which role connects, which policy applies and which session variables are trusted sit in your design.

The feature switch alone does not make isolation hold. Enabling row-level security on a table, creating a policy and turning on the flag is not the same as a correct design. Whether isolation holds depends on which roles the application connects as, whether the table owner is exempt from policies, whether roles carry the BYPASSRLS attribute, and whether the tenant identifier used in each policy comes from a value the client cannot set. A policy that compares a column to a setting the application fills from user input provides no isolation at all.

Before go-live, confirm three things with your own environment rather than assuming them: the role the application uses in production, the owner of each protected table, and the list of roles with BYPASSRLS. Review how each policy obtains the tenant identifier, and confirm your PostgreSQL version against the supported releases listed in the versioning policy, since the security fixes you depend on arrive in minor releases. These checks belong in the design review, before any data is loaded.

Which tenant isolation pattern fits your application

This table compares shared tables with a tenant column and row-level policies, schema-per-tenant, and database-per-tenant across isolation strength, operational load, cross-tenant reporting and the effort to add tenants. Read each row against your own tenant count, compliance boundary and team capacity, then use the final row to see which pattern usually fits best.

Factor	Shared tables with RLS	Schema per tenant	Database per tenant
Isolation strength	Enforced by policies on each table; a missing policy, a table owner or a BYPASSRLS role can expose rows	Separate namespaces; grants and search_path mistakes can still cross tenants but the default is narrower	Strongest logical boundary; a tenant's data sits in its own database with its own roles
Schema changes and migrations	One migration run updates every tenant at once; policy changes must be tested with the shared schema	Migrations must run once per schema, so tooling and failure handling grow with each tenant	Migrations run per database; scheduling and rollback become a fleet operation
Backups and restores	One backup covers all tenants; restoring a single tenant needs filtering or a logical export	Schema-level restore is possible with extra tooling; base backups still cover the whole cluster	Per-tenant backup and point-in-time recovery are possible, but each database adds jobs to monitor
Connection counts	Pooling in front of one database (for example PgBouncer) usually keeps connection counts manageable	Each schema shares the cluster's connection limits; pooler configuration needs care with session-level settings	Each database may need its own pool; total connections across the cluster need planning
Cross-tenant reporting	Straightforward queries across tenants, but reporting roles must be designed so policies still apply as intended	Possible through views or unions over schemas, which grow more complex as schemas increase	Requires a separate data pipeline or federated queries; often handled by exporting to an analytics store
Effort to add a tenant	Insert tenant rows and set the tenant identifier; no new database objects are created	Create a schema and run every migration into it; automation is needed to keep schemas aligned	Provision a database, roles, backups and monitoring; usually automated with infrastructure tooling
Best fit	Many tenants of modest size, a shared schema, central migrations and a single operations team	Moderate tenant counts where separation matters more than a single shared schema	Few large tenants, contractual or regulatory separation, or tenants that need different data locations or restore timing
When a stricter boundary is worth the cost	When a tenant's regulator or contract requires physical or dedicated-database separation, or a single policy error would be unacceptable	When tenants need separate schema customization but can share a database cluster	When dedicated resource isolation, separate retention or independent upgrade timing is required by the customer

Why table owners and BYPASSRLS roles skip your policies

The exemptions most likely to leak tenant data are not policy mistakes. They are roles that never evaluate the policy at all. By default, the table owner bypasses row-level security on the tables it owns. Roles with the BYPASSRLS attribute skip policies, and superusers skip them too. An application that connects as the owner, or as a role created with BYPASSRLS for convenience, can return every tenant's rows while tests run under a different role pass. Before go-live, list for each database which roles own the tenant tables, which roles carry BYPASSRLS, and which roles are superusers. Treat every application login that appears in that list as a finding to resolve.

FORCE ROW LEVEL SECURITY closes the owner gap. When it is set on a table, the policies also apply to the table owner, so the owner no longer bypasses them by default. It does not change the attributes that matter most here: superusers and roles with BYPASSRLS still skip policies when FORCE is set. Confirm the exact behavior for your PostgreSQL version in the official documentation before relying on it, including how the owner exemption interacts with views, functions and security definer routines in your schema. Because the supported range includes PostgreSQL 14 through 18, test the behavior on the version you actually run rather than assuming it carries across releases.

The practical pattern is to split duties across two roles. The application role is a least-privilege login that is not the owner, has no BYPASSRLS attribute and is not a superuser. It receives only the data manipulation privileges the application needs on tenant tables, and the application sets the tenant context that the policies read. The migration role owns the schema objects and runs DDL. Only the deployment pipeline uses it, never the application runtime. If the owner is the migration role and FORCE is set, policies also hold during migrations. If a backfill truly needs to bypass policies, run it as a time-limited, audited step with a documented approval, not as a standing BYPASSRLS login.

Verify the design with checks you can rerun. Query the catalog for each tenant table to confirm whether row-level security is enabled and whether it is forced, and query the role catalog for superuser and BYPASSRLS flags on every login the application uses. Then write a test that connects as the application role, sets two different tenant contexts and confirms that each session sees only its own rows and cannot write across tenants. Ask your implementation partner or internal team to keep role creation and grants in version-controlled migration scripts, so that any new login or attribute change is reviewed like other schema changes.

Set up tenant policies step by step

This sequence moves a PostgreSQL schema from shared tables to enforced tenant isolation, with a check after each step. It produces a tenant key, separated database roles, enabled and forced policies, and tests that show one tenant cannot read or write another tenant's rows.

Decide the tenant key and session context
Choose the column that identifies the tenant on every scoped table, usually a tenant_id of one fixed type, and choose the session setting that carries the current tenant, such as app.tenant_id. Trusted application code or middleware sets that value after it authenticates the user and resolves the tenant on the server side. Client input such as a request parameter, header or form field must never supply the tenant value, because a client can change it. Record the decision in a short design note that names each scoped table and its owning team. Done when every scoped table has a named tenant column and the team agrees on the setting name and the code path that writes it.
Create separate application and migration roles
Create a login role for the application runtime and a separate role for schema migrations, and create neither as a superuser or with BYPASSRLS. Grant the application role only the table privileges it needs, such as SELECT, INSERT, UPDATE and DELETE on scoped tables. The migration role makes schema changes during deployments, so schema work never runs under the identity that serves customer traffic. Check the result by querying pg_roles for rolsuper and rolbypassrls on both roles. Then connect with the application credentials, run select current_user, session_user, and confirm the identity your connection pool actually uses.
Enable row-level security on scoped tables
Run ALTER TABLE with ENABLE ROW LEVEL SECURITY on each table that holds tenant data. Once enabled, a role that is subject to policies and has no matching policy sees no rows, which gives the default-deny behavior the design depends on. Include any table reached through views and any table that later migrations add, and review each shared reference table to confirm it holds no tenant data before you exclude it. Verify by querying pg_class for relrowsecurity on every table in your inventory and comparing the output with the design note. Done when the list matches exactly and no scoped table is missing.
Force policies for table owners
Table owners skip row-level security unless FORCE ROW LEVEL SECURITY is set on the table, so run ALTER TABLE with FORCE ROW LEVEL SECURITY on each scoped table where the owner connects. This matters most when the application role owns its tables, which can happen in small deployments and leaks data without raising any error. If the migration role owns the tables, decide whether migrations that read tenant data need a documented exemption path, and keep that exemption narrow. Check by querying pg_class for relforcerowsecurity and confirming that the forced tables match your list of owners.
Write USING and WITH CHECK policies per command
Create policies for each command the application uses, and scope each one to the application role. Use USING to filter rows visible to SELECT, UPDATE and DELETE. Use WITH CHECK to validate rows written by INSERT and UPDATE, so a tenant cannot write a row that belongs to another tenant. A typical comparison reads the tenant column against current_setting('app.tenant_id', true) cast to the column type, and the missing_ok argument keeps an unset value from raising an error. Confirm each policy by reading the qual and with_check columns in pg_policies, and check that no policy applies to PUBLIC by accident.
Restrict BYPASSRLS and table ownership
Remove BYPASSRLS from every role that serves tenant traffic, and confirm that no application role is a superuser, because both attributes skip policies entirely. Move ownership of scoped tables from the application role to the migration role if the application role currently owns them. Revoke broad grants that let the application role act outside its tables and schemas. Confirm the result by querying pg_roles for rolbypassrls and rolsuper, pg_tables for tableowner, and information_schema.role_table_grants for privileges you did not intend to grant. Record any remaining exemption with its reason and the person accountable for it.
Test policies as a second tenant
Create two tenants with known rows. Connect with the application role, set the session tenant to the first tenant through the same middleware path production uses, and confirm the rows returned belong only to that tenant. Repeat for the second tenant, then attempt INSERT, UPDATE and DELETE against the other tenant's rows and confirm each one fails or affects zero rows. Run one query with the session value unset to confirm default-deny returns nothing. Keep these checks in an automated test suite that runs in your pipeline. Done when every cross-tenant attempt is denied and the results are attached to the release review.

Testing policies before go-live

Row-level security only protects tenants if the policies behave as designed under every role the application uses. The checks below turn isolation into a test suite that runs before each release, on each schema change and after each PostgreSQL upgrade.

Cross-tenant read and write tests

Authenticate as tenant A and attempt to select, update and delete rows that belong to tenant B, including by primary key guessed from another tenant's data. Expect zero rows returned and zero rows affected, not an error that reveals the row exists. Repeat this for every tenant-scoped table, since a single table with a missing policy is enough to leak data.

Write checks with WITH CHECK

Test INSERT and UPDATE statements that try to set the tenant column to another tenant's identifier, or that change an existing row's tenant to a different value. USING controls which rows are visible, but the WITH CHECK clause controls what new row values are allowed, so each write policy needs its own test. Confirm that the database rejects these writes rather than silently altering them.

Missing and empty tenant context

Run queries with the tenant setting unset, set to an empty string, and set to a malformed value, then confirm that default-deny behavior returns no rows and blocks writes. Check how your application's connection pooler resets session settings between requests, because a stale tenant value left on a pooled connection is a common cross-tenant failure. Ask your team to confirm the exact setting name and the fallback value used in production.

Owner and BYPASSRLS role queries

Run the same suite as the table owner and as any role with BYPASSRLS, and record which statements succeed. Owners skip policies unless FORCE ROW LEVEL SECURITY is set, and BYPASSRLS roles skip them always. The expected result is that the application's runtime role is subject to policies and that privileged roles are listed, documented and limited to administrative tasks.

Migration and background job roles

Schema migrations, backfills, reporting jobs and cleanup workers often connect with their own roles and sometimes need cross-tenant access. Test each of these roles separately, confirm which tenants it can see, and check that a job meant for one tenant cannot touch others. Any cross-tenant access should be explicit, narrow and covered by its own test case.

Testing with production-shaped objects

Run the verification set against the same tables, roles, grants and policies the application uses, not against a copy built with broader privileges, which can hide the failures you are looking for. Load representative data for at least two tenants so tests can detect leaks. Keep the test database's role setup generated from the same source as production to avoid drift.

Version control and rerun triggers

Store the policy definitions and the test cases together in Git, and run the suite in CI on every change to a policy, table, grant or role. Rerun the full set after each PostgreSQL minor release and before each major upgrade, since behavior in both areas can change between versions. Ask your team to confirm which upgrade checks the release process must include before any production change.

How connection pooling changes tenant context

A connection pooler that reuses server connections can carry one tenant's context into another tenant's request, so the tenant identifier must be scoped to each transaction and never assumed to be fresh. Many applications set a session variable such as app.tenant_id at the start of a request and let row-level security policies read it with current_setting(). That works only while the connection serves one request at a time. When the connection goes back into the pool, the variable can still hold the previous tenant's value. The next request may then borrow that connection, and if its code path skips the setting, the policies will treat the stale value as a valid tenant. Default-deny protects against a missing setting, but it does nothing against a setting that is present and wrong.

The pool mode decides how long a server connection stays bound to one client, and that determines what can persist. In session pooling, a client keeps the same server connection for its whole session, so a SET issued early in the session stays in effect for the rest of that session. Leakage is narrower because the connection is not handed to someone else mid-session, but the pool gains less from sharing. In transaction pooling, the server connection is assigned only for the duration of a transaction and then returned. This allows many more clients to share a small number of connections, but any setting that persists beyond the transaction will reach the next client. Session variables, temporary tables and prepared statements all deserve a check, because their behavior depends on the pool mode and on how the pooler resets connections between uses.

PgBouncer is a lightweight connection pooler for PostgreSQL that many teams place between applications and the database. Version 1.26.1 was released on October 14, 2026 with security fixes, so confirm whether your deployment runs that version or a later one before you finalize the design. Then confirm the pool mode for each database and user, because the setting can differ across pools, and confirm the reset behavior that runs when a server connection is returned. Check the reset query and any configuration that controls how session state is cleared, and read the documentation for the version you actually run. Record the answers in the design, since a configuration change or a pooler upgrade can alter what persists on a connection without any change to the application.

Set the tenant context inside each transaction, using SET LOCAL or set_config with its local flag enabled, so the value ends when the transaction ends. Make the application's data access layer responsible for this on every request, and add a guard that fails the request when the context is absent, instead of relying on developers to remember it. Then test reuse across tenants on purpose. Use a small pool size so that connections are forced to be shared, send alternating requests for tenant A and tenant B, and include a request that opens a transaction without setting any tenant. Illustrative expectation: the request without a tenant returns no rows. Repeat the test after every pooler upgrade, configuration change or change to the pool mode, and under concurrent load, since leaks often appear only when connections are contended.

Security considerations for tenant isolation

Row-level security is only as strong as the roles, sessions and administrative rights around it. The cards below cover the controls that stop a policy from being bypassed through the connecting account, the session setup or a schema change. Each one names a decision to make and a check to run before go-live.

Least-privilege application roles

Give the application a login role that holds only the table privileges it needs, such as SELECT, INSERT, UPDATE and DELETE on tenant tables, and no ownership of those tables. Use a separate role for migrations so the runtime role cannot alter schema or policies. Check with the psql privilege listing and the information schema that the runtime role has no CREATE right on the schema and no grants on administrative functions.

Protecting the tenant context path

Most multi-tenant designs read the tenant identifier from a session setting that the application sets when a connection or transaction starts. Do not build that value from request headers, form fields or URL parameters without checking it against the identity the application has already authenticated. Confirm that the setting is applied with a parameterized call, that the connection pool resets it between requests, and that a test request cannot set another tenant's identifier.

Revoking BYPASSRLS and superuser access

Roles with the BYPASSRLS attribute or superuser status skip row-level policies entirely. Table owners are also exempt unless FORCE ROW LEVEL SECURITY is set on the table. Remove both attributes from every application and reporting account that serves tenant users, then query the role catalog across all login roles to confirm none still hold them and that the application does not connect as the table owner.

Change control for policies and RLS

Creating, altering or dropping policies, enabling or disabling row-level security, and changing table ownership are DDL changes that can silently widen access. Restrict these rights to a deployment role, deliver them through versioned migrations, and require review and a change ticket before production. Compare live policy definitions with the reviewed source on a schedule so out-of-band changes become visible.

Authentication methods for applications and operators

PostgreSQL supports several client authentication methods, including password authentication with SCRAM, certificates, LDAP, GSSAPI and OAuth 2.0, which PostgreSQL 18 added. Choose a method per role and per source address in the host-based access configuration. MD5 password authentication is deprecated in PostgreSQL 18 in favor of SCRAM, so find older client configurations and move them before any upgrade.

Audit logging designed separately

Row-level security restricts access but does not record who read or changed which rows, so audit logging needs its own design. Decide which statements, role changes and policy changes must be logged, where the logs are stored, who may read them and how long they are kept. Confirm which logging options are available in your environment, then test that denied queries and administrative changes produce entries.

Questions buyers ask before hiring help with PostgreSQL row-level security

What drives the cost of a PostgreSQL row-level security build?

The main drivers are the number of tables that hold tenant data, the complexity of each policy, the role model, how much existing application code sets tenant context, and how much test coverage already exists. Each tenant-scoped table needs policies for the commands the application uses, so a schema with dozens of such tables takes more design and review than one with a handful. Policies that depend on joins, tenant hierarchies or shared reference data add work because every access path must be proven safe. Application code that sets the tenant identifier per request may need changes before the database can rely on it. If little test coverage exists, more effort goes into building tests that prove isolation before go-live. Ask any provider to break its quote down by these drivers and to name who performs each part of the work.

Which row-level security costs are one-time and which recur?

Design of the tenant model, policy writing and the first test suite are one-time work. Design settles which tables are tenant-scoped, how the tenant identifier is set, and which roles are exempt from policies. Policies and tests follow from that design and are built once for the existing schema. Recurring work begins after go-live. Every schema change that adds or alters a tenant-scoped table should trigger a policy review, and the test suite should run in your delivery pipeline on each change. Upgrade testing also recurs, because minor releases are applied over time and major versions are supported for a fixed period, so policies and tests should be rerun against the target version before rollout. Ask whether the quote includes a handover of the test suite and a review checklist, and whether ongoing review is a separate retainer.

What does a row-level security project need from our team?

The work needs four things from your side. Application owners must describe how requests reach the database, which tenant identifier is available, and which code paths run under which database role. A database administrator must review role grants, table ownership, and any BYPASSRLS or superuser access, and must be available for changes to the cluster. You should provide access to a staging copy of the production schema, ideally with representative data volumes, so tests run against the same structure without exposing production records. Someone with authority must also sign off on the tenant model, including what a tenant is, how shared rows are handled, and who may see cross-tenant data for support. Name these owners and their availability in the written scope.

Can our internal team implement row-level security without outside help?

Yes. An internal team with PostgreSQL experience can implement row-level security on its own, provided it reviews exemptions and tests carefully. The usual failure points are the exemptions. Table owners can read rows that policies would otherwise hide unless the table forces row-level security, and roles with BYPASSRLS or superuser rights bypass policies entirely. Your team should inventory every owner and bypassing role, decide which ones truly need an exemption, and test each application path with a non-privileged role. Tests should cover cross-tenant reads, inserts and updates, and what happens when tenant context is missing. Schedule an independent review by someone who did not write the policies. DeployOpen can supply that review or the full build, and the right choice depends on your team's capacity and on how many tables are involved.

How long does a row-level security build take?

Timing depends on the same drivers as cost: table count, policy complexity, the role model, how much application code must change to set tenant context, and how much test coverage must be built. A schema with a small set of tenant tables, one role model and existing context-setting code can move through design, build and testing faster than a large schema with hierarchical tenants and many services. Access also shapes the calendar. Staging copies, change windows and sign-off meetings often take as long as the policy work itself. DeployOpen does not give a fixed figure before the scope is known. Ask for a timeline in the written scope that lists milestones, client dependencies and the testing gates that must pass before go-live.

How do we get a quote for PostgreSQL row-level security work?

Request a written scope first, because DeployOpen does not publish a price list. Most engagements are fixed-scope, so the scope, deliverables and timeline are agreed in writing before work starts, and the quote follows from that scope. A useful scope names the tenant-scoped tables, the roles and exemptions to review, the application code that sets tenant context, the test suite to deliver, the staging access required, and any recurring services such as policy review or upgrade testing. Ask the provider to separate one-time work from retainers, to list exclusions, and to state what you must provide. Compare quotes on the same written scope rather than on headline totals, and check that each item in the scope maps to a deliverable.

Why do tenant isolation policies leak data even when they look correct?

Policies often look correct in isolation, and the leaks come from what surrounds them. Table owners bypass row-level security unless the table forces it, so an application that connects as the owner can read every tenant's rows. Roles with BYPASSRLS or superuser rights skip policies entirely, and a background job running under such a role can expose data without any error. Tables with row-level security enabled but no matching policy return no rows to ordinary users, which is safe but can hide a broken setup until go-live. Views and functions can run with the rights of their owner, and missing tenant context can produce empty results or wrong results depending on how the policy is written. Connection poolers that reuse sessions can also carry tenant settings between requests, so test them directly.

Limits and what to confirm in your environment

Row-level security is enforced by PostgreSQL, but how it behaves depends on your exact version, your query shapes, your data volume and the way your application connects. The cards below list the checks that connect a passing test suite to a safe production deployment.

Exact behavior for your version

Confirm row-level security behavior against the release you run. Supported major versions at the time of writing are 14 through 18, with end-of-support dates of November 12, 2026 for 14, November 11, 2027 for 15, November 9, 2028 for 16, November 8, 2029 for 17 and November 14, 2030 for 18, per the PostgreSQL versioning policy. Plan your tests and your upgrade path together, because policy behavior should be re-verified after a major upgrade.

Policy predicate performance at scale

A policy predicate runs on every row a query touches, so a tenant filter that is cheap on a test table can dominate plans on large tables. Check query plans on production-sized data, confirm that the tenant column is indexed in the form the predicate uses, and verify that the planner still chooses sensible access paths for the queries your application issues most often.

Views, foreign keys and triggers

Confirm how policies apply through views, since the owner and invoker rights of a view decide whether policies on underlying tables are checked. Also test foreign key checks and triggers that read or write across tenants, because they can see rows that the calling role cannot select. Write a test for each case your schema contains.

Tenant context through pooling and the ORM

Tenant identity is usually passed as a session setting, so it must be set and reset correctly on every checkout from a connection pooler such as PgBouncer. Confirm the pooling mode you run, whether transaction-level settings survive your ORM's connection handling, and that a request never inherits a previous tenant's context after a pooled connection is reused.

Backups, recovery and replication

Confirm how your base backups, point-in-time recovery and logical replication treat policies, roles and ownership. Restores into a different role layout can silently change who is exempt from policies. Test a restore into a separate environment and check that policies, grants and the role attributes that bypass them are all present afterward.

Authorization beyond the database

Policies limit which rows a role can read or change, but they do not decide whether a user may perform an action, which features a tenant has bought or how administrators are audited. Keep application authorization in place, and identify the cases that need separate design, such as cross-tenant reporting, support access and bulk jobs that must run as privileged roles.

How DeployOpen scopes and delivers tenant isolation

DeployOpen plans and builds PostgreSQL row-level security for multi-tenant applications inside infrastructure you control. We start from the tenancy model and the roles your application uses, then write policies, test them against those roles, and document how the database enforces isolation so your team can operate it after handover.

How we scope

Written scope lists the tables, tenant key, and application roles that policies must cover before work starts.
We map which roles own tables, which hold BYPASSRLS, and which connect through pooled sessions, since these often bypass policies.
Engagements are fixed-scope, with deliverables and timelines agreed in writing before any policy changes begin.

How we build

We implement default-deny policies, enable FORCE ROW LEVEL SECURITY where table owners would otherwise bypass it, and remove unneeded exemptions.
Policy tests run in a non-production cluster with at least two tenants, checking that cross-tenant reads and writes fail.
Migration scripts that add or change policies are versioned alongside the application schema for repeatable deployment.

How we hand over

Handover covers a policy inventory, role and grant map, and the test suite with instructions to run it on each release.
We document how new tables must be added with policies, and which roles application code is allowed to use.
Your team receives a walkthrough of the policy design, the exemptions that remain, and the reasons each one exists.

How we support

Ongoing support covers policy reviews when schema changes add tables or tenant-scoped columns, available as a retainer.
Regular checks confirm that role grants and BYPASSRLS attributes have not drifted from the documented design.
Support also covers PostgreSQL minor and major upgrades, so isolation tests are rerun against each supported version you run.