PostgreSQL Row-Level Security Interview Questions: Policies, Roles, and Tenant Isolation

A developer enables PostgreSQL row-level security, runs a query, and still sees every tenant's records. The policy may not be broken: the test connection may own the table. Another team gets the expected filtered result, but lets callers choose the session's tenant identifier. Their test passes while the security assumption fails.

PostgreSQL row-level security interview questions are therefore about more than writing a predicate. Explain who the database trusts, which command is being checked, and which execution paths can bypass or change that check. Begin with Design an access control system (RBAC + resource-based), then add the database-specific tests below.

This guide uses PostgreSQL's current documentation, which resolves to version 18 at review time. The SQL is an original isolated-lab exercise with expected outcomes derived from those contracts; it is not a transcript from a production database or a claim of a local PostgreSQL execution.

What does RLS add to ordinary privileges?

Ordinary grants authorize operations on database objects. RLS adds row-level conditions to supported access. Enabling RLS without an applicable policy produces default denial for roles subject to RLS. Creating a policy alone does not enable the feature. The official row-security overview describes these boundaries.

Keep the two layers separate in an answer. A role with no SELECT privilege does not gain access because a policy says true. Conversely, granting SELECT does not make every row visible once RLS applies. The GRANT reference remains relevant; policies do not replace least-privilege object permissions.

Also identify the excluded operations. Whole-table operations such as TRUNCATE are not filtered by row policies. Do not give an application a dangerous table-wide privilege and assume a tenant predicate will constrain it. Referential-integrity checks can reveal information through conflicts even when the conflicting row is not directly visible.

In a design discussion, state the target protection: for example, prevent an otherwise authorized application query from reading or modifying another tenant's ordinary rows. That is narrower and more testable than “the database is secure.” Authentication, connection credentials, privileged maintenance, and data exports still require their own controls.

Which roles bypass row-level security?

Superusers and roles with BYPASSRLS bypass row security. Table owners normally bypass it too; FORCE ROW LEVEL SECURITY can subject the owner to policies, but does not remove superuser or BYPASSRLS privileges. Review role attributes alongside the table configuration.

An interview diagnosis should start by printing the effective role, checking ownership, and checking role memberships—not by repeatedly changing the predicate. A test run as a migration owner tells you little about what the application role sees. A test that switches into another role must verify that the effective role actually changed.

Separate a narrowly privileged runtime role from the role that owns schema objects. Review whether runtime credentials can assume a privileged role or call a privileged helper. The absence of BYPASSRLS on one named role is insufficient if another allowed execution path reaches unrestricted data.

Explain USING and WITH CHECK with one update

USING determines which existing rows are eligible for applicable reads or modifications. WITH CHECK validates proposed new rows for inserts and updates. The CREATE POLICY reference specifies command behavior and the cases where an omitted check derives from USING.

For an update, reason about both sides: can this role target the old row, and is the new row acceptable? If tenant A can update its own invoice but the check does not prevent changing tenant_id, it may be able to move that invoice into tenant B's domain. Protecting visibility alone is not the whole write contract.

Distinguish two results in your test plan. An attempt to update an invisible tenant B row may affect zero rows. An attempt to take a visible tenant A row and give it a forbidden tenant value should fail the check. Treating both as “access denied” hides an important observable difference for API behavior and diagnostics.

Build a two-tenant fixture with real role boundaries

Use a disposable database where an administrator can create these test roles and objects. The setup role owns the table; neither tenant role owns it. The roles are intentionally non-login roles that an authorized test administrator can assume. This fixture models identity through the effective database role, not a user-supplied session variable.

CREATE ROLE tenant_a NOLOGIN NOBYPASSRLS;
CREATE ROLE tenant_b NOLOGIN NOBYPASSRLS;
CREATE SCHEMA rls_lab;

CREATE TABLE rls_lab.invoice (
    tenant_id text NOT NULL,
    invoice_id integer NOT NULL,
    amount integer NOT NULL,
    PRIMARY KEY (tenant_id, invoice_id)
);
INSERT INTO rls_lab.invoice VALUES
    ('tenant_a', 1, 100), ('tenant_b', 1, 200);

GRANT USAGE ON SCHEMA rls_lab TO tenant_a, tenant_b;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON rls_lab.invoice TO tenant_a, tenant_b;

ALTER TABLE rls_lab.invoice ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_rows ON rls_lab.invoice
    FOR ALL TO tenant_a, tenant_b
    USING (tenant_id = current_user)
    WITH CHECK (tenant_id = current_user);

The composite key lets both tenants have invoice number 1. It does not itself implement access control. The policy implements the row condition, and the role model determines whether current_user is an appropriate identity source. In a real deployment, forbid tenant credentials from assuming another tenant's role.

Test as the tenant role and keep each intentional error in a separate transaction or behind an appropriate savepoint:

BEGIN;
SET LOCAL ROLE tenant_a;
SELECT current_user;
SELECT * FROM rls_lab.invoice;
UPDATE rls_lab.invoice SET amount = 999
WHERE tenant_id = 'tenant_b' AND invoice_id = 1;
ROLLBACK;

Expected results are effective role tenant_a, only A's invoice, and zero rows changed by the cross-tenant update. These expectations are specification-derived; the fixture has not been run against a local PostgreSQL server here.

