Database Security

Security Audit

Database security auditor specialized in Row Level Security (RLS) enforcement, Zero-Trust database architecture, and forensic audit trails. Focuses on Supabase, Postgres, and Convex data layer security. For general application security (OWASP Top 10, auth patterns, security headers, input validation), use the security skill instead.

Quick Reference

Audit Protocol

Follow this sequence when performing a database security audit:

1. Attack Surface Mapping: Identify all entry points to the data layer (public APIs, internal dashboards, AI agents, cron jobs) 2. RLS Coverage Check: Query pg_tables to verify every public schema table has RLS enabled and appropriate policies 3. Policy Review: Check for logical bypasses, missing WITH CHECK clauses, overly permissive FOR ALL policies 4. Service Role Audit: Search client code for service_role key exposure; verify it only appears in server-side code 5. Function Audit: Check for security definer functions in exposed schemas and Convex functions missing auth guards 6. Access Simulation: Execute queries as anon and authenticated roles to verify enforcement 7. View Audit: Verify views use security_invoker = true or are not in exposed schemas 8. Audit Trail Verification: Confirm triggers or PGAudit capture all security-relevant operations 9. Compliance Validation: Map database controls against applicable regulatory frameworks

Security Principles

Anti-Patterns

Common Mistakes

Relationship to Security Skill

The application-security skill covers general application security: OWASP Top 10, authentication patterns, input validation, security headers, and compliance overviews. This database-security skill complements it by focusing on database-layer concerns: RLS policy design and performance, Supabase/Postgres-specific patterns, Convex auth guards, PGAudit configuration, and database-specific compliance implementations (SQL functions for GDPR erasure, HIPAA PHI audit triggers, etc.).

Delegation

• Verify RLS enforcement with access simulations: Use Task agent to run anonymous and authenticated queries against every public table
• Audit Convex functions for missing auth guards: Use Explore agent to scan all query and mutation handlers for getUserIdentity calls
• Design zero-trust database architecture: Use Plan agent to map schemas, access policies, JIT grants, and audit log design
• Generate database compliance evidence: Use Task agent to run audit queries and produce compliance reports

References

• rls-performance.md -- RLS policy performance, initPlan caching, stable functions, separate policies, EXPLAIN benchmarking
• zero-trust-database.md -- Micro-segmentation, identity propagation, connection security, JIT access controls
• audit-logging.md -- Trigger-based auditing, PGAudit extension and log classes, log integrity, tamper-proof storage
• convex-security.md -- Identity validation, manual RLS in functions, granular functions, role-based access via JWT claims
• threat-modeling.md -- STRIDE applied to database access, RLS bypass vectors, data layer trust boundaries
• application-security.md -- Service role management, schema exposure, security definer functions, views and RLS
• compliance-frameworks.md -- Database-specific GDPR, HIPAA, SOC2, PCI-DSS requirements and SQL implementations

Database Access Security

Patterns for securing database access from applications. For general OWASP Top 10 coverage and application-level security (XSS, CSRF, headers), see the application-security skill.

Supabase API Security

Service Role Key Management

The service_role key bypasses all RLS policies. It must never appear in client-side code.

// BAD: service_role key in browser-accessible code
const supabase = createClient(
  url,
  process.env.NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY!,
);

// GOOD: anon key for client-side, service_role only in server-side code
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!);

// GOOD: service_role only in server functions (API routes, server actions)
const adminClient = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!);

Anon Role Restrictions

The anon role should have minimal permissions. Never grant SELECT on sensitive tables to anon.

-- Restrict anon access to sensitive tables
REVOKE ALL ON sensitive_data FROM anon;

-- Allow anon read access only to public content
GRANT SELECT ON public_content TO anon;

Schema Exposure Controls

Only schemas listed in Supabase API settings are exposed via PostgREST. Security definer functions in exposed schemas can bypass RLS.

-- Create utility functions in a non-exposed schema
CREATE SCHEMA private;

CREATE OR REPLACE FUNCTION private.has_role(required_role text)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = (select auth.uid()) AND role = required_role
  );
$$;

