Query: codebuild_project_plaintext_env_variables_no_sensitive_aws_values

with codebuild_projects as (
  select
    *
  from
    terraform_resource
  where
    type = 'aws_codebuild_project'
),
invalid_key_name as (
  select
    distinct address
  from
    codebuild_projects,
    jsonb_array_elements(
      case
        jsonb_typeof(
          attributes_std -> 'environment' -> 'environment_variable'
        )
        when 'array' then (
          attributes_std -> 'environment' -> 'environment_variable'
        )
        else null
      end
    ) as env
  where
    env ->> 'name' ilike any (
      ARRAY [ '%AWS_ACCESS_KEY_ID%',
      '%AWS_SECRET_ACCESS_KEY%',
      '%PASSWORD%' ]
    )
    and env ->> 'type' = 'PLAINTEXT'
)
select
  a.address as resource,
  case
    when b.address is not null
    or (
      (
        attributes_std -> 'environment' -> 'environment_variable' ->> 'name' ilike any (
          ARRAY [ '%AWS_ACCESS_KEY_ID%',
          '%AWS_SECRET_ACCESS_KEY%',
          '%PASSWORD%' ]
        )
      )
      and attributes_std -> 'environment' -> 'environment_variable' ->> 'type' = 'PLAINTEXT'
    ) then 'alarm'
    else 'ok'
  end status,
  split_part(a.address, '.', 2) || case
    when b.address is not null
    or (
      (
        attributes_std -> 'environment' -> 'environment_variable' ->> 'name' ilike any (
          ARRAY [ '%AWS_ACCESS_KEY_ID%',
          '%AWS_SECRET_ACCESS_KEY%',
          '%PASSWORD%' ]
        )
      )
      and attributes_std -> 'environment' -> 'environment_variable' ->> 'type' = 'PLAINTEXT'
    ) then ' has plaintext environment variables with sensitive AWS values'
    else ' has no plaintext environment variables with sensitive AWS values'
  end || '.' reason,
  path || ':' || start_line
from
  codebuild_projects as a
  left join invalid_key_name as b on a.address = b.address;