PostgreSQL Security

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_crl_file = ''
ssl_ciphers = 'HIGH:!aNULL:!MD5'
ssl_prefer_server_ciphers = on
ssl_ecdh_curve = 'prime256v1'
ssl_min_protocol_version = 'TLSv1.3'

# pg_hba.conf - Require SSL
hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::/0 scram-sha-256

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

-- Policy: Users see only their data
CREATE POLICY patient_data_isolation ON patient_data
    FOR ALL
    TO app_users
    USING (user_id = current_setting('app.current_user_id')::int);

-- Bypass for admins
CREATE POLICY admin_all ON patient_data
    FOR ALL
    TO admins
    USING (true);

-- Set user context
SET app.current_user_id = '123';

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_statement_once = off
pgaudit.log_level = log

-- HIPAA/SOC2: Log all access to PHI
CREATE TABLE audit.phi_access (
    id serial PRIMARY KEY,
    user_name text,
    query text,
    parameters text,
    accessed_at timestamp DEFAULT now()
);

-- Trigger for sensitive tables
CREATE OR REPLACE FUNCTION audit_phi_access()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit.phi_access (user_name, query)
    VALUES (current_user, TG_OP || ' on ' || TG_TABLE_NAME);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;