LOAD 'pg_diffix';
CREATE TABLE test_validation (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  discount REAL,
  birthday DATE,
  lunchtime TIME,
  last_seen TIMESTAMP
);
CALL diffix.mark_personal('test_validation', 'id');
CREATE TABLE superclass (x INTEGER);
CREATE TABLE subclass (x INTEGER, y INTEGER);
INSERT INTO subclass VALUES (1, 2);
CALL diffix.mark_personal('superclass', 'x');
CALL diffix.mark_personal('subclass', 'y');
ALTER TABLE subclass INHERIT superclass;
-- No-op. Repeated to test the error on conflicting configuration
CALL diffix.mark_personal('superclass', 'x');
ERROR:  [PG_DIFFIX] Anonymization over tables using inheritance is not supported.
CONTEXT:  SQL statement "SECURITY LABEL FOR pg_diffix ON TABLE superclass IS 'personal'"
PL/pgSQL function diffix.mark_personal(text,text[]) line 7 at EXECUTE
CALL diffix.mark_personal('subclass', 'y');
ERROR:  [PG_DIFFIX] Anonymization over tables using inheritance is not supported.
CONTEXT:  SQL statement "SECURITY LABEL FOR pg_diffix ON TABLE subclass IS 'personal'"
PL/pgSQL function diffix.mark_personal(text,text[]) line 7 at EXECUTE
SET ROLE diffix_test;
----------------------------------------------------------------
-- Trusted mode query restrictions
----------------------------------------------------------------
SET pg_diffix.session_access_level = 'anonymized_trusted';
SELECT diffix.access_level();
    access_level    
--------------------
 anonymized_trusted
(1 row)

----------------------------------------------------------------
-- Supported queries
----------------------------------------------------------------
-- Supported functions for defining buckets
SELECT COUNT(*) FROM test_validation
  GROUP BY substring(city, 1, 1);
 count 
-------
(0 rows)

SELECT COUNT(*) FROM test_validation
  GROUP BY width_bucket(id, 0, 1000, 10), width_bucket(id::float, 0.0, 1000.0, 10);
 count 
-------
(0 rows)

SELECT COUNT(*) FROM test_validation
  GROUP BY round(id::float, -1), round(id::numeric, -1);
ERROR:  function round(double precision, integer) does not exist
LINE 2:   GROUP BY round(id::float, -1), round(id::numeric, -1);
                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SELECT COUNT(*) FROM test_validation
  GROUP BY round(id::float), ceil(id::float), ceiling(id::float), floor(id::float);
 count 
-------
(0 rows)

SELECT COUNT(*) FROM test_validation
  GROUP BY round(id::numeric), ceil(id::numeric), ceiling(id::numeric), floor(id::numeric);
 count 
-------
(0 rows)

SELECT
  diffix.round_by(id::numeric, 5),
  diffix.round_by(id::double precision, 5),
  COUNT(*)
FROM test_validation
GROUP BY 1, 2;
 round_by | round_by | count 
----------+----------+-------
(0 rows)

SELECT
  diffix.ceil_by(id::numeric, 5),
  diffix.ceil_by(id::double precision, 5),
  COUNT(*)
FROM test_validation
GROUP BY 1, 2;
 ceil_by | ceil_by | count 
---------+---------+-------
(0 rows)

SELECT
  diffix.floor_by(id::numeric, 5),
  diffix.floor_by(id::double precision, 5),
  COUNT(*)
FROM test_validation
GROUP BY 1, 2;
 floor_by | floor_by | count 
----------+----------+-------
(0 rows)

SELECT
  substring(cast(last_seen AS text), 1, 3),
  substring(cast(birthday AS text), 2, 3),
  substring(cast(lunchtime AS varchar), 1, 4)
FROM test_validation
GROUP BY 1, 2, 3;
 substring | substring | substring 
-----------+-----------+-----------
(0 rows)

-- Allow all functions post-anonymization.
SELECT 2 * length(city) FROM test_validation GROUP BY city;
 ?column? 
----------
(0 rows)

