[documentation] provide generated view definitions in examples/tutorials

[w0ut0]

Problem description While the provided tutorials show the configuration of policies quite well, it's not super clear to see how the resulting view is implemented (for each processing platform). Eg., does Pace make use of the Databricks UC built-in masking feature, or is it implemented in pure SQL?

Proposed solution For all tutorials, include the generated view definitions (next to the result table, shown per principal - which IS cool).

Additional context Pace looks really promising, looking forward to see it evolve!

[astronomous]

Hey @w0ut0 ! Thanks for your issue. Only logical! We'll get to it. The quick answer is: we leverage as much native functionality as possible (but platforms differ to a great degree). Meanwhile, pending any questions you might have, join us on Slack to discuss directly. Thanks again!

[trietsch]

Great idea! I've taken the opportunity to take this one step further: a dedicated API in PACE that will return the SQL DDL for the respective Data Policy, as the end result is not only dependent on the Data Policy, but also on the configuration of your PACE instance (e.g. for BigQuery there's a toggle in the PACE configuration to either use native IAM checks or use a principals reference table). I'll make sure to include the SQL DDL in the documentation as well :)

[trietsch]

@w0ut0 To give you a preview of the functionality, here's the corresponding CLI command for a Data Policy from our standalone example:

pace transpile data-policy --data-policy-file data-policy.yaml -o plain

create or replace view "public"."demo_view"
as
with
  "user_groups" as (
    select "rolname"
    from "pg_roles"
    where (
      "rolcanlogin" = false
      and pg_has_role(session_user, oid, 'member')
    )
  )
select
  "transactionid",
  case
    when (
      ('fraud_and_risk' IN ( SELECT rolname FROM user_groups ))
      or ('administrator' IN ( SELECT rolname FROM user_groups ))
    ) then "userid"
    else 0
  end as userid,
  case
    when ('administrator' IN ( SELECT rolname FROM user_groups )) then "email"
    when ('marketing' IN ( SELECT rolname FROM user_groups )) then regexp_replace(email, '^.*(@.*)$', '****\1', 'g')
    when ('fraud_and_risk' IN ( SELECT rolname FROM user_groups )) then "email"
    else '****'
  end as email,
  "age",
  case
    when ('administrator' IN ( SELECT rolname FROM user_groups )) then "brand"
    else CASE WHEN brand = 'MacBook' THEN 'Apple' ELSE 'Other' END
  end as brand,
  "transactionamount"
from "public"."demo"
where case
  when (
    ('administrator' IN ( SELECT rolname FROM user_groups ))
    or ('fraud_and_risk' IN ( SELECT rolname FROM user_groups ))
  ) then true
  else age > 8
end;
grant SELECT on public.demo_view to "fraud_and_risk";
grant SELECT on public.demo_view to "administrator";
grant SELECT on public.demo_view to "marketing";