-- Check the relations for which powa_admin is missing ACL
CREATE FUNCTION has_table_or_seq_privilege(relkind "char", rolname text,
                                           relid oid, priv text)
RETURNS bool
AS $$
BEGIN
    IF relkind = 'S' THEN
        RETURN has_sequence_privilege(rolname, relid, priv);
    ELSE
        RETURN has_table_privilege(rolname, relid, priv);
    END IF;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION check_has_privilege(rolname text,
                                    tbl_priv text[], seq_priv text[])
RETURNS TABLE (powa_role text, relname name, relkind "char", priv text)
AS $$
    WITH ext AS (
        SELECT c.oid, c.relname, c.relkind
        FROM pg_depend d
        JOIN pg_extension e ON d.refclassid = 'pg_extension'::regclass
            AND e.oid = d.refobjid
            AND e.extname = 'powa'
        JOIN pg_class c ON d.classid = 'pg_class'::regclass
            AND c.oid = d.objid
    ),
    acls(priv, isseq) AS (
        SELECT unnest(tbl_priv), false
        UNION ALL
        SELECT unnest(seq_priv), true
    )
    SELECT rolname AS powa_role, relname, relkind, priv
    FROM ext
    JOIN acls ON acls.isseq = (ext.relkind = 'S')
    WHERE NOT has_table_or_seq_privilege(relkind, rolname, ext.oid, priv)
    ORDER BY relname, priv;
$$ LANGUAGE sql;
CREATE FUNCTION check_has_not_privilege(rolname text,
                                        tbl_priv text[], seq_priv text[])
RETURNS TABLE (powa_role text, relname name, relkind "char", priv text)
AS $$
    WITH ext AS (
        SELECT c.oid, c.relname, c.relkind
        FROM pg_depend d
        JOIN pg_extension e ON d.refclassid = 'pg_extension'::regclass
            AND e.oid = d.refobjid
            AND e.extname = 'powa'
        JOIN pg_class c ON d.classid = 'pg_class'::regclass
            AND c.oid = d.objid
    ),
    acls(priv, isseq) AS (
        SELECT unnest(tbl_priv), false
        UNION ALL
        SELECT unnest(seq_priv), true
    )
    SELECT rolname AS powa_role, relname, relkind, priv
    FROM ext
    JOIN acls ON acls.isseq = (ext.relkind = 'S')
    WHERE has_table_or_seq_privilege(relkind, rolname, ext.oid, priv)
    ORDER BY relname, priv;
$$ LANGUAGE sql;
-- powa_admin should have all privileges on all relations
SELECT powa_role, relname, priv
FROM check_has_privilege('powa_admin',
    array ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES',
           'TRIGGER'],
    array ['USAGE', 'SELECT', 'UPDATE']);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- powa_read_all_data should have SELECT privilege on all relation except
 -- *_src_tmp tables and sequences
SELECT powa_role, relname, priv
FROM check_has_privilege('powa_read_all_data',
    array ['SELECT'],
    array []::text[]);
     powa_role      |               relname                |  priv  