-- Allow diffix.is_suppress_bin in non-direct access level.
SELECT city, count(*), diffix.is_suppress_bin(*) from test_validation GROUP BY 1;
 city | count | is_suppress_bin 
------+-------+-----------------
(0 rows)

-- Set operations between anonymizing queries.
SELECT city FROM test_validation EXCEPT SELECT city FROM test_validation;
 city 
------
(0 rows)

SELECT city FROM test_validation UNION SELECT city FROM test_validation;
 city 
------
(0 rows)

-- Anonymizing sublinks are supported.
SELECT EXISTS (SELECT city FROM test_validation);
 exists 
--------
 f
(1 row)

SELECT 1 WHERE EXISTS (SELECT city FROM test_validation);
 ?column? 
----------
(0 rows)

-- Anonymizing leaf subqueries are supported.
SELECT * FROM ( SELECT COUNT(*) FROM test_validation ) x;
 count 
-------
     0
(1 row)

SELECT COUNT(city)
FROM (
  SELECT city FROM test_validation
  GROUP BY 1
) x;
 count 
-------
     0
(1 row)

SELECT COUNT(*), COUNT(x.city), COUNT(DISTINCT x.id)
FROM (
  SELECT * FROM test_validation
) x;
 count | count | count 
-------+-------+-------
     0 |     0 |     0
(1 row)

SELECT COUNT(DISTINCT x.modified_id) FROM ( SELECT id AS modified_id FROM test_validation ) x;
 count 
-------
     0
(1 row)

SELECT COUNT(*), COUNT(x.city), COUNT(DISTINCT x.user_id)
FROM (
  SELECT y.city as city, y.id as user_id
  FROM ( SELECT * FROM test_validation ) y
) x;
 count | count | count 
-------+-------+-------
     0 |     0 |     0
(1 row)

SELECT * FROM (SELECT name FROM test_validation) x, (SELECT city FROM test_validation) y;
 name | city 
------+------
(0 rows)

WITH c AS (SELECT city FROM test_validation) SELECT * FROM c;
 city 
------
(0 rows)

SELECT (SELECT city FROM test_validation);
 city 
------
 
(1 row)

-- Allow discovery commands
\dt empty_test_customers
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | empty_test_customers | table | postgres
(1 row)

\d empty_test_customers
         Table "public.empty_test_customers"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 id       | integer |           | not null | 
 name     | text    |           |          | 
 city     | text    |           |          | 
 discount | real    |           |          | 
Indexes:
    "empty_test_customers_pkey" PRIMARY KEY, btree (id)

\dt+ empty_test_customers
                                     List of relations
 Schema |         Name         | Type  |  Owner   | Persistence |    Size    | Description 
--------+----------------------+-------+----------+-------------+------------+-------------
 public | empty_test_customers | table | postgres | permanent   | 8192 bytes | 
(1 row)

\d+ empty_test_customers
                             Table "public.empty_test_customers"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 id       | integer |           | not null |         | plain    |              | 
 name     | text    |           |          |         | extended |              | 
 city     | text    |           |          |         | extended |              | 
 discount | real    |           |          |         | plain    |              | 
Indexes:
    "empty_test_customers_pkey" PRIMARY KEY, btree (id)

-- Settings and labels UDFs work
SELECT * FROM diffix.show_settings() LIMIT 2;
              name              | setting |                                   short_desc                                   
--------------------------------+---------+--------------------------------------------------------------------------------
 pg_diffix.compute_suppress_bin | on      | Whether the suppress bin should be computed and included in the query results.
 pg_diffix.default_access_level | direct  | Access level for unlabeled users.
(2 rows)

SELECT * FROM diffix.show_labels() WHERE objname LIKE 'public.test_customers%';
 objtype |         objname          |  label   
---------+--------------------------+----------
 table   | public.test_customers    | personal
 column  | public.test_customers.id | aid
(2 rows)

