Control: Pub/Sub subscription IAM policies should only grant access to trusted principals

powerpipe control run gcp_perimeter.control.pubsub_subscription_policy_shared_access

powerpipe login
powerpipe control run gcp_perimeter.control.pubsub_subscription_policy_shared_access --share

    with policy_analysis as (
      select
        name as resource_id,
        -- Count all members
        count(*) as total_members,
        -- Count project-level members
        count(*) filter (where member like 'project%') as project_members,
        -- Count trusted members (excluding project-level)
        count(*) filter (where 
          member not like 'project%' and (
            (member like 'user:%' and split_part(member, 'user:', 2) = any(($1)::text[]))
            or
            (member like 'group:%' and split_part(member, 'group:', 2) = any(($2)::text[]))
            or
            (member like 'domain:%' and split_part(member, 'domain:', 2) = any(($3)::text[]))
            or
            (member like 'serviceAccount:%' and split_part(member, 'serviceAccount:', 2) = any(($4)::text[]))
          )
        ) as trusted_members,
        -- Collect untrusted members for alarm messages
        array_agg(distinct member) filter (where 
          member not like 'project%' and not (
          (member like 'user:%' and split_part(member, 'user:', 2) = any(($1)::text[]))
            or
            (member like 'group:%' and split_part(member, 'group:', 2) = any(($2)::text[]))
            or
            (member like 'domain:%' and split_part(member, 'domain:', 2) = any(($3)::text[]))
            or
            (member like 'serviceAccount:%' and split_part(member, 'serviceAccount:', 2) = any(($4)::text[]))
          )
        ) as untrusted_members
      from
        gcp_pubsub_subscription,
        jsonb_array_elements(iam_policy -> 'bindings') as binding,
        jsonb_array_elements_text(binding -> 'members') as member
      group by
        name
    )
    select
      r.name as resource,
      case
        -- SKIP: When no members exist
        when (r.iam_policy -> 'bindings') is null or jsonb_array_length(r.iam_policy -> 'bindings') = 0 then 'skip'
        -- INFO: When only project-level roles are assigned
        when p.total_members = p.project_members then 'info'
        -- OK: When all non-project members are trusted
        when p.untrusted_members is null and (p.trusted_members > 0 or p.project_members > 0) then 'ok'
        -- ALARM: When there are untrusted members
        else 'alarm'
      end as status,
      case
        when (r.iam_policy -> 'bindings') is null or jsonb_array_length(r.iam_policy -> 'bindings') = 0 then title || ' has no IAM policy members.'
        when p.total_members = p.project_members then title || ' only has project-level role assignments (' || p.project_members || ' members).'
        when p.untrusted_members is null and (p.trusted_members > 0 or p.project_members > 0) then title || ' policy only grants access to trusted principals (' || coalesce(p.trusted_members, 0) || ' trusted + ' || coalesce(p.project_members, 0) || ' project-level).'
        else title || ' policy contains ' || coalesce(array_length(p.untrusted_members, 1), 0) || ' untrusted member(s): ' || array_to_string(p.untrusted_members, ', ')
      end as reason,
      r.project,
      r.location
    from
      gcp_pubsub_subscription as r
      left join policy_analysis as p on p.resource_id = r.name
    where
      -- Only check resources where we have access to IAM policy
      r.iam_policy is not null;