08_data_export.sql

-- PostgreSQL Data Anonymization in AWS RDS
-- Data export techniques

-- Create a function to generate an anonymized SQL dump
-- Note: This requires the pg_dump utility and appropriate permissions
CREATE OR REPLACE FUNCTION anon.generate_anonymized_dump(
  output_file text,
  schema_name text DEFAULT 'public'
)
RETURNS text AS
$$
DECLARE
  cmd text;
  result text;
BEGIN
  -- Create a temporary view that combines all anonymized views
  EXECUTE format('
    CREATE OR REPLACE VIEW %I.anonymized_export AS
    
    -- Users data
    SELECT 
      ''users'' AS source_table,
      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 %I.users
    
    UNION ALL
    
    -- Patients data
    SELECT 
      ''patients'' AS source_table,
      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,
      anon.partial(ssn, 0, ''***-**-'', 4) AS partial_ssn,
      diagnosis_code,
      treatment_code,
      NULL AS notes
    FROM %I.patients
  ', schema_name, schema_name, schema_name);
  
  -- Build the pg_dump command
  cmd := format('pg_dump -t %I.anonymized_export -f %L', schema_name, output_file);
  
  -- Execute the command (this would work in a procedural language with shell access)
  -- In AWS RDS, you would need to use a different approach, such as:
  -- 1. Using a database link to export to another PostgreSQL instance
  -- 2. Using AWS Data Pipeline or AWS DMS
  -- 3. Using a client-side script that queries the anonymized views
  
  result := format('Command to execute: %s', cmd);
  
  -- Clean up
  EXECUTE format('DROP VIEW IF EXISTS %I.anonymized_export', schema_name);
  
  RETURN result;
END;
$$
LANGUAGE plpgsql;

-- Create a function to export anonymized data to a CSV file
-- Note: This requires the COPY command and appropriate permissions
CREATE OR REPLACE FUNCTION anon.export_to_csv(
  view_name text,
  output_file text,
  delimiter text DEFAULT ','
)
RETURNS text AS
$$
DECLARE
  cmd text;
  result text;
BEGIN
  -- Build the COPY command
  cmd := format('COPY %I TO %L WITH (FORMAT CSV, HEADER, DELIMITER %L)', view_name, output_file, delimiter);
  
  -- In AWS RDS, you would typically use the COPY command with S3:
  -- COPY view_name TO 's3://bucket-name/path/to/file.csv' 
  -- CREDENTIALS 'aws_iam_role=arn:aws:iam::account-id:role/role-name'
  -- CSV HEADER;
  
  result := format('Command to execute: %s', cmd);
  
  RETURN result;
END;
$$
LANGUAGE plpgsql;

-- Create a function to generate a SQL script with anonymized INSERT statements
CREATE OR REPLACE FUNCTION anon.generate_anonymized_inserts(
  table_name text,
  output_table text DEFAULT NULL,
  limit_rows int DEFAULT NULL
)
RETURNS text AS
$$
DECLARE
  target_table text;
  query text;
  result text := '';
  r record;
  col_names text := '';
  col_values text := '';
BEGIN
  -- Determine the target table name
  IF output_table IS NULL THEN
    target_table := table_name || '_anonymized';
  ELSE
    target_table := output_table;
  END IF;
  
  -- Build the query based on the table
  IF table_name = 'users' THEN
    query := '
      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_of_birth,
        anon.partial(ssn, 0, ''***-**-'', 4) AS ssn,
        anon.fake_address() AS address,
        anon.partial(phone, 0, ''***-***-'', 4) AS phone,
        created_at
      FROM users';
      
    IF limit_rows IS NOT NULL THEN
      query := query || format(' LIMIT %s', limit_rows);
    END IF;
    
    -- Generate the CREATE TABLE statement
    result := result || format('
CREATE TABLE IF NOT EXISTS %I (
  id INTEGER PRIMARY KEY,
  email TEXT,
  first_name TEXT,
  last_name TEXT,
  password_hash TEXT,
  date_of_birth DATE,
  ssn TEXT,
  address TEXT,
  phone TEXT,
  created_at TIMESTAMP
);

', target_table);
    
    -- Generate INSERT statements
    FOR r IN EXECUTE query LOOP
      -- Build column names and values
      col_names := 'id, email, first_name, last_name, password_hash, date_of_birth, ssn, address, phone, created_at';
      col_values := format('%s, %L, %L, %L, %L, %L, %L, %L, %L, %L', 
                          r.id, r.email, r.first_name, r.last_name, r.password_hash, 
                          r.date_of_birth, r.ssn, r.address, r.phone, r.created_at);
      
      -- Add the INSERT statement
      result := result || format('INSERT INTO %I (%s) VALUES (%s);
', target_table, col_names, col_values);
    END LOOP;
  
  ELSIF table_name = 'patients' THEN
    query := '
      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_of_birth,
        anon.partial(ssn, 0, ''***-**-'', 4) AS ssn,
        diagnosis_code,
        treatment_code
      FROM patients';
      
    IF limit_rows IS NOT NULL THEN
      query := query || format(' LIMIT %s', limit_rows);
    END IF;
    
    -- Generate the CREATE TABLE statement
    result := result || format('
CREATE TABLE IF NOT EXISTS %I (
  id INTEGER PRIMARY KEY,
  mrn TEXT,
  first_name TEXT,
  last_name TEXT,
  date_of_birth DATE,
  ssn TEXT,
  diagnosis_code TEXT,
  treatment_code TEXT
);

', target_table);
    
    -- Generate INSERT statements
    FOR r IN EXECUTE query LOOP
      -- Build column names and values
      col_names := 'id, mrn, first_name, last_name, date_of_birth, ssn, diagnosis_code, treatment_code';
      col_values := format('%s, %L, %L, %L, %L, %L, %L, %L', 
                          r.id, r.mrn, r.first_name, r.last_name, 
                          r.date_of_birth, r.ssn, r.diagnosis_code, r.treatment_code);
      
      -- Add the INSERT statement
      result := result || format('INSERT INTO %I (%s) VALUES (%s);
', target_table, col_names, col_values);
    END LOOP;
  
  ELSE
    RETURN format('Table %I is not supported for anonymized exports', table_name);
  END IF;
  
  RETURN result;
END;
$$
LANGUAGE plpgsql;

-- Example usage:
-- SELECT anon.generate_anonymized_inserts('users', 'users_anon', 10);