----------------------------------------------------------------
-- Unsupported queries
----------------------------------------------------------------
-- Get rejected because non SELECT queries are unsupported.
INSERT INTO test_validation VALUES (NULL, NULL,NULL);
ERROR:  [PG_DIFFIX] Feature 'non-select query' is not currently supported.
-- Get rejected because WITH is unsupported.
WITH c AS (SELECT 1 FROM test_validation) SELECT 1 FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'WITH' is not currently supported.
-- Get rejected because GROUPING SETS are unsupported.
SELECT city FROM test_validation GROUP BY GROUPING SETS ((city), ());
ERROR:  [PG_DIFFIX] Feature 'GROUPING SETS' is not currently supported.
SELECT city FROM test_validation GROUP BY CUBE ((city));
ERROR:  [PG_DIFFIX] Feature 'GROUPING SETS' is not currently supported.
-- Get rejected because SRF functions are unsupported.
SELECT generate_series(1,4) FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'SRF functions' is not currently supported.
-- Get rejected because sublinks are unsupported.
SELECT city, (SELECT 1 FROM test_validation) FROM test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Feature 'SubLinks' is not currently supported.
-- Get rejected because DISTINCT is unsupported.
SELECT DISTINCT city FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'DISTINCT' is not currently supported.
-- Get rejected because window functions are unsupported.
SELECT avg(discount) OVER (PARTITION BY city) FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'window functions' is not currently supported.
-- Get rejected because aggregators are unsupported.
SELECT SUM(id) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported aggregate in query.
LINE 1: SELECT SUM(id) FROM test_validation;
               ^
SELECT MIN(id) + MAX(id) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported aggregate in query.
LINE 1: SELECT MIN(id) + MAX(id) FROM test_validation;
               ^