-- Use in RLS policy (function bypasses RLS on user_roles table)
CREATE POLICY admin_access ON admin_data
FOR SELECT TO authenticated
USING (private.has_role('admin'));

Security definer functions should:

• Live in a non-exposed schema (not public)
• Set search_path = '' to prevent schema hijacking
• Be marked STABLE or IMMUTABLE when possible

Views and RLS

Views bypass RLS by default because they run as the view creator (typically postgres with bypass RLS).

-- BAD: View bypasses RLS (default behavior)
CREATE VIEW user_documents AS
SELECT * FROM documents;

-- GOOD: Postgres 15+ security_invoker forces RLS evaluation
CREATE VIEW user_documents
WITH (security_invoker = true) AS
SELECT * FROM documents;

Convex API Security

Public Function Exposure

All Convex functions are callable from the internet. Internal functions provide server-to-server isolation.

import { internalQuery, internalMutation } from './_generated/server';

// Internal function: only callable from other Convex functions
export const getUser = internalQuery({
  args: { userId: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query('users')
      .withIndex('by_external_id', (q) => q.eq('externalId', args.userId))
      .unique();
  },
});

External Service Authentication

For external services calling Convex (webhooks, cron jobs), verify a shared secret from environment variables.

import { httpAction } from './_generated/server';

export const webhook = httpAction(async (ctx, request) => {
  const secret = request.headers.get('x-webhook-secret');
  if (secret !== process.env.WEBHOOK_SECRET) {
    return new Response('Unauthorized', { status: 401 });
  }
  // Process webhook
});

Database Connection Security

Authorization Pattern: Row Ownership

The most common database access pattern is row ownership, where users can only access rows they own.

-- Standard ownership pattern
CREATE POLICY owner_select ON documents
FOR SELECT TO authenticated
USING ((select auth.uid()) = owner_id);

CREATE POLICY owner_insert ON documents
FOR INSERT TO authenticated
WITH CHECK ((select auth.uid()) = owner_id);

CREATE POLICY owner_update ON documents
FOR UPDATE TO authenticated
USING ((select auth.uid()) = owner_id)
WITH CHECK ((select auth.uid()) = owner_id);

CREATE POLICY owner_delete ON documents
FOR DELETE TO authenticated
USING ((select auth.uid()) = owner_id);

Security Audit Queries

-- Find functions in exposed schemas that are security definer
SELECT n.nspname, p.proname, p.prosecdef
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.prosecdef = true;

-- Check for tables granting access to anon
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'anon' AND table_schema = 'public';

Audit Log Implementation

Application-level logs can be bypassed. Database-level auditing (triggers or extensions) captures every change regardless of how it was initiated.

Trigger-Based Auditing (Postgres)

Create a generic trigger that captures INSERT, UPDATE, and DELETE operations:

-- Audit log table
CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  table_name text NOT NULL,
  action text NOT NULL,
  old_data jsonb,
  new_data jsonb,
  actor_id uuid,
  changed_at timestamptz DEFAULT now()
);

-- Generic trigger function
CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    INSERT INTO audit_log(table_name, action, old_data, actor_id)
    VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD), auth.uid());
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO audit_log(table_name, action, old_data, new_data, actor_id)
    VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), auth.uid());
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO audit_log(table_name, action, new_data, actor_id)
    VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW), auth.uid());
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to sensitive tables
CREATE TRIGGER audit_sensitive_data
AFTER INSERT OR UPDATE OR DELETE ON sensitive_data
FOR EACH ROW EXECUTE FUNCTION process_audit();

PGAudit Extension

For high-compliance environments (HIPAA, SOC2), use the pgaudit extension:

• Logs full SQL statements and their parameters
• Supports session-level and object-level auditing
• Can be scoped per database, per role, or globally

-- Enable pgaudit (requires shared_preload_libraries = 'pgaudit' in postgresql.conf)
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_statement_once = on;
ALTER SYSTEM SET pgaudit.log_level = 'log';

PGAudit Log Classes

Per-Database or Per-Role Scoping

-- Audit all writes on a specific database
ALTER DATABASE finance SET pgaudit.log = 'read, write';

