05_masked_views.sql

-- PostgreSQL Data Anonymization in AWS RDS
-- Example of creating masked views for different scenarios

-- Create a sample users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  password TEXT NOT NULL,
  date_of_birth DATE,
  ssn TEXT,
  address TEXT,
  phone TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data if the table is empty
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM users LIMIT 1) THEN
    INSERT INTO users (email, first_name, last_name, password, date_of_birth, ssn, address, phone)
    VALUES 
      ('john.doe@example.com', 'John', 'Doe', 'secret123', '1980-01-15', '123-45-6789', '123 Main St, Anytown, USA', '555-123-4567'),
      ('jane.smith@example.com', 'Jane', 'Smith', 'password456', '1992-07-22', '987-65-4321', '456 Oak Ave, Somewhere, USA', '555-987-6543'),
      ('bob.johnson@example.com', 'Bob', 'Johnson', 'letmein789', '1975-03-30', '456-78-9012', '789 Pine Rd, Nowhere, USA', '555-456-7890'),
      ('alice.williams@example.com', 'Alice', 'Williams', 'secure321', '1988-11-12', '321-54-9876', '321 Elm St, Anyplace, USA', '555-321-9876'),
      ('charlie.brown@example.com', 'Charlie', 'Brown', 'charlie123', '1995-05-05', '654-32-1098', '654 Cedar Ave, Somewhere, USA', '555-654-3210');
  END IF;
END $$;

-- Create a fully masked view for general use
CREATE OR REPLACE VIEW masked_users AS 
SELECT 
  id, 
  anon.partial_email(email) AS email, 
  anon.fake_first_name() AS first_name, 
  anon.fake_last_name() AS last_name, 
  anon.hash_string(password) AS password_hash,
  date_part('year', date_of_birth) AS birth_year,
  anon.partial(ssn, 0, '***-**-', 4) AS partial_ssn,
  anon.fake_address() AS address,
  anon.partial(phone, 0, '***-***-', 4) AS phone,
  created_at
FROM users;

-- Create a pseudonymized view for maintaining data relationships
CREATE OR REPLACE VIEW pseudonymized_users AS 
SELECT 
  id, 
  anon.pseudo_email(email) AS email, 
  anon.pseudo_name(first_name || ' ' || last_name) AS full_name,
  anon.hash_string(password) AS password_hash,
  date_part('year', date_of_birth) AS birth_year,
  anon.partial(ssn, 0, '***-**-', 4) AS partial_ssn,
  anon.pseudo_address(address) AS address,
  anon.partial(phone, 0, '***-***-', 4) AS phone,
  created_at
FROM users;

-- Create a view with minimal anonymization for analysts
CREATE OR REPLACE VIEW analyst_users AS 
SELECT 
  id, 
  anon.partial_email(email) AS email, 
  first_name,
  last_name,
  NULL AS password,
  date_of_birth,
  NULL AS ssn,
  anon.partial(address, 0, 'REDACTED ADDRESS', 0) AS address,
  anon.partial(phone, 0, '***-***-', 4) AS phone,
  created_at
FROM users;

-- Create a view with full anonymization for developers
CREATE OR REPLACE VIEW developer_users AS 
SELECT 
  id, 
  anon.partial_email(email) AS email, 
  anon.fake_first_name() AS first_name, 
  anon.fake_last_name() AS last_name, 
  anon.hash_string(password) AS password_hash,
  date_part('year', date_of_birth) AS birth_year,
  NULL AS ssn,
  anon.fake_address() AS address,
  anon.fake_phone() AS phone,
  created_at
FROM users;

-- Create a materialized view for better performance
CREATE MATERIALIZED VIEW IF NOT EXISTS masked_users_mat AS 
SELECT * FROM masked_users;

-- Create a sample patients table for healthcare example
CREATE TABLE IF NOT EXISTS patients (
  id SERIAL PRIMARY KEY,
  mrn TEXT NOT NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  date_of_birth DATE,
  ssn TEXT,
  diagnosis_code TEXT,
  treatment_code TEXT,
  notes TEXT
);

-- Insert sample data if the table is empty
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM patients LIMIT 1) THEN
    INSERT INTO patients (mrn, first_name, last_name, date_of_birth, ssn, diagnosis_code, treatment_code, notes)
    VALUES 
      ('MRN12345', 'John', 'Doe', '1980-01-15', '123-45-6789', 'D123', 'T456', 'Patient has hypertension. Prescribed medication.'),
      ('MRN67890', 'Jane', 'Smith', '1992-07-22', '987-65-4321', 'D234', 'T567', 'Patient has type 2 diabetes. Diet and exercise recommended.'),
      ('MRN24680', 'Bob', 'Johnson', '1975-03-30', '456-78-9012', 'D345', 'T678', 'Patient has asthma. Inhaler prescribed.'),
      ('MRN13579', 'Alice', 'Williams', '1988-11-12', '321-54-9876', 'D456', 'T789', 'Patient has allergies. Antihistamines recommended.'),
      ('MRN97531', 'Charlie', 'Brown', '1995-05-05', '654-32-1098', 'D567', 'T890', 'Patient has migraine. Pain management discussed.');
  END IF;
END $$;

-- Create an anonymized view for researchers
CREATE OR REPLACE VIEW research_patients AS 
SELECT 
  id,
  'MRN' || substring(anon.hash_string(mrn), 1, 8) AS mrn,
  anon.fake_first_name() AS first_name, 
  anon.fake_last_name() AS last_name, 
  date_part('year', date_of_birth) AS birth_year,
  NULL AS ssn,
  diagnosis_code,
  treatment_code,
  regexp_replace(
    regexp_replace(notes, first_name, 'PATIENT_FIRSTNAME'), 
    last_name, 'PATIENT_LASTNAME'
  ) AS anonymized_notes
FROM patients;