SELECT city FROM test_validation GROUP BY 1 ORDER BY AVG(LENGTH(city));
ERROR:  [PG_DIFFIX] Unsupported aggregate in query.
LINE 1: ...ECT city FROM test_validation GROUP BY 1 ORDER BY AVG(LENGTH...
                                                             ^
SELECT count(city ORDER BY city) FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'ORDER BY clauses in aggregate expressions' is not currently supported.
SELECT count(*) FILTER (WHERE true) FROM test_validation;
ERROR:  [PG_DIFFIX] Feature 'FILTER clauses in aggregate expressions' is not currently supported.
SELECT count(distinct id + 5) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported expression as aggregate argument.
LINE 1: SELECT count(distinct id + 5) FROM test_validation;
               ^
SELECT count(distinct least(id, 5)) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported expression as aggregate argument.
LINE 1: SELECT count(distinct least(id, 5)) FROM test_validation;
               ^
SELECT count(id + 5) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported expression as aggregate argument.
LINE 1: SELECT count(id + 5) FROM test_validation;
               ^
SELECT count(least(id, 5)) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported expression as aggregate argument.
LINE 1: SELECT count(least(id, 5)) FROM test_validation;
               ^
-- Get rejected because only a subset of expressions is supported for defining buckets.
SELECT COUNT(*) FROM test_validation GROUP BY LENGTH(city);
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY LENGTH(city);
                                                      ^
SELECT COUNT(*) FROM test_validation GROUP BY city || 'xxx';
ERROR:  [PG_DIFFIX] Use of operators to define buckets is not supported.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY city || 'xxx';
                                                           ^
SELECT LENGTH(city) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT LENGTH(city) FROM test_validation;
               ^
SELECT city, 'aaaa' FROM test_validation GROUP BY 1, 2;
ERROR:  [PG_DIFFIX] Simple constants are not allowed as bucket expressions.
LINE 1: SELECT city, 'aaaa' FROM test_validation GROUP BY 1, 2;
                     ^
SELECT COUNT(*) FROM test_validation GROUP BY round(floor(id));
ERROR:  [PG_DIFFIX] Primary argument for a bucket function has to be a simple column reference.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY round(floor(id...
                                                      ^
SELECT COUNT(*) FROM test_validation GROUP BY floor(cast(discount AS integer));
ERROR:  [PG_DIFFIX] Primary argument for a bucket function has to be a simple column reference.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY floor(cast(dis...
                                                      ^
SELECT COUNT(*) FROM test_validation GROUP BY substr(city, 1, id);
ERROR:  [PG_DIFFIX] Non-primary arguments for a bucket function have to be simple constants.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY substr(city, 1...
                                                      ^
SELECT COUNT(*) FROM test_validation GROUP BY substr('aaaa', 1, 2);
ERROR:  [PG_DIFFIX] Primary argument for a bucket function has to be a simple column reference.
LINE 1: SELECT COUNT(*) FROM test_validation GROUP BY substr('aaaa',...
                                                      ^
-- Get rejected because expression node type is unsupported.
SELECT COALESCE(discount, 20) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported or unrecognized query node type
SELECT NULLIF(discount, 20) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported or unrecognized query node type
SELECT GREATEST(discount, 20) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported or unrecognized query node type
SELECT LEAST(discount, 20) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported or unrecognized query node type
-- Get rejected because of JOINs
SELECT COUNT(*), COUNT(DISTINCT id), COUNT(DISTINCT cid) FROM test_validation
  INNER JOIN test_purchases tp ON id = cid;
ERROR:  [PG_DIFFIX] Feature 'JOINs in anonymizing queries' is not currently supported.
SELECT COUNT(c.city), COUNT(p.name) FROM test_validation c
  LEFT JOIN test_purchases ON c.id = cid
  LEFT JOIN test_products p ON pid = p.id;
ERROR:  [PG_DIFFIX] Feature 'JOINs in anonymizing queries' is not currently supported.
SELECT city, COUNT(price) FROM test_validation, test_products GROUP BY 1;
ERROR:  [PG_DIFFIX] Feature 'JOINs in anonymizing queries' is not currently supported.
SELECT city, COUNT(price) FROM test_products, test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Feature 'JOINs in anonymizing queries' is not currently supported.
SELECT city, COUNT(price) FROM test_products CROSS JOIN test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Feature 'JOINs in anonymizing queries' is not currently supported.
-- Get rejected because of WHERE
SELECT COUNT(*) FROM test_validation WHERE city = 'London';
ERROR:  [PG_DIFFIX] Feature 'WHERE clauses in anonymizing queries' is not currently supported.
-- Get rejected because of non-datetime cast to text
SELECT cast(id AS text) FROM test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Unsupported cast destination type name.
LINE 1: SELECT cast(id AS text) FROM test_validation GROUP BY 1;
               ^
SELECT cast(id AS varchar) FROM test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Unsupported cast destination type name.
LINE 1: SELECT cast(id AS varchar) FROM test_validation GROUP BY 1;
               ^
SELECT substring(cast(id AS text), 1, 1) FROM test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Primary argument for a bucket function has to be a simple column reference.
LINE 1: SELECT substring(cast(id AS text), 1, 1) FROM test_validatio...
               ^
SELECT substring(cast(id AS varchar), 1, 1) FROM test_validation GROUP BY 1;
ERROR:  [PG_DIFFIX] Primary argument for a bucket function has to be a simple column reference.
LINE 1: SELECT substring(cast(id AS varchar), 1, 1) FROM test_valida...
               ^
-- Invalid subqueries are rejected.
SELECT * FROM (SELECT length(city) FROM test_validation) x;
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT * FROM (SELECT length(city) FROM test_validation) x;
                              ^
SELECT EXISTS (SELECT length(city) FROM test_validation);
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT EXISTS (SELECT length(city) FROM test_validation);
                              ^
SELECT 1 WHERE EXISTS (SELECT length(city) FROM test_validation);
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT 1 WHERE EXISTS (SELECT length(city) FROM test_validat...
                                      ^
SELECT 1 UNION SELECT length(city) FROM test_validation;
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT 1 UNION SELECT length(city) FROM test_validation;
                              ^
SELECT * FROM (SELECT 1) t1, (SELECT length(city) FROM test_validation) t2;
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT * FROM (SELECT 1) t1, (SELECT length(city) FROM test_...
                                             ^
WITH c AS (SELECT length(city) FROM test_validation) SELECT * FROM c;
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: WITH c AS (SELECT length(city) FROM test_validation) SELECT ...
                          ^
SELECT (SELECT length(city) FROM test_validation);
ERROR:  [PG_DIFFIX] Unsupported function used to define buckets.
LINE 1: SELECT (SELECT length(city) FROM test_validation);
                       ^
-- Get rejected because of accessing pg_catalog tables with sensitive stats
SELECT * FROM pg_stats LIMIT 10;
ERROR:  permission denied for schema pg_catalog
SELECT * FROM pg_statistic LIMIT 10;
ERROR:  permission denied for table pg_statistic
SELECT * FROM pg_stat_user_functions LIMIT 10;
ERROR:  permission denied for schema pg_catalog
SELECT * FROM pg_stat_user_indexes LIMIT 10;
ERROR:  permission denied for schema pg_catalog
SELECT * FROM pg_class LIMIT 10;
ERROR:  permission denied for schema pg_catalog
-- Get rejected because of inheritance
SELECT x, y FROM subclass;
ERROR:  [PG_DIFFIX] Feature 'Inheritance in anonymizing queries.' is not currently supported.
SELECT x FROM superclass;
ERROR:  [PG_DIFFIX] Feature 'Inheritance in anonymizing queries.' is not currently supported.
-- Get rejected because attempt to use system columns
SELECT ctid FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT ctid FROM test_validation;
               ^
SELECT tableoid FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT tableoid FROM test_validation;
               ^
SELECT count(ctid) FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT count(ctid) FROM test_validation;
                     ^
SELECT count(tableoid) FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT count(tableoid) FROM test_validation;
                     ^
SELECT count(distinct ctid) FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT count(distinct ctid) FROM test_validation;
                              ^
SELECT count(distinct tableoid) FROM test_validation;
ERROR:  [PG_DIFFIX] System columns are not allowed in this context.
LINE 1: SELECT count(distinct tableoid) FROM test_validation;
                              ^
----------------------------------------------------------------
-- Untrusted mode query restrictions
----------------------------------------------------------------
SET pg_diffix.session_access_level = 'anonymized_untrusted';
SELECT diffix.access_level();
     access_level     
----------------------
 anonymized_untrusted
(1 row)

-- Get accepted
SELECT substring(city, 1, 2) from test_validation;
 substring 
-----------
(0 rows)

SELECT floor(discount) from test_validation;
 floor 
-------
(0 rows)

SELECT round(discount) from test_validation;
 round 
-------
(0 rows)

SELECT discount from test_validation;
 discount 
----------
(0 rows)

SELECT diffix.floor_by(discount, 2) from test_validation;
 floor_by 
----------
(0 rows)

SELECT diffix.round_by(discount, 2) from test_validation;
 round_by 
----------
(0 rows)

SELECT diffix.floor_by(discount, 20) from test_validation;
 floor_by 
----------
(0 rows)

SELECT diffix.floor_by(discount, 2.0) from test_validation;
 floor_by 
----------
(0 rows)

SELECT diffix.floor_by(discount, 0.2) from test_validation;
 floor_by 
----------
(0 rows)

SELECT diffix.floor_by(discount, 20.0) from test_validation;
 floor_by 
----------
(0 rows)

SELECT diffix.floor_by(discount, 50.0) from test_validation;
 floor_by 
----------
(0 rows)

-- Get rejected because of invalid generalization parameters
SELECT substring(city, 2, 2) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT substring(city, 2, 2) from test_validation;
                               ^
SELECT diffix.floor_by(discount, 3) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT diffix.floor_by(discount, 3) from test_validation;
                                         ^
SELECT diffix.floor_by(discount, 3.0) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT diffix.floor_by(discount, 3.0) from test_validation;
                                         ^
SELECT diffix.floor_by(discount, 5000000000.1) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT diffix.floor_by(discount, 5000000000.1) from test_val...
                                         ^
-- Get rejected because of invalid generalizing functions
SELECT width_bucket(discount, 2, 200, 5) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT width_bucket(discount, 2, 200, 5) from test_validatio...
               ^
SELECT ceil(discount) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT ceil(discount) from test_validation;
               ^
SELECT diffix.ceil_by(discount, 2) from test_validation;
ERROR:  [PG_DIFFIX] Generalization used in the query is not allowed in untrusted access level.
LINE 1: SELECT diffix.ceil_by(discount, 2) from test_validation;
               ^