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;