-- This test cannot be run in a single transaction
-- This test must be run on a database named 'contrib_regression'
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
NOTICE:  installing required extension "tsm_system_rows"
NOTICE:  installing required extension "pgcrypto"
-- INIT
SELECT anon.mask_init();
 mask_init 
-----------
 t
(1 row)

SELECT anon.start_dynamic_masking();
NOTICE:  schema "mask" already exists, skipping
 start_dynamic_masking 
-----------------------
 t
(1 row)

-- Table `people`
CREATE TABLE people (
  id SERIAL UNIQUE,
  name TEXT,
  "CreditCard" TEXT,
  fk_company INTEGER
);
INSERT INTO people
VALUES (1,'Schwarzenegger','1234567812345678', 1991);
SECURITY LABEL FOR anon ON COLUMN people.name
IS 'MASKED WITH FUNCTION anon.random_last_name() ';
SECURITY LABEL FOR anon ON COLUMN people."CreditCard"
IS 'MASKED WITH FUNCTION         anon.random_string(12)';
-- Table `CoMPaNy`
CREATE TABLE "CoMPaNy" (
  id_company SERIAL UNIQUE,
  "IBAN" TEXT,
  NAME TEXT
);
INSERT INTO "CoMPaNy"
VALUES (1991,'12345677890','Cyberdyne Systems');
SECURITY LABEL FOR anon ON COLUMN "CoMPaNy"."IBAN"
IS 'MASKED WITH FUNCTION anon.partial("IBAN", 0, $$***********$$, 4 ) This will be ignored';
SECURITY LABEL FOR anon ON COLUMN "CoMPaNy".NAME
IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';
-- BUG #51 :
CREATE TABLE test_type_casts(
  last_name VARCHAR(30)
);
SECURITY LABEL FOR anon ON column test_type_casts.last_name
IS 'MASKED WITH FUNCTION anon.random_last_name()::VARCHAR(30)';
-- Table `work`
CREATE TABLE work (
  id_work SERIAL,
  fk_employee INTEGER NOT NULL,
  fk_company INTEGER NOT NULL,
  first_day DATE NOT NULL,
  last_day DATE,
  FOREIGN KEY (fk_employee) references people(id),
  FOREIGN KEY (fk_company) references "CoMPaNy"(id_company)
);
INSERT INTO work
VALUES ( 1, 1 , 1991, DATE '1985-05-25',NULL);
SELECT count(*) = 5  FROM anon.pg_masks;
 ?column? 
----------
 t
(1 row)

SELECT masking_function = 'anon.partial("IBAN", 0, $$***********$$, 4 )'
  FROM anon.pg_masks
  WHERE attname = 'IBAN';
 ?column? 
----------
 t
(1 row)

SELECT name != 'Cyberdyne Systems' FROM mask."CoMPaNy" WHERE id_company=1991;
 ?column? 
----------
 t
(1 row)

SELECT name != 'Schwarzenegger' FROM mask.people WHERE id = 1;
 ?column? 
----------
 t
(1 row)

-- ROLE
CREATE ROLE skynet LOGIN;
SECURITY LABEL FOR anon ON ROLE skynet IS 'MASKED';
-- FORCE update because SECURITY LABEL doesn't trigger the Event Trigger
SELECT anon.mask_update();
 mask_update 
-------------
 t
(1 row)

-- We're using an external connection instead of `SET ROLE`
-- Because we need the tricky search_path
\! psql contrib_regression -U skynet -c 'SHOW search_path;'
 search_path  
--------------
 mask, public
(1 row)

-- This test should fail
--
-- We're catching the output because the message
-- PG10 would say "ERROR:  permission denied for relation people"
-- PG11 would say "ERROR:  permission denied for table people"
--
\! psql contrib_regression -U skynet -c "SELECT * FROM public.people;"  2>&1 | grep --silent 'ERROR:  permission denied' && echo 'ERROR:  permission denied'
ERROR:  permission denied
\! psql contrib_regression -U skynet -c "SELECT name != 'Schwarzenegger' FROM people WHERE id = 1;"
 ?column? 
----------
 t
(1 row)

\! psql contrib_regression -U skynet -c "SELECT name != 'Cyberdyne Systems' FROM \"CoMPaNy\" WHERE id_company=1991;"
 ?column? 
----------
 t
(1 row)

-- A maked role cannot modify a table containing a mask column
\! psql contrib_regression -U skynet -c "DELETE FROM people;" 2>&1 | grep --silent 'ERROR:  permission denied' && echo 'ERROR:  permission denied'
ERROR:  permission denied
\! psql contrib_regression -U skynet -c "UPDATE people SET name = 'check' WHERE name ='Schwarzenegger';"
ERROR:  cannot update column "name" of view "people"
DETAIL:  View columns that are not columns of their base relation are not updatable.
\! psql contrib_regression -U skynet -c "INSERT INTO people VALUES (1,'Schwarzenegger','1234567812345678', 1991);" ;
ERROR:  cannot insert into column "name" of view "people"
DETAIL:  View columns that are not columns of their base relation are not updatable.
\! psql contrib_regression -U skynet -c "DELETE FROM work;" 2>&1 | grep --silent 'ERROR:  permission denied' && echo 'ERROR:  permission denied'
ERROR:  permission denied
-- A masked role cannot access the stats of a masked column
\! psql contrib_regression -U skynet -c "SELECT count(histogram_bounds)=0 FROM pg_stats WHERE tablename='people' AND attname='name';"
 ?column? 
----------
 t
(1 row)

--  CLEAN
--DROP SCHEMA mask CASCADE;
DROP TABLE test_type_casts CASCADE;
NOTICE:  drop cascades to view mask.test_type_casts
DROP TABLE work CASCADE;
NOTICE:  drop cascades to view mask.work
DROP TABLE "CoMPaNy" CASCADE;
NOTICE:  drop cascades to view mask."CoMPaNy"
DROP TABLE people CASCADE;
NOTICE:  drop cascades to view mask.people
DROP EXTENSION anon CASCADE;
NOTICE:  drop cascades to event trigger anon_mask_update
DROP EXTENSION tsm_system_rows;
DROP EXTENSION pgcrypto;
DROP SCHEMA mask;
REASSIGN OWNED BY skynet TO postgres;
DROP OWNED BY skynet CASCADE;
DROP ROLE skynet;