--------------------+--------------------------------------+--------
 powa_read_all_data | powa_all_indexes_src_tmp             | SELECT
 powa_read_all_data | powa_all_tables_src_tmp              | SELECT
 powa_read_all_data | powa_catalog_attribute_src_tmp       | SELECT
 powa_read_all_data | powa_catalog_class_src_tmp           | SELECT
 powa_read_all_data | powa_catalog_collation_src_tmp       | SELECT
 powa_read_all_data | powa_catalog_database_src_tmp        | SELECT
 powa_read_all_data | powa_catalog_language_src_tmp        | SELECT
 powa_read_all_data | powa_catalog_namespace_src_tmp       | SELECT
 powa_read_all_data | powa_catalog_proc_src_tmp            | SELECT
 powa_read_all_data | powa_catalog_role_src_tmp            | SELECT
 powa_read_all_data | powa_catalog_type_src_tmp            | SELECT
 powa_read_all_data | powa_databases_src_tmp               | SELECT
 powa_read_all_data | powa_kcache_src_tmp                  | SELECT
 powa_read_all_data | powa_qualstats_src_tmp               | SELECT
 powa_read_all_data | powa_replication_slots_src_tmp       | SELECT
 powa_read_all_data | powa_stat_activity_src_tmp           | SELECT
 powa_read_all_data | powa_stat_archiver_src_tmp           | SELECT
 powa_read_all_data | powa_stat_bgwriter_src_tmp           | SELECT
 powa_read_all_data | powa_stat_checkpointer_src_tmp       | SELECT
 powa_read_all_data | powa_stat_database_conflicts_src_tmp | SELECT
 powa_read_all_data | powa_stat_database_src_tmp           | SELECT
 powa_read_all_data | powa_stat_io_src_tmp                 | SELECT
 powa_read_all_data | powa_stat_replication_src_tmp        | SELECT
 powa_read_all_data | powa_stat_slru_src_tmp               | SELECT
 powa_read_all_data | powa_stat_subscription_src_tmp       | SELECT
 powa_read_all_data | powa_stat_subscription_stats_src_tmp | SELECT
 powa_read_all_data | powa_stat_wal_receiver_src_tmp       | SELECT
 powa_read_all_data | powa_stat_wal_src_tmp                | SELECT
 powa_read_all_data | powa_statements_src_tmp              | SELECT
 powa_read_all_data | powa_user_functions_src_tmp          | SELECT
 powa_read_all_data | powa_wait_sampling_src_tmp           | SELECT
(31 rows)

-- powa_read_all_data should not have non-SELECT privilege on any table, and no
-- privilege on sequences
SELECT powa_role, relname, priv
FROM check_has_not_privilege('powa_read_all_data',
    array ['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'],
    array ['USAGE', 'SELECT', 'UPDATE']);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- powa_read_all_metrics should be the same as powa_read_all_data except that
-- it can't acceess any pg_qualstats related table
SELECT powa_role, relname, priv
FROM check_has_privilege('powa_read_all_metrics',
    array ['SELECT'],
    array []::text[]);
       powa_role       |                  relname                   |  priv  
-----------------------+--------------------------------------------+--------
 powa_read_all_metrics | powa_all_indexes_src_tmp                   | SELECT
 powa_read_all_metrics | powa_all_tables_src_tmp                    | SELECT
 powa_read_all_metrics | powa_catalog_attribute_src_tmp             | SELECT
 powa_read_all_metrics | powa_catalog_class_src_tmp                 | SELECT
 powa_read_all_metrics | powa_catalog_collation_src_tmp             | SELECT
 powa_read_all_metrics | powa_catalog_database_src_tmp              | SELECT
 powa_read_all_metrics | powa_catalog_language_src_tmp              | SELECT
 powa_read_all_metrics | powa_catalog_namespace_src_tmp             | SELECT
 powa_read_all_metrics | powa_catalog_proc_src_tmp                  | SELECT
 powa_read_all_metrics | powa_catalog_role_src_tmp                  | SELECT
 powa_read_all_metrics | powa_catalog_type_src_tmp                  | SELECT
 powa_read_all_metrics | powa_databases_src_tmp                     | SELECT
 powa_read_all_metrics | powa_kcache_src_tmp                        | SELECT
 powa_read_all_metrics | powa_qualstats_constvalues_history         | SELECT
 powa_read_all_metrics | powa_qualstats_constvalues_history_current | SELECT
 powa_read_all_metrics | powa_qualstats_src_tmp                     | SELECT
 powa_read_all_metrics | powa_replication_slots_src_tmp             | SELECT
 powa_read_all_metrics | powa_stat_activity_src_tmp                 | SELECT
 powa_read_all_metrics | powa_stat_archiver_src_tmp                 | SELECT
 powa_read_all_metrics | powa_stat_bgwriter_src_tmp                 | SELECT
 powa_read_all_metrics | powa_stat_checkpointer_src_tmp             | SELECT
 powa_read_all_metrics | powa_stat_database_conflicts_src_tmp       | SELECT
 powa_read_all_metrics | powa_stat_database_src_tmp                 | SELECT
 powa_read_all_metrics | powa_stat_io_src_tmp                       | SELECT
 powa_read_all_metrics | powa_stat_replication_src_tmp              | SELECT
 powa_read_all_metrics | powa_stat_slru_src_tmp                     | SELECT
 powa_read_all_metrics | powa_stat_subscription_src_tmp             | SELECT
 powa_read_all_metrics | powa_stat_subscription_stats_src_tmp       | SELECT
 powa_read_all_metrics | powa_stat_wal_receiver_src_tmp             | SELECT
 powa_read_all_metrics | powa_stat_wal_src_tmp                      | SELECT
 powa_read_all_metrics | powa_statements_src_tmp                    | SELECT
 powa_read_all_metrics | powa_user_functions_src_tmp                | SELECT
 powa_read_all_metrics | powa_wait_sampling_src_tmp                 | SELECT
