Query: iam_policy_statement

with policy as (
  select
    distinct on (arn) *
  from
    aws_iam_policy
  where
    arn = $1
)
select
  coalesce(t.stmt ->> 'Sid', concat('[', i :: text, ']')) as "Statement",
  t.stmt ->> 'Effect' as "Effect",
  action as "Action",
  notaction as "NotAction",
  resource as "Resource",
  notresource as "NotResource",
  t.stmt ->> 'Condition' as "Condition"
from
  policy as p,
  --aws_iam_policy as p,
  jsonb_array_elements(p.policy_std -> 'Statement') with ordinality as t(stmt, i)
  left join jsonb_array_elements_text(t.stmt -> 'Action') as action on true
  left join jsonb_array_elements_text(t.stmt -> 'NotAction') as notaction on true
  left join jsonb_array_elements_text(t.stmt -> 'Resource') as resource on true
  left join jsonb_array_elements_text(t.stmt -> 'NotResource') as notresource on true