Database Layer Security (Postgres)

The Risk

Without indexes, every query scans the entire table row by row. On a table with 10K+ rows, this makes queries slow enough that an attacker can tie up your database connections with a few concurrent requests. Indexes turn full scans into near-instant lookups.

The Solution

Create indexes on columns that you frequently filter, sort, or group by. Think of an index like a book's table of contents - instead of reading every page to find a topic, you jump straight to it. This makes queries dramatically faster and harder to abuse, because even a malicious query resolves quickly instead of tying up the database.

The Fix

-- Add indexes on columns used in WHERE, GROUP BY, ORDER BY, JOIN
CREATE INDEX idx_col ON table_name(column_name);

-- For composite queries
CREATE INDEX idx_multi ON table_name(col1, col2);

The Risk

A single runaway query (or a deliberately crafted one) can consume 100% CPU for minutes, blocking all other queries. Without a timeout, your database becomes unresponsive until you manually kill the query. This is an easy denial-of-service vector.

The Solution

Set a maximum time limit on every database user role. 15 seconds is a good starting point for read-only analytics APIs - most legitimate queries finish in under 5 seconds. If any query takes longer than that, the database automatically kills it. This prevents a single slow or malicious query from locking up your entire database and affecting all other users. Also set the timeout at the application level (e.g. PG_STATEMENT_TIMEOUT_MS environment variable) so it applies to every connection in the pool.

The Fix

-- Set on every role that connects
ALTER ROLE myuser SET statement_timeout = '15s';

The Risk

If your application connects as the database owner (the postgres superuser), a SQL injection vulnerability gives the attacker full control: drop tables, read password hashes, modify data. A restricted read-only role limits the damage - even a successful injection can only read data from allowed tables.

The Solution

Create a separate database user that can only read data (SELECT) from specific tables you choose. Connect your application using this restricted user instead of the all-powerful admin account. Also block access to system tables that contain password hashes and configuration. Even if an attacker finds a way to run SQL, they can only read - not delete, modify, or access sensitive system data.

The Fix

CREATE ROLE app_readonly WITH LOGIN PASSWORD '...';
GRANT SELECT ON specific_table TO app_readonly;
ALTER ROLE app_readonly SET default_transaction_read_only = on;

-- Revoke access to system schemas
REVOKE ALL ON SCHEMA pg_catalog FROM app_readonly;
REVOKE ALL ON SCHEMA information_schema FROM app_readonly;

The Risk

Supabase exposes a PostgREST API publicly. Your anon key is visible in frontend code. Without Row Level Security (RLS), anyone with that key can INSERT, UPDATE, and DELETE any row in any table. The anon key is not a secret - RLS is your real access control. But enabling RLS alone is not enough - a policy like "Users can update their own profile" with USING (auth.uid() = user_id) applies to ALL columns. If the table has sensitive columns (is_admin, status, subscription_tier, limits), the user can update those on their own row. One API call = privilege escalation.

The Solution

Enable Row Level Security on every table and create policies that define who can do what. For read-only data, allow SELECT only. For user-writable tables, be extremely careful about UPDATE policies - they grant write access to every column on matching rows. Keep sensitive columns (roles, subscription status, permissions, usage limits) in separate tables that users can never write to directly. Billing webhooks and server-side logic should write to those tables via service_role. If you cannot restructure the schema, add BEFORE UPDATE triggers to reject changes to protected columns. If tables are backend-only, revoke all access from the anonymous role entirely.

The Fix

ALTER TABLE mytable ENABLE ROW LEVEL SECURITY;
CREATE POLICY "read_only" ON mytable FOR SELECT USING (true);

-- If backend-only (no frontend needs anon access):
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anon;

-- DANGER: This policy lets users update ANY column on their row
-- including is_admin, role, subscription_tier, limits!
CREATE POLICY "update_own" ON profiles
  FOR UPDATE USING (auth.uid() = user_id);