Independent test as tenant A	Expected outcome under this fixture
Select all invoices	Only A's row
Insert new invoice for A	Allowed with a fresh composite key
Insert invoice for B	Check violation
Update B's existing amount	Zero affected rows
Change A's tenant_id to B	Check violation; use a fresh invoice ID to avoid another conflict
Delete B's invoice	Zero affected rows
Run same select as ordinary owner	Both rows, unless FORCE applies

Then repeat symmetrically as B. A single positive test for A does not demonstrate isolation in both directions. Preserve the fixture's original state with rollback, and distinguish policy failures from unrelated key or column-privilege failures.

How do multiple policies combine?

Applicable permissive policies combine with OR; restrictive policies combine with AND, alongside the requirement for a permitting policy. Restrictive policies do not independently grant access. This means adding a broad permissive support policy can widen access rather than further constrain an existing tenant rule.

An interview example is a tenant predicate plus a permissive USING (true) policy available through an inherited role. For the affected command, the broad policy can make the tenant restriction ineffective. Audit the complete set of applicable policies and role memberships, not just the policy whose name includes “tenant.”

Write a truth table before changing policy types. Which roles require cross-tenant support access? Which commands should that access include? Is an additional geographic or archival restriction mandatory even for support? Express the intended combination first, then test it. Do not assume a collection of individually sensible policy names yields the intended boolean expression.

Can a tenant session setting be trusted?

A common shared-role design reads a setting such as app.tenant_id inside a policy. PostgreSQL's configuration functions provide current_setting and transaction-local set_config. They store context; they do not authenticate the claim in that context.

If a client can execute arbitrary SQL under the shared role and set its own tenant value, that value is not a trustworthy tenant identity. RLS based only on it cannot protect against that attacker. A trusted application layer must derive context from authenticated and authorized identity, prevent arbitrary SQL execution, and control the paths that establish context. Different threat models may require separate database identities or another carefully designed boundary.

For a trusted application using a connection pool, set context inside the same explicit transaction as the queries:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);
-- Execute bound, authorized tenant queries on this transaction.
COMMIT;

$1 denotes an application-bound value, not a tenant chosen from an unchecked request parameter. The true argument makes this setting local to the transaction. The SET documentation explains transaction-local behavior and why session-level settings can outlive a request.

Test commit, rollback, exceptions, and sequential A-then-B requests on the same physical connection. Missing context should fail closed, not fall back to a broad tenant. If using text-to-UUID casts, consider absent, empty, and malformed values separately. An error can be the intended fail-closed result, but the application must still cleanly finish the transaction before returning the connection.

What about views and SECURITY DEFINER functions?

A view can change whose permissions and policies govern access to underlying relations. Where appropriate, security_invoker views use the invoking user's relevant access context; ordinary view behavior must be examined with the owner's privileges in mind. The CREATE VIEW reference documents these distinctions. Do not validate the base table and assume every exposed view has the same boundary.

A SECURITY DEFINER function executes with its owner's privileges. If that owner bypasses RLS, the function may expose rows the caller could not query directly. Such a function can be legitimate, but its input validation, allowed callers, output scope, and object resolution become security-critical.

Follow the CREATE FUNCTION security guidance: use a safe search path, avoid attacker-writable object resolution, and review execution grants. In a review, trace the actual function body and owner instead of assuming a friendly helper name implies tenant isolation.

The same scrutiny belongs on reporting jobs and exports. A privileged batch job may intentionally access every tenant; its outputs, caches, and destinations must preserve the intended audience. RLS on the source table cannot retroactively restrict an unscoped file already exported by an authorized bypass role.

How do you make an isolation test convincing?

Build a matrix across roles, commands, and execution paths. Include direct table reads, views, functions, batch updates, and connection reuse. Verify actual returned rows and affected-row counts, not only HTTP status codes. A handler returning 200 after changing zero rows may hide a failed assumption.

For performance, inspect representative plans under the application role with realistic tenant sizes. A predicate on tenant_id may influence index design, but RLS is not itself an index. Functions or subqueries in policies can add cost and introduce snapshot or concurrency considerations. Measure the actual protected query, not an owner query that skipped the policy.

Keep the test environment explicit: PostgreSQL version, role attributes, memberships, ownership, grants, enabled/forced state, and applicable policies. These facts explain why the same SQL text can behave differently in a developer session and the application. None should be replaced by “it worked for me.”

Practise the broader authorization decision

Use the PostgreSQL interview guide for transaction and engine foundations. These adjacent exercises let you add RLS-specific role and tenant checks without claiming their original prompts are exclusively about RLS.

Question	RLS follow-up
Design an access control system (RBAC + resource-based)	Identify the trusted principal and object domain
Scale an Authorization Checker	Keep cached authorization scoped to the right tenant
Explain ACID and isolation levels	Separate transaction isolation from access isolation
Write PostgreSQL updates with date filters	Check both eligible old rows and acceptable new rows
Implement an Idempotent Versioned Database Update	Preserve tenant scope through retries and version checks

Finish by explaining one bypass role, one rejected write, and one reused-connection test. That combination demonstrates the real interview skill: defining and verifying an access boundary rather than merely displaying valid policy syntax.

Sources and Further Reading

• PostgreSQL: Row Security Policies
• PostgreSQL: CREATE POLICY
• PostgreSQL: CREATE ROLE
• PostgreSQL: GRANT
• PostgreSQL: Configuration Settings Functions
• PostgreSQL: SET
• PostgreSQL: CREATE VIEW
• PostgreSQL: CREATE FUNCTION