-- This test cannot be run in a single transcation
-- This test must be run on a database named 'contrib_regression'
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
-- INIT
SELECT anon.mask_init();
NOTICE:  schema "mask" already exists, skipping
 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.random_iban()';
SECURITY LABEL FOR anon ON COLUMN "CoMPaNy".NAME 
IS 'MASKED WITH FUNCTION anon.random_company() jenfk snvi  jdnvkjsnvsndvjs';
-- 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(*) = 4  FROM anon.pg_masks;
 ?column? 
----------
 f
(1 row)

SELECT masking_function = 'anon.random_iban()' 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 anyway, the skynet role is not allowed to access the people table
\! psql contrib_regression -U skynet -c "SELECT * FROM public.people;"
ERROR:  permission denied for table people
\! 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;"
ERROR:  permission denied for view people
\! 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;";
ERROR:  permission denied for view work
-- 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
REASSIGN OWNED BY skynet TO postgres;
DROP OWNED BY skynet CASCADE;
DROP ROLE skynet;