Chapter 33: Database Security — Auth, Encryption, Injection

SQL Injection — The #1 Database Attack

// VULNERABLE (string concatenation):
query = "SELECT * FROM users WHERE name = '" + user_input + "'";
// If user_input = "'; DROP TABLE users; --"
// → SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
// YOUR TABLE IS GONE.

// SAFE (parameterized query):
query = "SELECT * FROM users WHERE name = $1";
params = [user_input];  // treated as DATA, never as SQL

Authentication & Authorization

-- Create roles with minimal privileges
CREATE ROLE app_readonly LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

CREATE ROLE app_readwrite LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- Never let applications connect as superuser!

Encryption

• In transit: TLS/SSL for client-server connections (always enable)
• At rest: filesystem encryption (LUKS, dm-crypt) or cloud-managed encryption
• Column-level: pgcrypto extension for sensitive fields

Row-Level Security (RLS)

-- Each user can only see their own data
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_docs ON documents
  USING (owner_id = current_user_id());
-- Now SELECT * FROM documents only returns rows owned by current user

Security Checklist

• ☐ Parameterized queries everywhere (no SQL injection)
• ☐ TLS enabled for all connections
• ☐ Least-privilege roles (no superuser for apps)
• ☐ Network access restricted (firewall, pg_hba.conf)
• ☐ Passwords hashed (scram-sha-256 in PostgreSQL)
• ☐ Audit logging enabled
• ☐ Backups encrypted
• ☐ Regular security updates applied