Query: iam_user_denylist_public

SQL

with user_with_acces as (
  select
    distinct project
  from
    gcp_iam_policy,
    jsonb_array_elements(bindings) as b,
    jsonb_array_elements_text(b -> 'members') as m
  where
    m like 'allUsers'
)
select
  a.project as resource,
  case
    when b.project is null then 'ok'
    else 'alarm'
  end as status,
  case
    when b.project is null then 'No public users have access to resources via IAM.'
    else 'Public users have access to resources via IAM.'
  end as reason,
  a.project as project
from
  gcp_iam_policy as a
  left join user_with_acces as b on a.project = b.project;