BEGIN;
--
-- D A T A
--
CREATE TABLE stock (
  id INT,
  amount INT
);
INSERT INTO stock VALUES
(1,1000),
(2,400);
CREATE TABLE employee (
  id INT PRIMARY KEY,
  firstname TEXT,
  lastname TEXT,
  phone TEXT UNIQUE,
  ssn TEXT UNIQUE,
  iban TEXT UNIQUE,
  linkedin_url TEXT
);
INSERT INTO employee
VALUES
(1,'Sarah','Connor','0609110911','153-473-999','FI9562411724264125',NULL),
(2,'Kyle', 'Reese', '0230366642','573-731-129','GB32BARC20039593595589',NULL)
;
CREATE TABLE call_history (
  id INT,
  fk_employee_phone TEXT REFERENCES employee(phone) DEFERRABLE,
  call_start TIMESTAMP,
  call_stop TIMESTAMP
);
INSERT INTO call_history
VALUES
(464,'0609110911','2020-05-24 08:11:30', '2020-05-24 08:11:43'),
(465,'0609110911','2020-05-25 15:58:23', '2020-05-24 16:01:11');
INSERT INTO call_history
VALUES
(464,'0609110911','2020-05-24 08:11:30', '2020-05-24 08:11:43'),
(465,'0609110911','2020-05-25 15:58:23', '2020-05-24 16:01:11');
CREATE TABLE profile (
  id INT,
  customer_id INT REFERENCES employee(id),
  personalDataA TEXT,
  personalDataB TEXT,
  personalDataC TEXT,
  personalDataD TEXT,
  personalDataE TEXT,
  personalDataF TEXT,
  personalDataG TEXT,
  personalDataH TEXT
);
INSERT INTO profile
SELECT i, (i % 2) + 1,'personal data A '|| i, 'personal data B '|| i, 'personal data C '|| i, 'personal data D '|| i,'personal data E '|| i, 'personal data F '|| i, 'personal data G '|| i, 'personal data H '|| i
FROM GENERATE_SERIES(1, 50000) i;
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
NOTICE:  installing required extension "tsm_system_rows"
NOTICE:  installing required extension "pgcrypto"
--
-- R U L E S
--
SECURITY LABEL FOR anon ON COLUMN employee.firstname
IS 'MASKED WITH VALUE NULL';
SECURITY LABEL FOR anon ON COLUMN employee.lastname
IS 'MASKED WITH FUNCTION anon.fake_last_name()';
-- The FK must be masked with the same function on both ends
SECURITY LABEL FOR anon ON COLUMN employee.phone
IS 'MASKED WITH FUNCTION md5(phone)';
SECURITY LABEL FOR anon ON COLUMN call_history.fk_employee_phone
IS 'MASKED WITH FUNCTION md5(fk_employee_phone)';
-- This is designed to break the UNIQUE constraint
SECURITY LABEL FOR anon ON COLUMN employee.ssn
IS 'MASKED WITH FUNCTION anon.partial(ssn,0,$$XXX-XXX-XX$$,1)';
SECURITY LABEL FOR anon ON COLUMN employee.iban
IS 'MASKED WITH FUNCTION anon.random_string(18)';
-- Issue #181
SECURITY LABEL FOR anon ON COLUMN employee.linkedin_url
IS 'MASKED WITH VALUE ''https://example.com/path'' ';
-- Issue #185 : improve perfs
SECURITY LABEL FOR anon ON COLUMN profile.personalDataA
IS 'MASKED WITH VALUE ''xxxA'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataB
IS 'MASKED WITH VALUE ''xxxB'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataC
IS 'MASKED WITH VALUE ''xxxC'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataD
IS 'MASKED WITH VALUE ''xxxD'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataE
IS 'MASKED WITH VALUE ''xxxE'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataF
IS 'MASKED WITH VALUE ''xxxF'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataG
IS 'MASKED WITH VALUE ''xxxG'' ';
SECURITY LABEL FOR anon ON COLUMN profile.personalDataH
IS 'MASKED WITH VALUE ''xxxH'' ';
--
-- T E S T S
--
-- Should return a NOTICE but anonymize data anyway
SELECT anon.anonymize_column('employee','lastname');
NOTICE:  The faking data is not present.
HINT:  You probably need to run 'SELECT anon.init()' 
NOTICE:  The anon extension is not initialized.
HINT:  Use 'SELECT anon.init()' before running this function
 anonymize_column 
------------------
 t
(1 row)

SELECT count(*)=0 FROM employee WHERE lastname= 'Connor';
 ?column? 
----------
 t
(1 row)

SELECT anon.init();
 init 
------
 t
(1 row)

SAVEPOINT after_init;
-- Anonymize all
-- This should fail because of the UNIQUE constraint on the ssn column
-- DISABLED because the error output differs between PG11- and PG12+
--SELECT anonymize_database();
--ROLLBACK TO after_init;
-- Remove uniquess and it should work
ALTER TABLE employee DROP CONSTRAINT employee_ssn_key;
SELECT anon.anonymize_database();
 anonymize_database 
--------------------
 t
(1 row)

-- Issue #114 : Check if all columns are masked
SELECT phone != '0609110911' FROM employee WHERE id=1;
 ?column? 
----------
 t
(1 row)

-- Issue #181
SELECT bool_and(linkedin_url IS NOT NULL) FROM employee;
 bool_and 
----------
 t
(1 row)

-- Anonymize a masked table
SELECT anon.anonymize_table('employee');
 anonymize_table 
-----------------
 t
(1 row)

-- Anonymize a table with no mask
-- returns NULL
SELECT anon.anonymize_table('stock') IS NULL;
 ?column? 
----------
 t
(1 row)

-- Anonymize a table that does not exist
SELECT anon.anonymize_table('employee');
 anonymize_table 
-----------------
 t
(1 row)

-- Issue #185 : improve perfs
SELECT anon.anonymize_table('profile');
 anonymize_table 
-----------------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataA != 'xxxA';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataB != 'xxxB';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataC != 'xxxC';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataD != 'xxxD';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataE != 'xxxE';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataF != 'xxxF';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataG != 'xxxG';
 ?column? 
----------
 t
(1 row)

SELECT count(*)=0 FROM profile WHERE personalDataH != 'xxxH';
 ?column? 
----------
 t
(1 row)

-- Anonymize a masked column
SELECT anon.anonymize_column('employee','phone');
 anonymize_column 
------------------
 t
(1 row)

-- Anonymize an unmasked column
-- returns FALSE and a WARNING
SELECT anon.anonymize_column('employee','firstname') IS FALSE;
 ?column? 
----------
 f
(1 row)

-- Anonymize a column that does not exist
-- returns FALSE and a WARNING
SELECT anon.anonymize_column('employee','xxxxxxxxxxxxxxxx') IS FALSE;
WARNING:  There is no masking rule for column xxxxxxxxxxxxxxxx in table public.employee
 ?column? 
----------
 t
(1 row)

ROLLBACK;