Certificate

# ===== Why certificates belong in an SQLi conversation =====
# A perfectly parameterised app is still at risk if:
# - The DB is reachable over plaintext on a hostile network
# - Backups travel over HTTP and leak via a misconfigured S3 bucket
# - A leaked CA mints a 'valid' cert for your DB host and an attacker MITMs creds
# Certificate hygiene closes those paths.

# ===== 1) Require TLS to the database =====
# PostgreSQL pg_hba.conf
# hostssl shop  app_user  10.0.0.0/8  scram-sha-256 clientcert=verify-full
#
# Disables non-TLS connections from app subnets AND requires a client cert.

# MySQL my.cnf
# [mysqld]
# require_secure_transport = ON
# tls_version = TLSv1.2,TLSv1.3
# ssl_ca = /etc/mysql/ca.crt
# ssl_cert = /etc/mysql/server.crt
# ssl_key = /etc/mysql/server.key
#
# CREATE USER 'app'@'%' REQUIRE SSL;

# ===== 2) Use mTLS for service-to-DB auth =====
# The app holds a client certificate signed by your internal CA. Postgres
# checks the cert's CN matches the database role.
#
# pg_hba.conf
# hostssl shop  app_user  10.0.0.0/8  cert clientcert=verify-full
#
# psql or driver:
# DATABASE_URL='postgres://app_user@db.internal:5432/shop?sslmode=verify-full&sslrootcert=/etc/ssl/ca.crt&sslcert=/etc/ssl/app.crt&sslkey=/etc/ssl/app.key'

# ===== 3) Use a managed CA / cert rotation =====
# Tools: cert-manager (k8s), AWS RDS Certificate Authority (rotated yearly),
# step-ca, smallstep, HashiCorp Vault PKI engine.
# Rotate root CA on a known schedule; trust BOTH old + new during overlap.

# ===== 4) Encrypt backups in transit and at rest =====
# pg_dump shop | gpg --symmetric --cipher-algo AES256 \
#               | aws s3 cp - s3://backups-prod/db/$(date +%F).sql.gpg
# Bucket: enforce TLS-only access (aws:SecureTransport condition), default
# SSE-KMS encryption, MFA delete, and Object Lock for ransomware resistance.

# ===== 5) Certificate Transparency monitoring =====
# CT logs every cert issued for your domains. Subscribe to alerts so you find
# out if a rogue cert is minted for db.example.com (compromised CA, social
# engineering at a CA).
# Free tools: Cert Spotter, crt.sh email alerts, Cloudflare CT monitoring.

# ===== 6) Restrict DB ports to private subnets =====
# Even with TLS, the DB should not be reachable on the public internet.
# AWS RDS: 'Publicly accessible: No' + a security group that only allows
# the app tier's SG on the DB port.

# ===== 7) Least-privilege DB roles (defence in depth for SQLi) =====
# A successful injection against a least-privilege role cannot do much.
# CREATE ROLE app_user LOGIN PASSWORD 'strong';
# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
# DO NOT GRANT: CREATE, ALTER, DROP, TRUNCATE, COPY, EXECUTE on sensitive functions,
# BYPASSRLS, or pg_user / pg_authid SELECT.

# ===== 8) Pinning / strict TLS on app side =====
# Postgres driver: sslmode=verify-full (NOT prefer / require — those silently
# downgrade if the cert is wrong)
# Node 'pg' library:
#   const pool = new Pool({ ssl: { ca: caCert, rejectUnauthorized: true } });

# ===== 9) Rotate compromised certs FAST =====
# Have a runbook for 'CA compromised' that:
# - Revokes affected client certs
# - Issues new ones from the alternate CA
# - Updates app deploys
# - Validates with end-to-end smoke tests
# Practice it once a quarter; do not learn it during an incident.

# ===== 10) Decision tree =====
# - DB on the public internet?           -> move to a private subnet TODAY
# - Plaintext DB connections?            -> turn on require_secure_transport
# - Static creds in env vars?            -> rotate via Vault dynamic creds
# - Backups unencrypted in a bucket?     -> SSE-KMS + bucket TLS-only policy
# - No CT monitoring?                    -> 5 minutes to subscribe; free

-- /certificate/sqli