Lab 13: Capstone — Harden & Audit a Complete Database¶

-- Run this ENTIRE block to create the insecure baseline
CREATE SCHEMA IF NOT EXISTS insecure;

-- Table 1: No RLS, no encryption, no classification, no constraints
CREATE TABLE insecure.customers (
  id          SERIAL PRIMARY KEY,
  name        TEXT,          -- CONFIDENTIAL: nullable, no length limit
  email       TEXT,          -- CONFIDENTIAL: no format validation
  ssn         TEXT,          -- RESTRICTED: PII stored in plaintext!
  credit_card TEXT,          -- RESTRICTED: PCI violation — raw PAN!
  salary      NUMERIC,       -- CONFIDENTIAL: no protection
  password    TEXT           -- CRITICAL: passwords in cleartext!
);

-- Table 2: No audit trail, no referential integrity, no amount validation
CREATE TABLE insecure.transactions (
  id          SERIAL PRIMARY KEY,
  customer_id INT,           -- No FK constraint — orphan rows allowed
  amount      NUMERIC,       -- No CHECK — negative amounts allowed
  card_number TEXT           -- PCI violation: raw PAN in transactions
);

-- DANGER: grant everything to all users
GRANT ALL ON insecure.customers    TO PUBLIC;
GRANT ALL ON insecure.transactions TO PUBLIC;

-- Insert sample data exhibiting every vulnerability
INSERT INTO insecure.customers
  (name, email, ssn, credit_card, salary, password)
VALUES
  ('Alice Chen', 'alice@corp.com', '123-45-6789', '4111111111111111', 95000, 'alice123'),
  ('Bob Smith',  'bob@corp.com',   '234-56-7890', '5500000000000004', 72000, 'password');

INSERT INTO insecure.transactions
  (customer_id, amount, card_number)
VALUES
  (1,  149.99, '4111111111111111'),
  (2, -50.00,  '5500000000000004');  -- negative amount: no constraint stops this

CREATE TABLE IF NOT EXISTS public.capstone_findings (
  finding_id   SERIAL PRIMARY KEY,
  severity     TEXT NOT NULL
                 CHECK (severity IN ('CRITICAL','HIGH','MEDIUM','LOW')),
  category     TEXT NOT NULL,
  description  TEXT NOT NULL,
  remediation  TEXT NOT NULL,
  status       TEXT DEFAULT 'OPEN'
                 CHECK (status IN ('OPEN','REMEDIATED'))
);

INSERT INTO capstone_findings (severity, category, description, remediation) VALUES
  ('CRITICAL', 'Authentication',
   'Passwords stored in plaintext — full compromise on any SELECT',
   'Hash with pgcrypto digest() or crypt(); never store cleartext credentials'),
  ('CRITICAL', 'PCI-DSS',
   'Full PAN (credit card number) stored in plaintext — immediate PCI violation',
   'Encrypt with pgp_sym_encrypt(), store only last 4 digits plaintext'),
  ('CRITICAL', 'PII Protection',
   'SSN stored in plaintext — HIPAA/state privacy law violation',
   'Encrypt with pgp_sym_encrypt() using a strong symmetric key'),
  ('HIGH', 'Access Control',
   'GRANT ALL ON both tables TO PUBLIC — any DB user can read/write all data',
   'REVOKE ALL FROM PUBLIC; grant minimum required privileges to named roles'),
  ('HIGH', 'Data Integrity',
   'No FK constraint on transactions.customer_id — orphan transaction rows possible',
   'ADD FOREIGN KEY REFERENCES secure.customers(id) ON DELETE RESTRICT'),
  ('HIGH', 'Data Integrity',
   'Negative transaction amounts allowed — fraud vector',
   'ADD CHECK (amount > 0) constraint'),
  ('MEDIUM', 'Auditing',
   'No audit trail on customers or transactions — changes undetectable',
   'Add audit.capture_change() triggers to both tables'),
  ('MEDIUM', 'Data Quality',
   'name and email columns are nullable — data integrity risk',
   'ADD NOT NULL constraints; add CHECK for email format'),
  ('MEDIUM', 'Compliance',
   'No Row Level Security — all users see all rows',
   'Enable RLS; create policies restricting access by role or user context'),
  ('LOW', 'Classification',
   'No data classification metadata on sensitive columns',
   'Add COMMENT ON COLUMN with classification label (RESTRICTED, CONFIDENTIAL)');

-- Summary by severity
SELECT
  severity,
  COUNT(*) AS finding_count
FROM capstone_findings
GROUP BY severity
ORDER BY
  CASE severity
    WHEN 'CRITICAL' THEN 1
    WHEN 'HIGH'     THEN 2
    WHEN 'MEDIUM'   THEN 3
    WHEN 'LOW'      THEN 4
  END;

REVOKE ALL ON insecure.customers    FROM PUBLIC;
REVOKE ALL ON insecure.transactions FROM PUBLIC;
REVOKE CREATE ON SCHEMA insecure    FROM PUBLIC;