-- FIX: Protect sensitive columns with a trigger
CREATE OR REPLACE FUNCTION protect_sensitive_columns()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status != OLD.status OR NEW.limits != OLD.limits THEN
    RAISE EXCEPTION 'Cannot modify protected columns';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER guard_sensitive
  BEFORE UPDATE ON app_users
  FOR EACH ROW EXECUTE FUNCTION protect_sensitive_columns();

The Risk

Without a properly configured connection pool, your app creates a new database connection for every request (slow) or runs out of connections under load (crash). Cold-start connections add latency. Without an acquire timeout, requests queue forever during connection exhaustion.

The Solution

Use a connection pool that keeps a few database connections ready at all times (pre-warmed) so requests don't wait for new connections to be created. Set a maximum number of connections so your database doesn't get overwhelmed, and set a timeout so requests fail quickly rather than hanging forever if all connections are busy.

The Fix

# asyncpg - pre-warm and configure limits
pool = await asyncpg.create_pool(
    dsn=DATABASE_URL,
    min_size=3,       # pre-warmed connections
    max_size=6,       # upper limit
    command_timeout=15,
    init=init_conn    # set read-only + statement timeout
)

The Risk

Postgres functions marked SECURITY DEFINER execute with the function owner's privileges, completely bypassing Row Level Security. If such a function accepts user-supplied identity parameters (email, user_id, org_id), any authenticated user can impersonate anyone else. For example, a function increment_usage(p_email text, ...) SECURITY DEFINER lets any user insert or update usage records for ANY email - complete identity spoofing that RLS cannot prevent.

The Solution

Audit all SECURITY DEFINER functions in your database. Custom functions should almost never need SECURITY DEFINER - use the default SECURITY INVOKER instead. If a function genuinely needs elevated privileges, never accept user-supplied identity parameters. Pull identity from the JWT using auth.email() or auth.uid() inside the function body. After fixing a function signature, remember that Postgres treats different parameter counts as separate overloads - you must explicitly DROP the old vulnerable version.

The Fix

-- Find all SECURITY DEFINER functions
SELECT n.nspname, p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prosecdef = true
  AND n.nspname NOT IN ('pg_catalog','information_schema');

-- BAD: accepts user-supplied email (spoofable)
CREATE FUNCTION increment_usage(p_email text, p_app text)
  SECURITY DEFINER AS $$ ...
  INSERT INTO usage (email, ...) VALUES (p_email, ...) $$;

-- GOOD: pulls email from JWT (unforgeable)
CREATE FUNCTION increment_usage(p_app text)
  SECURITY DEFINER AS $$ ...
  INSERT INTO usage (email, ...) VALUES (auth.email(), ...) $$;

-- After fixing: DROP the old overload explicitly
DROP FUNCTION increment_usage(text, text, text, text, numeric);

The Risk

Supabase defaults grant the authenticated role full CRUD permissions (INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER, REFERENCES) on every table - regardless of your RLS policies. This means if RLS is accidentally disabled on any table, or a permissive policy is added carelessly, authenticated users instantly have full write access. RLS is one layer. Grants are another. You need both locked down.

The Solution

After setting up RLS policies, audit and tighten the underlying table grants so the authenticated role only has the specific permissions that match your actual RLS policies. Read-only data tables (reference data, public datasets, lookup tables) should have SELECT-only grants. User-writable tables should have only the operations their RLS policies allow (e.g., SELECT + INSERT + UPDATE for a files table, not DELETE + TRUNCATE). This is defense-in-depth - even without RLS, damage is limited to what the grants allow.

The Fix

-- Audit current grants
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND grantee IN ('anon', 'authenticated')
ORDER BY table_name;

-- Lock down read-only data tables
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER, REFERENCES
  ON my_readonly_table FROM authenticated;

-- Lock down user-writable tables (keep only needed ops)
REVOKE TRUNCATE, TRIGGER, REFERENCES
  ON user_files FROM authenticated;

-- Lock down tables users should never write to
REVOKE UPDATE, DELETE, TRUNCATE, TRIGGER, REFERENCES
  ON app_users FROM authenticated;