-- Audit everything for a specific role
ALTER ROLE auditor SET pgaudit.log = 'all';

Log Integrity

Prevent attackers from deleting audit logs:

-- Restrict app user to INSERT only
GRANT INSERT ON audit_log TO app_user;
REVOKE UPDATE, DELETE ON audit_log FROM app_user;

-- Separate schema for audit isolation
CREATE SCHEMA audit;
ALTER TABLE audit_log SET SCHEMA audit;

Application-Level Audit Logging

For events not captured by database triggers:

interface AuditEvent {
  userId: string;
  action: string;
  resource: string;
  ip: string;
  userAgent: string;
  success: boolean;
  metadata?: Record<string, unknown>;
}

async function auditLog(event: AuditEvent) {
  await db.auditLog.create({
    data: {
      ...event,
      timestamp: new Date(),
    },
  });
}

// Log security-relevant events
await auditLog({
  userId: user.id,
  action: 'LOGIN',
  resource: 'auth',
  ip: req.ip,
  userAgent: req.headers['user-agent'] ?? '',
  success: true,
});

What to Audit

Database Compliance

Database-specific compliance requirements and implementation patterns. For general compliance framework overviews and application-level controls, see the application-security skill.

GDPR: Database Requirements

Right to Erasure Implementation

-- Cascade delete with audit trail
CREATE OR REPLACE FUNCTION delete_user_data(target_user_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Log the deletion request before executing
  INSERT INTO audit.deletion_log (user_id, requested_at)
  VALUES (target_user_id, now());

  -- Delete in dependency order
  DELETE FROM comments WHERE author_id = target_user_id;
  DELETE FROM posts WHERE author_id = target_user_id;
  DELETE FROM sessions WHERE user_id = target_user_id;
  DELETE FROM users WHERE id = target_user_id;
END;
$$;

Data Retention Automation

-- Automated cleanup of expired data
CREATE OR REPLACE FUNCTION cleanup_expired_data()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- Remove sessions older than retention period
  DELETE FROM sessions WHERE expires_at < now();

  -- Anonymize inactive user data past retention period
  UPDATE users
  SET email = 'anonymized-' || id || '@deleted.local',
      name = 'Deleted User',
      anonymized_at = now()
  WHERE last_active_at < now() - interval '3 years'
    AND anonymized_at IS NULL;
END;
$$;

Right to Portability

-- Export all user data as JSON
CREATE OR REPLACE FUNCTION export_user_data(target_user_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result jsonb;
BEGIN
  SELECT jsonb_build_object(
    'profile', (SELECT to_jsonb(u) FROM users u WHERE id = target_user_id),
    'posts', (SELECT jsonb_agg(to_jsonb(p)) FROM posts p WHERE author_id = target_user_id),
    'comments', (SELECT jsonb_agg(to_jsonb(c)) FROM comments c WHERE author_id = target_user_id)
  ) INTO result;

  INSERT INTO audit.data_exports (user_id, exported_at)
  VALUES (target_user_id, now());

  RETURN result;
END;
$$;

HIPAA: Database Requirements

PHI Access Controls

-- Separate schema for PHI
CREATE SCHEMA phi;

-- Strict role-based access
CREATE ROLE phi_reader;
CREATE ROLE phi_writer;

GRANT USAGE ON SCHEMA phi TO phi_reader, phi_writer;
GRANT SELECT ON ALL TABLES IN SCHEMA phi TO phi_reader;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA phi TO phi_writer;

-- RLS on PHI tables
ALTER TABLE phi.patient_records ENABLE ROW LEVEL SECURITY;

CREATE POLICY provider_access ON phi.patient_records
FOR SELECT TO phi_reader
USING (
  provider_id = (select auth.uid())
  OR EXISTS (
    SELECT 1 FROM phi.care_team
    WHERE patient_id = phi.patient_records.patient_id
    AND provider_id = (select auth.uid())
  )
);

PHI Audit Requirements

HIPAA requires logging all access to Protected Health Information:

-- PHI-specific audit trigger
CREATE OR REPLACE FUNCTION phi.audit_phi_access()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit.phi_access_log (
    table_name, record_id, action, actor_id, actor_role, accessed_at
  ) VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    (select auth.uid()),
    current_setting('request.jwt.claims', true)::jsonb ->> 'role',
    now()
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_patient_records
AFTER SELECT OR INSERT OR UPDATE OR DELETE ON phi.patient_records
FOR EACH ROW EXECUTE FUNCTION phi.audit_phi_access();

SOC2: Database Controls

Trust Service Criteria for Databases

Change Management Evidence

-- Track schema changes with PGAudit
ALTER SYSTEM SET pgaudit.log = 'ddl';

-- Version database migrations (use a migration tool)
-- Every schema change is a tracked migration with a timestamp

PCI-DSS: Database Requirements

Cardholder Data Protection

Never store raw cardholder data. Use tokenization via payment processors:

-- Store only tokenized payment references
CREATE TABLE payment_methods (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id),
  stripe_payment_method_id text NOT NULL,
  last_four text NOT NULL,
  card_brand text NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Enable RLS and audit logging
ALTER TABLE payment_methods ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_only ON payment_methods
FOR ALL TO authenticated
USING ((select auth.uid()) = user_id);

Cross-Framework Database Checklist

Convex Security

Convex functions are public by default. Every function must be explicitly secured using the ctx.auth object.

Identity Validation

Create a helper to validate the user and return their metadata:

import { type QueryCtx, type MutationCtx } from './_generated/server';

async function getAuthenticatedUser(ctx: QueryCtx | MutationCtx) {
  const identity = await ctx.auth.getUserIdentity();
  if (!identity) throw new Error('Unauthorized');
  return identity;
}

Every query and mutation handler must call this before accessing data:

import { query } from './_generated/server';
import { v } from 'convex/values';

export const getSecureData = query({
  args: { id: v.id('items') },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();
    if (!identity) throw new Error('Unauthenticated');

    const item = await ctx.db.get(args.id);
    if (!item || item.ownerId !== identity.subject) {
      throw new Error('Unauthorized access attempt logged.');
    }
    return item;
  },
});

Manual RLS in Functions

Since Convex does not have native SQL RLS, implement access control in function handlers:

import { mutation } from './_generated/server';
import { v } from 'convex/values';

export const updatePost = mutation({
  args: { id: v.id('posts'), content: v.string() },
  handler: async (ctx, args) => {
    const user = await getAuthenticatedUser(ctx);
    const post = await ctx.db.get(args.id);

    if (!post) throw new Error('Post not found');
    if (post.authorId !== user.subject) {
      throw new Error('You are not the author of this post.');
    }

    await ctx.db.patch(args.id, { content: args.content });
  },
});

Granular Functions

Split broad update functions into specific, purpose-built functions:

By splitting functions, different authorization rules apply to each specific action. For example, any user can update their display name, but only admins can update permissions.

Role-Based Access

Custom claims from JWTs are accessed directly on the UserIdentity object. The field name depends on your auth provider's JWT template. Nested fields use dot notation in bracket syntax.

export const adminAction = mutation({
  args: { targetUserId: v.string() },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();
    if (!identity) throw new Error('Unauthenticated');

    // Access custom claims directly (field name depends on auth provider)
    // Clerk example: configure "role" in JWT template Claims
    const role = identity.role as string | undefined;
    // Custom JWT example: nested fields use dot notation
    // const role = identity["properties.role"] as string | undefined;
    if (role !== 'admin') {
      throw new Error('Admin access required');
    }

    // Proceed with admin action
  },
});