-- Verify: this query should return 0 rows
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'insecure'
  AND grantee = 'PUBLIC';

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS secure;

CREATE TABLE secure.customers (
  id                  SERIAL PRIMARY KEY,
  -- Required, validated fields
  name                TEXT NOT NULL,
  email               TEXT NOT NULL
                        CONSTRAINT ck_email
                        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  -- RESTRICTED: SSN encrypted at rest
  ssn_encrypted       BYTEA,
  -- RESTRICTED: full PAN encrypted; only last 4 digits stored plaintext (PCI-DSS)
  credit_card_enc     BYTEA,
  credit_card_last4   CHAR(4),
  -- CONFIDENTIAL: salary encrypted
  salary_encrypted    BYTEA,
  -- CRITICAL: passwords hashed — NEVER stored in cleartext
  password_hash       TEXT NOT NULL,
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- Migrate data from insecure baseline with encryption applied
INSERT INTO secure.customers
  (name, email,
   ssn_encrypted,
   credit_card_enc, credit_card_last4,
   salary_encrypted,
   password_hash)
SELECT
  name,
  email,
  pgp_sym_encrypt(ssn,          'CAP_ENC_KEY_2026'),
  pgp_sym_encrypt(credit_card,  'CAP_ENC_KEY_2026'),
  RIGHT(credit_card, 4),
  pgp_sym_encrypt(salary::TEXT, 'CAP_ENC_KEY_2026'),
  encode(digest(password, 'sha256'), 'hex')  -- SHA-256 hash (bcrypt preferred in production)
FROM insecure.customers;

-- Verify: sensitive data is encrypted, passwords are hashed
SELECT
  id,
  name,
  email,
  credit_card_last4,
  length(ssn_encrypted)  AS ssn_enc_bytes,
  length(credit_card_enc) AS cc_enc_bytes,
  LEFT(password_hash, 20) AS pw_hash_preview
FROM secure.customers;

CREATE TABLE secure.transactions (
  id          SERIAL PRIMARY KEY,
  customer_id INT           NOT NULL
                REFERENCES secure.customers(id) ON DELETE RESTRICT,
  amount      NUMERIC(10,2) NOT NULL CHECK (amount > 0),
  card_last4  CHAR(4),
  txn_time    TIMESTAMPTZ DEFAULT NOW()
);

-- Verify all constraints are in place
SELECT
  constraint_name,
  constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'secure'
  AND table_name   = 'transactions'
ORDER BY constraint_type;

ALTER TABLE secure.customers    ENABLE ROW LEVEL SECURITY;
ALTER TABLE secure.transactions ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own record, DBA role members see all
CREATE POLICY customer_self_service ON secure.customers
  FOR SELECT
  USING (
    name = current_user
    OR pg_has_role(current_user, 'role_dba', 'member')
  );

-- Verify RLS is enabled
SELECT
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'secure'
ORDER BY tablename;

CREATE TRIGGER capstone_audit_customers
  AFTER INSERT OR UPDATE OR DELETE ON secure.customers
  FOR EACH ROW EXECUTE FUNCTION audit.capture_change();

CREATE TRIGGER capstone_audit_transactions
  AFTER INSERT OR UPDATE OR DELETE ON secure.transactions
  FOR EACH ROW EXECUTE FUNCTION audit.capture_change();

-- Generate audit events to populate the log
INSERT INTO secure.transactions (customer_id, amount, card_last4)
VALUES (1, 89.99, '1111');

UPDATE secure.customers
SET email = 'alice.chen@corp.com'
WHERE name = 'Alice Chen';

-- Verify triggers and log entries
SELECT tgname, tgenabled
FROM pg_trigger
WHERE tgname LIKE 'capstone_%';

UPDATE capstone_findings
SET status = 'REMEDIATED'
WHERE finding_id IN (1, 2, 3, 4, 5, 6, 7);

-- Findings summary
SELECT
  status,
  COUNT(*) AS count
FROM capstone_findings
GROUP BY status;

CREATE OR REPLACE FUNCTION public.verify_capstone()
RETURNS TABLE(
  check_id    TEXT,
  description TEXT,
  result      TEXT,
  points      INT
) AS $$
BEGIN

  -- [1] pgcrypto extension installed
  RETURN QUERY SELECT
    '1'::TEXT,
    'pgcrypto extension installed'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM pg_extension WHERE extname = 'pgcrypto')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    5::INT;

  -- [2] PUBLIC privileges revoked from insecure schema tables
  RETURN QUERY SELECT
    '2'::TEXT,
    'PUBLIC privileges revoked from insecure tables'::TEXT,
    CASE WHEN NOT EXISTS(
      SELECT 1 FROM information_schema.role_table_grants
      WHERE table_schema = 'insecure' AND grantee = 'PUBLIC')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [3] SSN stored as encrypted BYTEA
  RETURN QUERY SELECT
    '3'::TEXT,
    'SSN stored as encrypted BYTEA (not plaintext TEXT)'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = 'secure'
        AND table_name   = 'customers'
        AND column_name  = 'ssn_encrypted'
        AND data_type    = 'bytea')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [4] Credit card encrypted (bytea) and last4 column exists
  RETURN QUERY SELECT
    '4'::TEXT,
    'Credit card encrypted, last4 plaintext only'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = 'secure'
        AND table_name   = 'customers'
        AND column_name  = 'credit_card_enc'
        AND data_type    = 'bytea')
      AND EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = 'secure'
        AND table_name   = 'customers'
        AND column_name  = 'credit_card_last4')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [5] Passwords hashed — no original plaintext values present
  RETURN QUERY SELECT
    '5'::TEXT,
    'Passwords hashed (no plaintext passwords stored)'::TEXT,
    CASE WHEN NOT EXISTS(
      SELECT 1 FROM secure.customers
      WHERE password_hash IN ('alice123','password','Password1','letmein'))
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [6] Foreign key constraint on transactions.customer_id
  RETURN QUERY SELECT
    '6'::TEXT,
    'Foreign key constraint on transactions.customer_id'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM information_schema.table_constraints
      WHERE table_schema     = 'secure'
        AND table_name       = 'transactions'
        AND constraint_type  = 'FOREIGN KEY')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    5::INT;

  -- [7] CHECK constraint prevents negative amounts
  RETURN QUERY SELECT
    '7'::TEXT,
    'CHECK constraint prevents negative transaction amounts'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM information_schema.table_constraints
      WHERE table_schema    = 'secure'
        AND table_name      = 'transactions'
        AND constraint_type = 'CHECK')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    5::INT;

  -- [8] RLS enabled on secure.customers
  RETURN QUERY SELECT
    '8'::TEXT,
    'Row Level Security enabled on secure.customers'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM pg_tables
      WHERE schemaname = 'secure'
        AND tablename  = 'customers'
        AND rowsecurity = TRUE)
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [9] Audit trigger exists on secure.customers
  RETURN QUERY SELECT
    '9'::TEXT,
    'Audit trigger on secure.customers'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM pg_trigger
      WHERE tgname = 'capstone_audit_customers')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    10::INT;

  -- [10] Audit log has entries from capstone tables
  RETURN QUERY SELECT
    '10'::TEXT,
    'Audit log contains events from capstone tables'::TEXT,
    CASE WHEN EXISTS(
      SELECT 1 FROM audit.event_log
      WHERE table_name = 'customers')
      THEN 'PASS' ELSE 'FAIL'
    END::TEXT,
    5::INT;

