BookNest needs three kinds of reader: finance sees all of the mart, regional analysts see their region's sales lines, and support sees customers with masked e-mail and no sales at all. Groups carry the privileges and logins inherit them, so adding a person is one GRANT. The last listing adds finance and a support login, then checks the privilege matrix and what each role actually reads:
CREATE ROLE finance NOLOGIN;
CREATE ROLE fin_ana LOGIN IN ROLE finance;
CREATE ROLE sup_sam LOGIN IN ROLE support;
GRANT USAGE ON SCHEMA mart TO finance;
GRANT SELECT ON ALL TABLES IN SCHEMA mart TO finance;
CREATE POLICY finance_all ON mart.sales FOR SELECT TO finance USING (true); -- OR'ed
SELECT r AS login, has_table_privilege(r, 'mart.sales', 'SELECT') AS sales,
has_table_privilege(r, 'mart.fact_sales', 'SELECT') AS star,
has_column_privilege(r, 'customers', 'email', 'SELECT') AS raw_email,
has_table_privilege(r, 'mart.customers_masked', 'SELECT') AS masked_email
FROM unnest(ARRAY['fin_ana', 'ana_eu', 'ana_apac', 'sup_sam']) AS r;
SET ROLE fin_ana;
SELECT current_user, count(*) AS lines, sum(gross_amount) AS gross FROM mart.sales;
SET ROLE ana_eu;
SELECT current_user, count(*) AS lines, sum(gross_amount) AS gross FROM mart.sales;
RESET ROLE;login | sales | star | raw_email | masked_email ----------+-------+------+-----------+-------------- fin_ana | t | t | f | t ana_eu | t | f | f | f ana_apac | t | f | f | f sup_sam | f | f | f | t fin_ana | 137944 | 3509959.19 ... ana_eu | 23808 | 610018.10
Finance needed its own permissive policy: with RLS enabled, a GRANT alone would show it no rows. Run such checks after every deployment, like the dbt 37,942 tests of Generic and Singular Tests, and mind rollups: mv_region_group_month (Pre-Aggregation and Caching) has no policy, so grant it only to roles that may see every region. 416-reset.sql undoes this section.