# Database user follows least privilege (not using superuser for application)

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

## Why it matters

An application connecting to Postgres as the `postgres` superuser has unrestricted DDL access — it can drop tables, create new databases, and read any schema. If the application is compromised through a SQL injection vulnerability or a dependency supply-chain attack, the attacker has full database control. CWE-272 (least privilege violation) and OWASP A01 both require that each system component operates with the minimum permissions necessary. Using `service_role` in Supabase client-side code is the same failure pattern: RLS is bypassed entirely, and any client-side exploit has admin-level database access.

## Severity rationale

Medium because a superuser application connection amplifies the blast radius of any application-layer compromise to full database control, but exploitation still requires a secondary vulnerability.

## Remediation

Create a dedicated application database role with DML-only privileges. Use the superuser only for running migrations. In Supabase, use `anon`/`authenticated` keys for API routes and reserve `service_role` for trusted server-only admin operations.

```sql
CREATE USER app_user WITH PASSWORD 'use_env_var';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
```

```ts
// Supabase — correct key per context
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!) // client-safe
const admin = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!, {
  auth: { autoRefreshToken: false, persistSession: false }
}) // server-only, bypasses RLS
```

## Detection

- **ID:** `least-privilege`
- **Severity:** `medium`
- **What to look for:** Count every database connection configuration and check the database connection user/role used by the application. In the connection string pattern in `.env.example`, look at the username segment: `postgresql://username:password@host/db`. Common violations: the username is `postgres` (superuser on most Postgres installations) or an account with `SUPERUSER`, `CREATEDB`, or `CREATEROLE` privileges. Check whether the same database user is used for both the application runtime (which only needs DML: SELECT, INSERT, UPDATE, DELETE) and migrations (which need DDL: CREATE TABLE, ALTER TABLE, DROP TABLE). For Supabase: check whether the `service_role` key (which bypasses RLS) is used in contexts where the `anon` or `authenticated` role is appropriate. The `service_role` key should only be used server-side for admin operations.
- **Pass criteria:** Application connects with at least 1 limited database role that has DML privileges only on application tables (not DDL, not superuser). A separate, more privileged role is used for running migrations. Supabase uses `anon`/`authenticated` keys for client-facing operations and `service_role` only in trusted server contexts.
- **Fail criteria:** Application connects as `postgres` superuser in production. Same role used for application runtime and schema migrations. Supabase `service_role` key used client-side or in contexts where it bypasses RLS inappropriately.
- **Skip (N/A) when:** Using a fully managed service (e.g., Supabase with its built-in role system configured appropriately, Firebase) that handles role separation automatically and the application uses the correct key for each context.
- **Detail on fail:** Specify the privilege issue. Example: `"Database connection in .env.example uses 'postgres' superuser account — application has unrestricted database access including DDL."` or `"Supabase SUPABASE_SERVICE_ROLE_KEY used in client-side code — bypasses RLS for all users."`.
- **Remediation:** Create a dedicated application role:

  ```sql
  -- Create an application user with minimal privileges
  CREATE USER app_user WITH PASSWORD 'use_env_var_not_this';

  -- Grant DML access to application tables only
  GRANT CONNECT ON DATABASE myapp TO app_user;
  GRANT USAGE ON SCHEMA public TO app_user;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

  -- For future tables (important — apply to new tables automatically)
  ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

  -- Migration runner uses the more privileged postgres user
  -- Application runtime uses app_user
  ```

  In Supabase, use the correct key per context:
  ```ts
  // Client-side / public API — use anon key (respects RLS)
  const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!)

  // Server-side admin operations — use service_role (bypasses RLS — use carefully)
  const supabaseAdmin = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!, {
    auth: { autoRefreshToken: false, persistSession: false }
  })
  ```

---

## External references

- cwe CWE-272
- owasp A01

Taxons: access-control

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