END;
$$ LANGUAGE plpgsql;

-- ── Run the verification ──────────────────────────────────────────────────────
SELECT
  check_id,
  description,
  result,
  points,
  CASE WHEN result = 'PASS' THEN points ELSE 0 END AS earned
FROM verify_capstone()
ORDER BY check_id::INT;

-- ── Final score ───────────────────────────────────────────────────────────────
SELECT
  SUM(points)                                                AS total_possible,
  SUM(CASE WHEN result = 'PASS' THEN points ELSE 0 END)      AS earned,
  SUM(CASE WHEN result = 'PASS' THEN 1 ELSE 0 END)::TEXT
    || '/' || COUNT(*)::TEXT                                 AS checks_passed
FROM verify_capstone();

-- SECTION 1: Extension inventory
SELECT
  'Extensions'                           AS section,
  extname                                AS item,
  extversion                             AS status_or_count
FROM pg_extension
WHERE extname NOT IN ('plpgsql')         -- hide built-ins

UNION ALL

-- SECTION 2: Role security audit
SELECT
  'Role Security'                        AS section,
  rolname                                AS item,
  CASE
    WHEN rolsuper    THEN 'SUPERUSER — review required'
    WHEN rolcreatedb THEN 'createdb flag set'
    ELSE 'ok'
  END                                    AS status_or_count
FROM pg_roles
WHERE rolcanlogin = TRUE
  AND rolname NOT LIKE 'pg_%'

UNION ALL

-- SECTION 3: RLS status for all user tables
SELECT
  'RLS Status'                           AS section,
  schemaname || '.' || tablename         AS item,
  CASE WHEN rowsecurity THEN 'RLS ON' ELSE 'RLS OFF' END AS status_or_count
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema')

UNION ALL

-- SECTION 4: Audit log summary by table and operation
SELECT
  'Audit Log Summary'                    AS section,
  table_name || ' — ' || operation       AS item,
  COUNT(*)::TEXT                         AS status_or_count
FROM audit.event_log
GROUP BY table_name, operation

UNION ALL

-- SECTION 5: Compliance control implementation rate
SELECT
  'Compliance Matrix'                    AS section,
  framework                              AS item,
  SUM(CASE WHEN status='IMPLEMENTED' THEN 1 ELSE 0 END)::TEXT
    || '/' || COUNT(*)::TEXT
    || ' implemented'                    AS status_or_count
FROM public.compliance_matrix
GROUP BY framework

ORDER BY section, item;