Query: ec2_instance_no_iam_role_with_write_access_to_resource_based_policies

with iam_roles as (
  select
    r.arn as role_arn,
    i.arn as intance_arn
  from
    aws_iam_role as r,
    jsonb_array_elements_text(instance_profile_arns) as p
    left join aws_ec2_instance as i on p = i.iam_instance_profile_arn
  where
    i.arn is not null
),
iam_role_with_permission as (
  select
    arn
  from
    aws_iam_role,
    jsonb_array_elements(assume_role_policy_std -> 'Statement') as s,
    jsonb_array_elements_text(s -> 'Principal' -> 'Service') as service,
    jsonb_array_elements_text(s -> 'Action') as action
  where
    arn in (
      select
        role_arn
      from
        iam_roles
    )
    and s ->> 'Effect' = 'Allow'
    and service = 'ec2.amazonaws.com'
    and action in (
      'ecr:setrepositorypolicy',
      'serverlessrepo:putapplicationpolicy',
      'backup:putbackupvaultaccesspolicy',
      'efs:putfilesystempolicy',
      'glacier:setvaultaccesspolicy',
      'secretsmanager:putresourcepolicy',
      'events:putpermission',
      'mediastore:putcontainerpolicy',
      'glue:putresourcepolicy',
      'ses:putidentitypolicy',
      'lambda:addpermission',
      'lambda:addlayerversionpermission',
      's3:putbucketpolicy',
      's3:putbucketacl',
      's3:putObject',
      's3:putobjectacl',
      'kms:creategrant',
      'kms:putkeypolicy',
      'es:Updateelasticsearchdomainconfig',
      'sns:addpermission',
      'sqs:addpermission',
      '*:*'
    )
)
select
  i.arn as resource,
  case
    when p.arn is null then 'ok'
    else 'alarm'
  end status,
  case
    when p.arn is null then title || ' has no write access permission to resource based policies.'
    else title || ' has write access permission to resource based policies.'
  end as reason,
  i.account_id
from
  aws_ec2_instance as i
  left join iam_roles as r on r.intance_arn = i.arn
  left join iam_role_with_permission as p on p.arn = r.role_arn;