02_basic_functions.sql

-- PostgreSQL Data Anonymization in AWS RDS
-- Basic anonymization functions using pgcrypto

-- Create a function to hash strings (one-way anonymization)
CREATE OR REPLACE FUNCTION anon.hash_string(text) 
RETURNS TEXT AS 
$$
  SELECT encode(digest($1, 'sha256'), 'hex');
$$ 
LANGUAGE SQL IMMUTABLE;

-- Create a function to partially mask email addresses
CREATE OR REPLACE FUNCTION anon.partial_email(email text)
RETURNS text AS
$$
BEGIN
  IF email IS NULL THEN
    RETURN NULL;
  END IF;
  
  IF position('@' in email) = 0 THEN
    RETURN substring(email, 1, 2) || '******';
  END IF;
  
  RETURN substring(email, 1, 2) || '******' || substring(email from position('@' in email));
END;
$$ 
LANGUAGE plpgsql IMMUTABLE;

-- Create a function to partially mask any string
CREATE OR REPLACE FUNCTION anon.partial(text_val text, prefix_len int, mask text, suffix_len int)
RETURNS text AS
$$
BEGIN
  IF text_val IS NULL THEN
    RETURN NULL;
  END IF;
  
  RETURN 
    substring(text_val, 1, prefix_len) || 
    mask || 
    substring(text_val, length(text_val) - suffix_len + 1, suffix_len);
END;
$$ 
LANGUAGE plpgsql IMMUTABLE;

-- Create a function to generate random integers in a range
CREATE OR REPLACE FUNCTION anon.random_int(min_val int, max_val int)
RETURNS int AS
$$
BEGIN
  RETURN floor(random() * (max_val - min_val + 1) + min_val)::int;
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate random strings
CREATE OR REPLACE FUNCTION anon.random_string(length int)
RETURNS text AS
$$
DECLARE
  chars text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  result text := '';
  i int := 0;
BEGIN
  FOR i IN 1..length LOOP
    result := result || substring(chars, anon.random_int(1, length(chars)), 1);
  END LOOP;
  RETURN result;
END;
$$
LANGUAGE plpgsql VOLATILE;