# Row-Level Security (RLS) or equivalent access control on multi-tenant tables

- **Pattern:** `ab-000914` (`database-design-operations.security-access.row-level-security`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000914
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000914)

## Why it matters

Multi-tenant tables without Row-Level Security allow any authenticated user to read or modify other users' data by supplying a different ID in the request. In Supabase applications, tables without RLS policies are readable and writable by any authenticated session — there is no application-layer fallback. This is OWASP A01 (Broken Access Control) and CWE-284, and it is an IDOR (Insecure Direct Object Reference) vulnerability: an attacker iterates IDs and retrieves other users' posts, files, or messages. Application-level filtering applied inconsistently across routes has the same result — every unguarded route is a data breach vector.

## Severity rationale

High because missing row-level isolation allows any authenticated user to access or modify other users' data, constituting an IDOR vulnerability with direct GDPR and data breach implications.

## Remediation

Enable RLS on every table containing user-specific data in Supabase and create policies for each operation. For non-Supabase applications, enforce ownership checks in reusable middleware, not inline per-route.

```sql
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);
```

For application-level auth, centralize ownership checks: `if (resource.userId !== session.userId) throw new ForbiddenError()`. Never rely on each route handler applying this check independently — a single missed route exposes all data.

## Detection

- **ID:** `row-level-security`
- **Severity:** `high`
- **What to look for:** Determine whether the application is multi-tenant — does it store data for multiple users or organizations in shared tables? For multi-tenant applications, check how data isolation is enforced. In Supabase: look for `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` and `CREATE POLICY` statements in migration files or Supabase SQL editor exports. Check that every user-specific table has RLS enabled. For raw Postgres with application auth: look for RLS policies in migration files. For applications without database-level RLS: look for consistent application-level filtering — every query that retrieves user-specific data should have a `WHERE user_id = $currentUserId` or equivalent. Check multiple query locations to confirm filtering is applied consistently (not just in some routes).
- **Pass criteria:** Data isolation is enforced. Supabase applications have RLS enabled on all tables containing user-specific data with appropriate policies — enumerate every table with user-specific data and confirm at least 1 RLS policy or application-level filter per table. Or application-level filtering is applied consistently in all queries that access user-specific data — not just most queries, but all of them.
- **Cross-reference:** For API-level authorization checks beyond database access control, the API Security audit covers endpoint authorization patterns and IDOR prevention.
- **Fail criteria:** No RLS on multi-tenant tables in Supabase/Postgres. Application-level filtering is inconsistent — some endpoints filter by user ID, others don't (IDOR vulnerability). Queries access other users' data without authorization checks.
- **Skip (N/A) when:** Single-tenant application with no user-specific data isolation needed (e.g., admin-only tool with a single account). Or application has no user authentication and all data is public.
- **Detail on fail:** Specify which tables lack protection. Example: `"Supabase project has 'posts', 'comments', and 'files' tables with no RLS policies — any authenticated user can read or write any row."` or `"GET /api/posts/[id] fetches post by ID without checking post.userId matches the authenticated user — IDOR vulnerability."`.
- **Remediation:** Enable RLS in Supabase:

  ```sql
  -- Enable RLS on tables containing user data
  ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
  ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

  -- Policy: users can only read their own posts
  CREATE POLICY "Users can view own posts"
    ON posts FOR SELECT
    USING (auth.uid() = user_id);

  -- Policy: users can only insert posts as themselves
  CREATE POLICY "Users can insert own posts"
    ON posts FOR INSERT
    WITH CHECK (auth.uid() = user_id);

  -- Policy: users can only update their own posts
  CREATE POLICY "Users can update own posts"
    ON posts FOR UPDATE
    USING (auth.uid() = user_id);

  -- Policy: users can only delete their own posts
  CREATE POLICY "Users can delete own posts"
    ON posts FOR DELETE
    USING (auth.uid() = user_id);

  -- For public content readable by anyone
  CREATE POLICY "Published posts are publicly readable"
    ON posts FOR SELECT
    USING (status = 'published');
  ```

  For application-level filtering (non-Supabase), centralize auth checks in middleware:
  ```ts
  // middleware/requireOwnership.ts
  export async function requireOwnership(resourceUserId: string, requestUserId: string) {
    if (resourceUserId !== requestUserId) {
      throw new ForbiddenError('Access denied')
    }
  }
  ```

---

## External references

- cwe CWE-284
- owasp A01

Taxons: data-integrity, access-control

HTML version: https://auditbuffet.com/patterns/ab-000914