(33 rows)

SELECT powa_role, relname, priv
FROM check_has_not_privilege('powa_read_all_metrics',
    array ['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'],
    array ['USAGE', 'SELECT', 'UPDATE']);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- powa_write_all_data should have SELECT/INSERT/UPDATE/DELETE/TRUNCATE
-- privileges on all relations (and all privileges on sequences)
SELECT powa_role, relname, priv
FROM check_has_privilege('powa_write_all_data',
    array ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'],
    array ['USAGE', 'SELECT', 'UPDATE']);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- powa_write_all_data should not have TRIGGER/REFERENCES privileges on any
-- relations
SELECT powa_role, relname, priv
FROM check_has_not_privilege('powa_write_all_data',
    array ['TRIGGER', 'REFERENCES'],
    array []::text[]);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- powa_snapshot should have SELECT/INSERT/UPDATE/DELETE/TRUNCATE
-- privileges on all metric-related relations (and all privileges on sequences)
-- only
SELECT powa_role, relname, relkind, array_agg(priv)
FROM check_has_privilege('powa_snapshot',
    array ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'],
    array ['USAGE', 'SELECT', 'UPDATE'])
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;
   powa_role   |          relname           | relkind |            array_agg            
---------------+----------------------------+---------+---------------------------------
 powa_snapshot | powa_all_functions         | v       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_catalog_src_queries   | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_catalogs              | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_db_module_config      | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_db_module_functions   | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_db_module_src_queries | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_db_modules            | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_extension_config      | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_extension_functions   | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_extensions            | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_functions             | v       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_module_config         | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_module_functions      | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_modules               | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_roles                 | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_servers               | r       | {DELETE,INSERT,TRUNCATE,UPDATE}
 powa_snapshot | powa_servers_id_seq        | S       | {SELECT,UPDATE,USAGE}
(17 rows)

-- powa_snapshot should not have TRIGGER/REFERENCES privileges on any relations
SELECT powa_role, relname, priv
FROM check_has_not_privilege('powa_snapshot',
    array ['TRIGGER', 'REFERENCES'],
    array []::text[]);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)

-- and try to detect any unexpected GRANT on powa_snapshot, as any newly
-- created table will have too many privileges granted unless explicitly
-- handled
SELECT DISTINCT powa_role, relname
FROM check_has_not_privilege('powa_snapshot',
    array ['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'],
    array ['USAGE', 'SELECT', 'UPDATE'])
WHERE relkind != 'v'
AND relname NOT LIKE '%history'
AND relname NOT LIKE '%history\_db'
AND relname NOT LIKE '%history\_current'
AND relname NOT LIKE '%history\_current\_db'
AND relname NOT LIKE '%src\_tmp'
AND relname NOT LIKE 'powa\_catalog\_%'
AND relname NOT LIKE '%qualstats%'
AND relname NOT LIKE '%kcache%'
AND relname NOT IN ('powa_databases', 'powa_snapshot_metas', 'powa_statements');
 powa_role | relname 
-----------+---------
(0 rows)

-- powa_signal_backend should not have any privilege on any relation
SELECT powa_role, relname, priv
FROM check_has_not_privilege('powa_signal_backend',
    array ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES',
           'TRIGGER'],
    array ['USAGE', 'SELECT', 'UPDATE']);
 powa_role | relname | priv 
-----------+---------+------
(0 rows)