Encryption and Compliance

Convex encrypts all data at rest and in transit. For high-compliance environments:

Security Checklist

• [ ] Every function calls ctx.auth.getUserIdentity()
• [ ] Ownership checked before data access or modification
• [ ] Functions are granular (not generic update-all)
• [ ] Role-based access for admin operations
• [ ] No function exposes data without authorization
• [ ] Error messages do not leak internal details

RLS Performance Optimization

Each RLS policy is essentially a hidden WHERE clause added to every query. If not optimized, it can turn an O(1) lookup into an O(N) scan.

Mandatory Indexing

Any column used in a USING or WITH CHECK clause MUST be indexed. Without indexes, RLS adds a sequential scan to every query, causing 100x+ slowdowns on large tables.

-- If policy is (select auth.uid()) = user_id, then user_id needs a B-Tree index
CREATE INDEX idx_sensitive_data_user_id ON sensitive_data(user_id);

-- For team-based access
CREATE INDEX idx_sensitive_data_team_id ON sensitive_data(team_id);
CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_team_members_team_id ON team_members(team_id);

Wrap auth Functions in SELECT

Supabase recommends wrapping auth.uid() and auth.jwt() in a subselect. This triggers an initPlan optimization that caches the result instead of calling the function per row.

-- BAD: auth.uid() called per row
CREATE POLICY user_access ON documents
FOR SELECT USING (auth.uid() = user_id);

