Row-level security that silently does nothing

Two independent bypasses are in play.

1. Ownership. A table's owner is exempt from its own RLS policies. Since app owns orders, the policy is never evaluated for app. Superusers and roles with BYPASSRLS are also always exempt. Enabling RLS alone fixes nothing here.

2. Session state. Even with RLS active, SET app.tenant_id on a pooled connection is session-scoped. If a request fails before resetting, or a pooler hands the connection to another tenant's request, the setting leaks and the next statement runs as the wrong tenant.

Fix:

CREATE ROLE app_owner NOLOGIN;
ALTER TABLE orders OWNER TO app_owner;   -- all tables
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- binds the owner too

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true)::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

• FORCE is what makes the policy apply to the owner - essential when DBA-initiated maintenance or a support engineer connects as a privileged role.
• Use current_setting(..., true) so a missing setting yields NULL and the predicate is false: fail closed, returning zero rows, instead of raising an error that an error handler might paper over.
• Set the tenant with SET LOCAL inside the transaction so it reverts on commit, and have the pooler issue DISCARD ALL on checkout. Never rely on a SET done once per connection.
• WITH CHECK matters as much as USING: without it a tenant can INSERT rows labelled with another tenant's id.

Add a second, permissive policy scoped to the reporting role rather than granting BYPASSRLS.

GRANT SELECT ON orders TO report;
CREATE POLICY report_read_all ON orders
  FOR SELECT TO report USING (true);

Policies are OR-ed, so report sees everything and app still sees only its own rows. This is auditable in the catalog, applies only to the tables you choose, and only to SELECT.

Why not BYPASSRLS:

• It can only be granted by a superuser, and it applies to every table and every command, including future tables nobody reviewed.
• It is invisible in query text and easy to leave on a role that is later reused. A policy is a reviewable object with a name and an owner.

For the analyst: do not create a parallel grant path. GRANT report TO analyst; gives exactly the read-only surface the reporting role has, and nothing else - as long as report itself holds only SELECT and the analyst is created NOLOGIN-safe and NOINHERIT is handled deliberately. Alternatively create a dedicated analyst role with GRANT SELECT plus membership in the reporting policy's role.

Keep FORCE ROW LEVEL SECURITY in place even after the grants: the reporting policy is what grants breadth, and ownership should never be an implicit backdoor.

Every query against the table gains an extra qual, so an index whose leading column is tenant_id becomes far more valuable: without it, each tenant's query scans and filters all 60 tenants' rows. Confirm (tenant_id, ...) leads the index on every RLS-protected table that app queries.

Check specifically for these traps:

• Volatility of the qual. current_setting is stable, which is fine. If someone wraps it in a VOLATILE helper function, the planner may treat it as an opaque filter and lose index and join pushdown - write it inline or mark helpers STABLE.
• Function-backed policies and pushdown. A policy calling a user function that is not marked LEAKPROOF can inhibit predicate pushdown into views and subqueries. Test with EXPLAIN on the real queries, not on a toy table.
• Prepared statements and plan caching. A generic plan made under one tenant setting is still valid because the setting is read at execution time, not plan time - but verify, because a policy that folds the value into the plan would produce wrong results, not just slow ones.
• Empty or NULL setting. USING (false) plans should be fast; make sure the application fails visibly (zero rows) rather than running an unindexed scan against every query.

Finally, run a leak test as part of the release: connect as app, set tenant A, attempt to read, update and delete rows of tenant B, and assert all three fail.