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

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.

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.

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;

// 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

• ID: database-design-operations.security-access.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:

  -- 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:

  // 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 }
  })