-- GOOD: Wrapped in select, result cached via initPlan
CREATE POLICY user_access ON documents
FOR SELECT USING ((select auth.uid()) = user_id);

This optimization only works when the function result does not depend on row data.

Wrapping in Stable Functions

Postgres can cache the results of STABLE functions. Wrap complex subqueries in a function to avoid re-executing them for every row.

CREATE OR REPLACE FUNCTION check_membership(org_id uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM memberships
    WHERE organization_id = org_id AND user_id = (select auth.uid())
  );
$$ LANGUAGE sql STABLE;

CREATE POLICY member_access ON documents
FOR SELECT USING (check_membership(organization_id));

Without the STABLE marker, Postgres may re-execute the subquery for each row in the table.

Avoid Cross-Schema Subqueries

Keep RLS logic within the same schema to minimize planning overhead. Cross-schema joins in RLS policies add query planner complexity and may prevent optimizations.

Benchmarking with EXPLAIN

Always test RLS policies with real data volumes:

-- Run with EXPLAIN to check execution plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM my_table;

What to look for:

Separate Policies per Operation

Supabase recommends creating separate policies for SELECT, INSERT, UPDATE, and DELETE instead of using FOR ALL. An UPDATE requires a matching SELECT policy to function correctly.

-- Enable RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- Separate SELECT policy (team-based access)
CREATE POLICY team_select ON sensitive_data
FOR SELECT
TO authenticated
USING (
  team_id IN (
    SELECT team_id FROM team_members WHERE user_id = (select auth.uid())
  )
);

-- Separate INSERT policy
CREATE POLICY team_insert ON sensitive_data
FOR INSERT
TO authenticated
WITH CHECK (
  team_id IN (
    SELECT team_id FROM team_members WHERE user_id = (select auth.uid())
  )
);

Add Explicit Filters in Application Code

RLS policies act as implicit WHERE clauses, but always add explicit filters in application queries too. This helps Postgres build a better query plan.

-- Even though RLS filters by user_id, add it explicitly
SELECT * FROM documents WHERE user_id = (select auth.uid());

Performance Checklist

• [ ] All columns in RLS USING clauses are indexed
• [ ] auth.uid() and auth.jwt() wrapped in (select ...) subselects
• [ ] Complex subqueries are wrapped in STABLE functions
• [ ] Separate policies per operation (SELECT, INSERT, UPDATE, DELETE)
• [ ] RLS logic stays within the same schema
• [ ] EXPLAIN ANALYZE shows Index Scan (not Sequential Scan)
• [ ] Application queries include explicit filters matching RLS conditions
• [ ] Tested with production-scale data volumes
• [ ] service_role key is never used client-side

Data Layer Threat Modeling

Apply STRIDE specifically at data layer trust boundaries to identify database-level threats. For general application-level STRIDE coverage, see the application-security skill.

STRIDE at the Data Layer

S - Spoofing (Identity at DB Level)

• Threat: Attacker forges or reuses JWT to impersonate another user at the database level
• Data layer examples: Stolen JWT used with Supabase client, service_role key leaked to client
• Mitigations: Short JWT expiry, asymmetric signing (RS256/EdDSA), audience validation, never expose service_role to clients

T - Tampering (Data Integrity)

