Database Security

-- Never use superuser for application connections
-- Create a dedicated user with minimal permissions
CREATE USER app_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

-- Read-write user (no DDL)
CREATE USER app_readwrite WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- Admin user (separate from application)
CREATE USER db_admin WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE mydb TO db_admin;

-- Create roles with specific permissions
CREATE ROLE read_only;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

CREATE ROLE read_write;
GRANT read_only TO read_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

CREATE ROLE db_maintain;
GRANT read_write TO db_maintain;
GRANT CREATE ON SCHEMA public TO db_maintain;

-- Assign users to roles
GRANT read_only TO service_analytics;
GRANT read_write TO service_api;
GRANT db_maintain TO deploy_user;

-- Revoke default public permissions
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Enable RLS on a table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Users can only see their own orders
CREATE POLICY user_orders ON orders
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::int);

-- Admin users can see all orders
CREATE POLICY admin_orders ON orders
    FOR ALL
    USING (current_setting('app.user_role') = 'admin');

-- Application sets context per request
SET app.current_user_id = '42';
SET app.user_role = 'user';

-- This query now only returns orders for user 42
SELECT * FROM orders;  -- filtered automatically

-- Install pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive columns
CREATE TABLE customer_data (
    id SERIAL PRIMARY KEY,
    name TEXT,
    ssn_encrypted BYTEA,
    credit_card_encrypted BYTEA
);

-- Insert encrypted data
INSERT INTO customer_data (name, ssn_encrypted)
VALUES ('Alice', pgp_sym_encrypt('123-45-6789', 'encryption_key'));

-- Query with decryption
SELECT name, pgp_sym_decrypt(ssn_encrypted, 'encryption_key') AS ssn
FROM customer_data;

-- Create index on encrypted column (for equality searches)
CREATE INDEX idx_customer_ssn ON customer_data (md5(pgp_sym_decrypt(ssn_encrypted, 'encryption_key')));

# 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'

# Require SSL connections (reject non-SSL)
# pg_hba.conf:
hostssl all all 0.0.0.0/0 scram-sha-256

# Disable SSL for local connections only (optional)
host all all 127.0.0.1/32 trust

-- Enable connection logging
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

-- Log DDL statements
ALTER SYSTEM SET log_statement = 'ddl';

-- Log slow queries (also captures the query text)
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- 1 second

-- Log role changes
ALTER SYSTEM SET log_statement = 'all';  -- WARNING: very verbose

-- Query audit log for suspicious activity
SELECT
    pid,
    usename,
    client_addr,
    query,
    query_start
FROM pg_stat_activity
WHERE query_start > NOW() - INTERVAL '1 hour'
  AND query LIKE '%DROP%'
   OR query LIKE '%DELETE%'
   OR query LIKE '%TRUNCATE%'
ORDER BY query_start DESC;