Query: s3_bucket_restrict_public_write_access

with public_acl as (
  select
    distinct name
  from
    aws_s3_bucket,
    jsonb_array_elements(acl -> 'Grants') as grants
  where
    (grants -> 'Grantee' ->> 'URI' = 'http://acs.amazonaws.com/groups/global/AllUsers'
    or grants -> 'Grantee' ->> 'URI' = 'http://acs.amazonaws.com/groups/global/AuthenticatedUsers')
    and (
      grants ->> 'Permission' = 'FULL_CONTROL'
      or grants ->> 'Permission' = 'WRITE_ACP'
      or grants ->> 'Permission' = 'WRITE'
    )
), write_access_policy as (
    select
      distinct name
    from
      aws_s3_bucket,
      jsonb_array_elements(policy_std -> 'Statement') as s,
      jsonb_array_elements_text(s -> 'Action') as action
    where
      s ->> 'Effect' = 'Allow'
      and (
        s -> 'Principal' -> 'AWS' = '["*"]'
        or s ->> 'Principal' = '*'
      )
      and (
        action = '*'
        or action = '*:*'
        or action = 's3:*'
        or action ilike 's3:put%'
        or action ilike 's3:delete%'
        or action ilike 's3:create%'
        or action ilike 's3:update%'
        or action ilike 's3:replicate%'
        or action ilike 's3:restore%'
      )
)
select
  b.arn as resource,
  case
    when (block_public_acls or a.name is null) and not bucket_policy_is_public then 'ok'
    when (block_public_acls or a.name is null) and (bucket_policy_is_public and block_public_policy) then 'ok'
    when bucket_policy_is_public and p.name is null then 'ok'
    else 'alarm'
  end status,
  case
    when (block_public_acls or a.name is null ) and not bucket_policy_is_public then b.title || ' not publicly writable.'
    when (block_public_acls or a.name is null) and (bucket_policy_is_public and block_public_policy) then b.title || ' not publicly writable.'
    when (block_public_acls or a.name is null) and (bucket_policy_is_public and p.name is null) then b.title || ' not publicly writable.'
    else b.title || ' publicly writable.'
  end reason
  
  , b.region, b.account_id
from
  aws_s3_bucket as b
  left join public_acl as a on b.name = a.name
  left join write_access_policy as p on b.name = p.name;