Auth and RBAC Are in Place, So Why Can Users Read Each Other's Data?

An API can verify every JWT, run a role check on every route, and still let user B open user A’s invoice by editing one path segment. The reason is structural: RBAC answers whether a role may perform invoice:read on the resource type Invoice, and it never answers whether this caller may read invoice 8812. That second question is object-level authorization, and its absence is the top entry in the OWASP API Security Top 10 (API1:2023, Broken Object Level Authorization, or BOLA, which most people still call IDOR). For a team with RBAC already shipped and a relational database underneath, the default I recommend is scoped queries behind a repository that requires a Principal, plus a two-user cross-tenant test generated from the route table in CI. On shared-schema multi-tenant PostgreSQL, add Row-Level Security as the database backstop. The usual hardening steps leave that gap open; four methods close it, and a route-driven test keeps it closed.

Three Questions, Three OWASP Categories#

RBAC, JWT scopes and gateway route policies all answer the same question: may this caller use this function? OWASP files failures of that question under API5:2023, Broken Function Level Authorization (BFLA). The question that leaks data is a different one, and the property-level question is a third.

Question	OWASP category	Typical control	Example failure
May this caller use this function?	API5:2023 BFLA	RBAC, JWT scopes, route guards	A viewer calls DELETE /admin/users/:id
May this caller touch this object?	API1:2023 BOLA	Ownership, tenant or relationship check	User B calls GET /invoices/8812, which belongs to A
May this caller read or write this field?	API3:2023 BOPLA	Output DTOs, write allowlists	A user sets role: "admin" through PATCH

OWASP rates BOLA as easy to exploit, widespread, and easy to detect. The attacker changes an object ID in the path, the query string, a header or the body, and the API returns someone else’s record. Because the role check sits in the BFLA row, it passes every time: user B does hold invoice:read. The check that would have failed was never written.

The vulnerable shape is short enough to recognise on sight. This is a Hono handler with Prisma, but the same pattern appears in Express, Next.js route handlers and GraphQL resolvers:

// RBAC passes: user B has the "accountant" role, which grants invoice:read.
app.get('/invoices/:id', requirePermission('invoice:read'), async (c) => {
  const invoice = await prisma.invoice.findUnique({
    where: { id: c.req.param('id') }, // any invoice in the database
  });
  if (!invoice) return c.json({ error: 'Not found' }, 404);
  return c.json(invoice);
});

The request passes three gates and reaches the database with no predicate that mentions the caller.

In a project I worked on, the list endpoint was scoped correctly: GET /invoices filtered by the caller’s tenant from the first commit. The detail endpoint and the PDF export were added later by different people, and both looked up the invoice by ID alone. The RBAC middleware was on all three routes. Nothing in code review flagged the difference, because the role check was visibly present and the missing predicate was invisible.

The Same Gap in Public Incidents#

The pattern is common enough that the incident write-ups read like variations on one template. OWASP Top 10:2025 reports that every application in its tested data set showed some form of broken access control. That figure covers the whole category, not BOLA alone, and CWE-639 (authorization bypass through user-controlled key) is among the weaknesses it maps.

Case	What was in place	What was missing
USPS Informed Visibility (2018)	A login was required to use the API	Any authenticated user could query other users’ account details; roughly 60 million accounts were exposed, per KrebsOnSecurity
Peloton (2021)	After the first fix, endpoints required authentication	Pen Test Partners documented that the same data stayed readable by every logged-in account, and anyone could self-register, until a later fix
First American Financial (2019)	Document links carried a valid record identifier	Changing the identifier in a link returned other customers’ documents; hundreds of millions of files were reachable
Optus (2022)	Access control was fixed on the main portal	The regulator alleges the same coding error was not fixed on the API domain, and the attack was a simple process of trial and error

Two of these cases carry the lesson. Peloton shows that adding authentication to an endpoint does not close BOLA, since every attacker can become an authenticated user. Optus (as alleged by ACMA in its court filing, not yet a finding) shows the failure mode of per-endpoint checks: the fix landed on one code path and was missed on its twin.

