Query: ec2_instance_no_iam_with_write_level_access

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 ('iam:addclientidtoopenidconnectprovider','iam:addroletoinstanceprofile','iam:addusertogroup','iam:changepassword','iam:createaccesskey','iam:createaccountalias','iam:creategroup','iam:createinstanceprofile','iam:createloginprofile','iam:createopenidconnectprovider','iam:createrole','iam:createsamlprovider','iam:createservicelinkedrole','iam:createservicespecificcredential','iam:createuser','iam:createvirtualmfadevice','iam:deactivatemfadevice','iam:deleteaccesskey','iam:deleteaccountalias','iam:deletegroup','iam:deleteinstanceprofile','iam:deleteloginprofile','iam:deleteopenidconnectprovider','iam:deleterole','iam:deletesamlprovider','iam:deletesshpublickey','iam:deleteservercertificate','iam:deleteservicelinkedrole','iam:deleteservicespecificcredential','iam:deletesigningcertificate','iam:deleteUser','iam:deletevirtualmfadevice','iam:enablemfadevice','iam:passrole','iam:removeclientidfromopenidconnectprovider','iam:removerolefrominstanceprofile','iam:removeuserfromgroup','iam:resetservicespecificcredential','iam:resyncmfadevice','iam:setsecuritytokenservicepreferences','iam:updateaccesskey','iam:updateaccountpasswordpolicy','iam:updategroup','iam:updateloginprofile','iam:updateopenidconnectproviderthumbprint','iam:updaterole','iam:updateroledescription','iam:updatesamlprovider','iam:updatesshpublicKey','iam:updateservercertificate','iam:updateservicespecificcredential','iam:updatesigningcertificate','iam:updateuser','iam:uploadsshpublicKey','iam:uploadservercertificate','iam:uploadsigningcertificate','*:*')
      )
    )
)
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 IAM rite level access.'
    else title || ' has IAM write level access.'
  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;