• Threat: Attacker modifies data through RLS bypass or unprotected mutation
• Data layer examples: Missing WITH CHECK on INSERT/UPDATE policies, Convex mutation without ownership check
• Mitigations: RLS WITH CHECK on all write operations, ownership validation in Convex handlers, database constraints

R - Repudiation (Audit Gaps)

• Threat: Data modifications occur without attribution
• Data layer examples: Direct SQL access without audit triggers, missing actor_id in audit logs
• Mitigations: Database-level audit triggers, PGAudit extension, immutable audit schema

I - Information Disclosure (Data Leakage)

• Threat: Unauthorized data access through policy gaps
• Data layer examples: Missing RLS on new tables, anon role with SELECT on sensitive tables, verbose Postgres error messages
• Mitigations: RLS on every public table, restrict anon access, generic error responses

D - Denial of Service (Query Performance)

• Threat: Malicious queries exploit unoptimized RLS policies
• Data layer examples: Sequential scans from missing indexes on RLS columns, complex cross-schema subqueries in policies
• Mitigations: Index all RLS columns, wrap auth functions in subselects, EXPLAIN ANALYZE testing

E - Elevation of Privilege (Permission Escalation)

• Threat: User gains access to data outside their authorization scope
• Data layer examples: Standing admin privileges, permissive RLS policies combined with OR, Convex function missing role check
• Mitigations: JIT access grants, restrictive policies, granular Convex functions with role validation

Data Layer Trust Boundaries

RLS Bypass Threat Identification

Common paths attackers use to bypass RLS:

-- Find tables in public schema without RLS enabled
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = false;

-- List all RLS policies and their expressions
SELECT schemaname, tablename, policyname, permissive, cmd, qual
FROM pg_policies
WHERE schemaname = 'public';

Risk Assessment for Data Layer

Zero-Trust Database Architecture

Core Principles

1. Never Trust, Always Verify: Even if a request comes from your internal web server, verify the end-user's identity at the DB level 2. Least Privilege: Grant only the permissions needed for the specific operation 3. Assume Breach: Design your DB assuming an attacker already has a valid JWT

Micro-Segmentation

Divide your database into logical segments with different access controls:

-- Create separate schemas
CREATE SCHEMA private;
CREATE SCHEMA audit;

-- Restrict app user access to audit schema
GRANT INSERT ON audit.audit_log TO app_user;
REVOKE UPDATE, DELETE ON audit.audit_log FROM app_user;

Identity Propagation

Pass the full end-user context (ID, role, organization) down to the database level:

Supabase/Postgres

-- Access user identity in RLS policies
SELECT auth.uid();                        -- Current user ID (from JWT sub claim)
SELECT auth.jwt();                        -- Full JWT claims as JSON
SELECT auth.jwt() ->> 'role';             -- Extract role from JWT claims
SELECT auth.jwt() -> 'app_metadata';      -- Access app metadata from JWT

Convex

// Access user identity in function handlers
const identity = await ctx.auth.getUserIdentity();
if (!identity) throw new Error('Unauthenticated');
const userId = identity.subject;

Connection Security

Just-in-Time (JIT) Access

Avoid standing privileges for administrative tasks. Implement time-bound access grants:

-- Grant temporary admin access (expires after task)
CREATE OR REPLACE FUNCTION grant_temp_admin(
  target_user_id uuid,
  duration_minutes integer DEFAULT 60
)
RETURNS void AS $$
BEGIN
  INSERT INTO temp_admin_grants (user_id, expires_at)
  VALUES (target_user_id, now() + (duration_minutes || ' minutes')::interval);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- RLS policy checks temp grants
CREATE POLICY admin_access ON admin_data
FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM temp_admin_grants
    WHERE user_id = auth.uid()
    AND expires_at > now()
  )
);

Key rules:

• No standing admin privileges
• All elevated access is time-bound
• Grants are logged in the audit trail
• Expired grants are cleaned up automatically

Implementation Checklist

• [ ] Schemas segmented (public, private, audit)
• [ ] Identity propagated to database level
• [ ] TLS 1.3 enforced on all connections
• [ ] Asymmetric JWT signing configured
• [ ] IP allowlisting for admin connections
• [ ] JIT access implemented for admin tasks
• [ ] All access patterns audited