Hardening Steps That Leave the Gap Open#

Several common responses reduce the ease of exploitation without adding the missing check. They are worth doing, but none of them is the control.

Measure	What it does	Why BOLA stays open
UUIDs instead of sequential IDs	Slows enumeration	IDs leak through URLs, logs, shared links, referrers and other API responses. The OWASP IDOR cheat sheet frames complex identifiers as defence in depth and says access checks remain essential
Hiding the button in the UI	Improves UX	The API is still callable directly
API gateway authentication and route policies	Function-level: route plus token	The gateway does not know who owns invoice 8812 unless it calls the domain, at which point it is a handler check in a different place
JWT scopes or claims (invoice:read)	Function-level (the BFLA row)	A scope says nothing about which invoice
if (params.userId === jwt.sub)	Protects /users/:userId/* routes	OWASP’s API1 page says this covers only a small subset of cases and is not a sufficient solution
Rate limiting	Slows mass extraction	One leaked record is still a leak

The UUID row deserves a precise reading. The OWASP API1 page lists random identifiers among its prevention items, and the IDOR cheat sheet explains the role they play: they make guessing harder, and they do nothing once an identifier has been seen. Treat them as hardening that complements the object check.

Scoped Queries Behind a Repository#

The principle is to fetch within the caller’s scope, never to fetch and then check. The OWASP IDOR cheat sheet contrasts an insecure lookup that searches all projects with a scoped lookup that searches only the current user’s projects, and recommends method shapes like findByIdAndOwnerId(id, currentUserId). In SQL the scope is part of the lookup key on both reads and writes:

-- Read: tenant is part of the key, not a post-check
SELECT id, amount, status
FROM invoices
WHERE id = $1 AND tenant_id = $2;

-- Write: the scope is repeated in the write itself
UPDATE invoices SET status = $3
WHERE id = $1 AND tenant_id = $2
RETURNING id;   -- zero rows => 404

The repository is what turns this from a convention into the only available path. There is no findById(id) without a Principal, and the Principal is built once from the verified token:

import type { PrismaClient } from '@prisma/client';

interface Principal {
  tenantId: string;
  userId: string;
}

// Same error for "does not exist" and "exists but is not yours".
export class NotFoundError extends Error {}

// The only way handlers reach invoice data.
export function invoiceRepo(db: PrismaClient, p: Principal) {
  const scope = { tenantId: p.tenantId };
  return {
    findById: (id: string) => db.invoice.findFirst({ where: { id, ...scope } }),
    list: (opts: { take: number; cursor?: string }) =>
      db.invoice.findMany({
        where: scope,
        take: opts.take,
        ...(opts.cursor ? { skip: 1, cursor: { id: opts.cursor } } : {}),
        orderBy: { id: 'asc' },
      }),
    async markPaid(id: string) {
      const { count } = await db.invoice.updateMany({
        where: { id, ...scope },
        data: { status: 'PAID' },
      });
      if (count === 0) throw new NotFoundError();
    },
  };
}

// Tenant context comes from the verified token, never from a client header.
app.use('*', async (c, next) => {
  const claims = c.get('jwtPayload');
  c.set('principal', { tenantId: claims.tenant_id, userId: claims.sub });
  await next();
});

Two details carry most of the value. First, tenant_id is a mandatory predicate, and the tenant comes from the token or session. A client-supplied X-Tenant-Id header is acceptable only if it is validated against the user’s memberships before it reaches the repository. Second, the write path uses updateMany with the scope and checks the count, so there is no window between a check and an unscoped update({ where: { id } }). Post 102 in the permission-systems series explains the generic not-found error that this repository relies on, so I will not repeat the enumeration argument here.

The repository needs a fence, because the raw client is still importable. An ESLint no-restricted-imports rule that forbids the Prisma client import outside src/repositories/ is enough to make the unscoped path show up in review as a lint failure. The snippets here target Prisma ORM 7 with the legacy @prisma/client package; with the prisma-client generator, the rule has to name the generated output path instead. Background jobs and admin tools then need an explicit system principal with its own audit trail. That is a design decision made once, and it turns the unscoped back door into a named, audited path.

The trade-off is that this method models ownership and tenancy well and nothing else. Sharing, teams and delegated access need either richer scope objects or a relationship engine feeding the same repository, which is where the override section below comes in. Post 104 covers the can() and policy-builder approach for attribute-based rules, so I link rather than rebuild it. What the repository buys in return is that the scoped lookup is the only shape it offers, so no handler has to remember it. A lint rule is a review signal, not a compiler boundary. Raw SQL, a second client and separate services still bypass it, which is the gap the next section covers.

Row-Level Security as the Database Backstop#

The repository covers code that goes through it. Raw SQL in a migration script, a reporting job, or a new service written by someone who never saw the lint rule does not. For shared-schema multi-tenant SaaS on PostgreSQL, Row-Level Security makes the database refuse rows outside the tenant even when the application forgets the predicate. Post 106 describes RLS as a safety net for row-level isolation in one sentence; this section is the operational detail behind that sentence.

-- The app connects as a role that does not own the tables and cannot bypass RLS.
CREATE ROLE app_user LOGIN NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- the table owner is subject to policies too

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

The application sets the tenant per transaction, so the setting dies at COMMIT or ROLLBACK and cannot bleed into the next request on a pooled connection:

import type { Pool, PoolClient } from 'pg';

export async function withTenant<T>(
  pool: Pool,
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // is_local = true: the value applies only to the current transaction
    await client.query(`SELECT set_config('app.tenant_id', $1, true)`, [tenantId]);
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Four facts from the PostgreSQL documentation explain most of the surprises people hit with this setup. When RLS is enabled on a table with no policy, a default-deny policy applies and no rows are visible. Superusers and roles with BYPASSRLS always bypass row security. Table owners bypass it too unless FORCE ROW LEVEL SECURITY is set. That is the usual reason RLS appears to work in psql but not in the application, or the other way round: migrations tend to run as the owner. Finally, referential-integrity checks (primary key, unique, foreign key) always bypass row security, and the docs warn that this can leak existence through a covert channel.

The pooling caveat matters in practice. PgBouncer’s feature matrix marks session-level SET and RESET as unsupported in transaction pooling mode, so a SET app.tenant_id outside a transaction can land on whichever connection the pooler hands out next. A transaction-local set_config(..., true) inside a transaction is the safe form, and it is also the form Prisma’s official RLS client-extension example uses. That example is explicitly demo-only, and its README notes that explicit $transaction() calls may conflict with the extension. That is why the snippet above uses plain pg.

The nullif(..., '') wrapper handles an undocumented quirk. A thread on the pgsql-general list reports that after a custom setting has been assigned with SET LOCAL, current_setting returns an empty string once the transaction ends. Running the same sequence on PostgreSQL 17.10 shows the same empty string after both commit and rollback. Tom Lane’s explanation on the list is that a custom setting exists once it has been assigned, and its reset value is the empty string. He also calls the pattern an unsupported use of a mechanism meant for extension settings, so treat the nullif as required rather than optional. A bare ''::uuid cast then raises an error, which fails closed but is noisy. With nullif, the comparison against NULL simply matches no rows, which is the quieter fail-closed outcome.

On performance, Supabase’s RLS guidance applies to any PostgreSQL deployment. Index the columns the policy filters on. Wrap function calls inside the policy in (select ...) so they evaluate once per statement instead of once per row; this is only valid when the function’s result does not depend on the row. Keep joins out of policies; use IN or ANY over a precomputed set. The right number for your workload comes from EXPLAIN ANALYZE before and after enabling the policy, measured on your own tables.

The trade-off is operational: two database roles (a migration role that owns tables and a runtime role that does not), a transaction per request, and debugging sessions where “no rows” is indistinguishable from “no data”. Everything above assumes an application server in front of the database, where RLS is the backstop and the repository is the primary control. In architectures where clients query the database directly, as with Supabase, RLS is the primary boundary and the policies carry the full authorization model.

This setup guards against a forgotten predicate. It does not guard against SQL injection. Any role can call set_config, so injected SQL that runs on the same connection can switch app.tenant_id to another tenant. Parameterised queries stay a separate, required control.

Choosing Between the Four Methods#

The four methods differ most on one axis: what happens when someone forgets.

Method	Survives a forgotten endpoint?	List endpoints	Sharing and relationships	Operational cost	Main failure mode
Handler check (fetch, then compare)	No	Manual per list	Ad hoc	None	Opt-in; one missed route leaks
Scoped repository	Yes, if the raw client is fenced off	Native (WHERE scope)	Limited to ownership and tenancy	Low	Escape hatches: raw SQL, admin scripts, jobs
PostgreSQL RLS	Yes, at the database	Native	Awkward beyond tenant or owner columns	Medium (roles, pooling, migrations)	Owner or superuser bypass; session SET with pooling; slow policies
ReBAC or policy engine	Only where it is wired in	Needs a list-objects or filter API	Strong	High (extra service, data sync)	Dual-write drift; per-item check loops

When to Override the Default#

Handler-level checks are the method most teams already have in a few places, and they work for the endpoint you remembered:

app.get('/invoices/:id', requirePermission('invoice:read'), async (c) => {
  const { tenantId } = c.get('principal');
  const invoice = await prisma.invoice.findUnique({ where: { id: c.req.param('id') } });
  if (!invoice || invoice.tenantId !== tenantId) {
    return c.json({ error: 'Not found' }, 404);
  }
  return c.json(invoice);
});

They are acceptable as a transitional step while routes migrate to the repository, and for nothing longer. Every new route (export, bulk, duplicate, webhook replay, GraphQL resolver) needs a person to remember the comparison, and the check on read says nothing about the write that follows in a different function. OWASP’s own third example scenario for API1 is a GraphQL delete mutation that takes a document ID with no permission check, which is the same shape.

Relationship and policy engines are the override for a different problem: access that comes from sharing, nested folders or org-to-team-to-project inheritance instead of a single owner_id or tenant_id column. The SpiceDB and Auth0 FGA comparison and the Cedar, Rego and OpenFGA comparison on this site cover the engines, the dual-write problem and the policy languages in depth. The angle those posts leave open is list endpoints. A per-object check() protects GET /invoices/:id; GET /invoices needs a filter, and a loop of checks over every candidate row is the wrong tool. OpenFGA’s ListObjects returns the objects of a type that a user has a relationship with, and its documentation warns that performance varies drastically with model complexity and tuple count. OPA’s partial evaluation turns a policy into residual conditions that can become a data filter. Either way, the ID set or the filter still has to be pushed into the query, so in practice the engine feeds the repository:

findById: async (id: string) => {
  const { allowed } = await fga.check({
    user: `user:${p.userId}`,
    relation: 'viewer',
    object: `invoice:${id}`,
  });
  return allowed ? db.invoice.findFirst({ where: { id, ...scope } }) : null;
},

The trade-off is a network hop per check, a second source of truth for relationships, and consistency questions that the SpiceDB post already works through. Reach for an engine when the relationship graph is the product, and keep the repository chokepoint either way.

The Two-User Test in CI#

OWASP’s prevention list for API1 ends with an instruction to write tests that evaluate the authorization mechanism and to refuse deployments that make them fail. The IDOR cheat sheet adds the method: create multiple accounts with different authorization scopes, and verify that access is denied regardless of whether the identifier is predictable. The harness below does both. It seeds tenant A (with one invoice) and tenant B (with a token), then has B request A’s object on every ID-bearing route. A status code alone proves little on PATCH and DELETE, because a handler can change the row and still answer 404. Each case therefore also checks that the response does not echo A’s data and that A’s row is exactly as seeded. The route table is the single source of truth. A second test fails when the router has a parameterised route the table does not cover, which is the guard against the Optus failure mode. It proves coverage, not that each expectation in the table is right; that part stays a review job.

import { describe, expect, it } from 'vitest';
import { app } from '../src/app';
import { fixtures } from './fixtures'; // seeds tenant A invoice, tenant B token; reads A's row directly

const idRoutes = [
  { method: 'GET', pattern: '/invoices/:id' },
  { method: 'PATCH', pattern: '/invoices/:id', body: { status: 'PAID' } },
  { method: 'DELETE', pattern: '/invoices/:id' },
  { method: 'GET', pattern: '/invoices/:id/pdf' },
] as const;

describe.each(idRoutes)('cross-tenant $method $pattern', (route) => {
  it('user B cannot reach tenant A object', async () => {
    const res = await app.request(route.pattern.replace(':id', fixtures.tenantA.invoiceId), {
      method: route.method,
      headers: {
        Authorization: `Bearer ${fixtures.tenantB.token}`,
        'Content-Type': 'application/json',
      },
      body: 'body' in route ? JSON.stringify(route.body) : undefined,
    });
    expect(res.status).toBe(404);
    // A 404 alone proves little on writes: the response must not echo A's data,
    // and A's row must be exactly as seeded.
    expect(await res.text()).not.toContain(fixtures.tenantA.invoiceId);
    expect(await fixtures.readTenantAInvoice()).toEqual(fixtures.tenantA.invoice);
  });
});

it('every parameterised route has a cross-tenant case', () => {
  const registered = app.routes
    .filter((r) => r.method !== 'ALL' && r.path.includes(':'))
    .map((r) => `${r.method} ${r.path}`);
  const covered = idRoutes.map((r) => `${r.method} ${r.pattern}`);
  expect(new Set(registered)).toEqual(new Set(covered));
});

Hono exposes app.routes directly, and each middleware registered with app.use('*', ...) appears there with method ALL, which the filter drops. The Set comparison also absorbs the duplicate entries Hono records when a route has per-route middleware. On Express the same inventory needs a walk over the router stack, and the rest of the harness is unchanged apart from swapping app.request for Supertest.

Three further checks belong in the same pipeline when RLS is in place. The first connects as app_user with app.tenant_id set to tenant B and asserts that SELECT count(*) FROM invoices WHERE tenant_id = '<A>' returns zero. The second runs the same count with app.tenant_id never set, and again after a committed transaction, and expects zero both times on the PostgreSQL version you run. The third is a catalog query that fails when any table with a tenant_id column is missing either flag:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
  AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);
-- expected: zero rows

For manual and dynamic testing, Autorize is an open-source Burp Suite extension. It replays a higher-privileged user’s traffic with a lower-privileged or unauthenticated session and flags responses that match; its flags still need a human to confirm. ID fuzzing follows the same idea by hand: harvest identifiers from user A’s session and substitute them into user B’s requests in the path, query string, body and headers, since OWASP lists all four as BOLA vectors.

Static analysis is weak here for a structural reason. Conventional SAST follows tainted data from a source to a sink, and a missing AND tenant_id = $2 has neither; the scanner has no business context that says user B should not see invoice 8812. Several vendors now claim that AI-assisted analysis finds this class of logic flaw, SonarSource’s Hunter Agent among them. Those claims are vendor-sourced and not independently benchmarked, so treat such tools as an extra signal and keep the two-user test as the control.

Mistakes That Reopen the Gap#

• Scoping the read but not the write. The ownership comparison runs on findUnique, and the update({ where: { id } }) three lines later has no scope. Repeat the scope in every UPDATE and DELETE, or use updateMany with a count check as in the repository above.
• Tenant ID taken from the client. An X-Tenant-Id header or a tenantId field in the body accepted at face value. Derive it from the verified token or session, or validate it against the user’s memberships first.
• RLS enabled, but the application connects as the table owner. The policies silently do not apply. Set FORCE ROW LEVEL SECURITY and run the application under a separate non-owner NOBYPASSRLS role.
• Session-level SET app.tenant_id behind a transaction-mode pooler. The setting lands on the wrong connection. Use set_config(..., true) inside an explicit transaction.
• Casting current_setting(...) without nullif(..., ''). The empty-string state turns into a cast error after the first transaction ends.
• 403 for “not yours” and 404 for “does not exist”. The difference confirms existence to an attacker. Return the same response for both.
• Testing with one tenant or with the admin account. Post 106 lists untested cross-tenant scenarios as a pitfall; the harness above is the fix.
• Per-item check() loops on list endpoints. Push a filter into the query instead, whether that filter is a scope, a list-objects result or a partially evaluated policy.
• GraphQL nested resolvers. The parent query is scoped, and the node(id:) or nested invoice(id:) resolver is not. Resolvers call the same repository.
• Declaring victory after BOLA. The user may own the object and still must not set role or read internalNotes. That is API3 BOPLA, and post 105 covers field-level permissions for it.

The scoped repository with a cross-tenant CI test holds as the default for any team with RBAC already shipped, a relational database, and access rules that reduce to ownership or tenancy. On shared-schema multi-tenant PostgreSQL, RLS with FORCE, a non-owner role and transaction-local set_config belongs underneath it. Reach for a relationship or policy engine when sharing and nested relationships define access, and even then route its output through the same repository. If the codebase currently relies on handler checks, the first step is the route-inventory test, because it tells you which endpoints are unscoped before any refactor begins.

References#

• OWASP API1:2023 Broken Object Level Authorization (opens in new tab) - Definition, attack vectors, example scenarios and prevention list for BOLA, including why comparing the session user ID with the path parameter is not sufficient
• OWASP Insecure Direct Object Reference Prevention Cheat Sheet (opens in new tab) - The scoped-lookup pattern, complex identifiers as defence in depth, and multi-account testing guidance
• OWASP Top 10:2025 A01 Broken Access Control (opens in new tab) - Prevalence data for broken access control in web applications and its mapping to CWE-639
• PostgreSQL Documentation: Row Security Policies (opens in new tab) - Official reference for RLS semantics: default deny, BYPASSRLS, table-owner bypass, FORCE ROW LEVEL SECURITY and referential-integrity covert channels
• PostgreSQL Documentation: System Administration Functions (opens in new tab) - Reference for set_config with transaction-local scope and current_setting with missing_ok
• pgsql-general: SET LOCAL variable doesn’t become undefined after commit (opens in new tab) - Mailing-list thread showing that custom settings revert to an empty string after a transaction ends
• pgsql-general: Tom Lane on RESET, NULL and empty-string settings (opens in new tab) - Explanation of why a custom setting’s reset value is the empty string once it has been assigned, and why the pattern is unsupported
• PgBouncer Features (opens in new tab) - Feature matrix showing that session-level SET and RESET are unsupported in transaction pooling mode
• Prisma Client Extensions: Row-Level Security example (opens in new tab) - Official example of setting a transaction-local PostgreSQL variable from Prisma, with its documented caveats
• Supabase: RLS Performance and Best Practices (opens in new tab) - Guidance on indexing policy columns and avoiding per-row function evaluation in RLS policies
• OpenFGA: Perform a List Objects Request (opens in new tab) - How a relationship-based engine answers which objects a user can see, and the performance caveats for list endpoints
• Open Policy Agent: Evaluating a Data Filter Policy (opens in new tab) - Using partial evaluation to turn authorization policies into query filters
• PortSwigger/autorize on GitHub (opens in new tab) - Burp Suite extension that replays requests with a lower-privileged session to detect authorization gaps
• KrebsOnSecurity: USPS Site Exposed Data on 60 Million Users (opens in new tab) - An authenticated API that let any logged-in user query other users’ account data
• Pen Test Partners: Tour de Peloton, Exposed User Data (opens in new tab) - Primary disclosure showing that adding authentication left user data readable by every logged-in account
• SecurityWeek: First American Financial Exposed Millions of Sensitive Documents (opens in new tab) - A link parameter change that exposed other customers’ documents
• CSO Online: Optus breach occurred due to a coding error, alleges ACMA (opens in new tab) - The regulator’s account of an access-control error fixed on one domain and missed on the API domain
• SonarSource: SonarQube Hunter Agent detects logical flaws (opens in new tab) - A vendor’s case for AI-assisted detection of broken access control, as the counterpoint to the traditional SAST blind spot