Query: elb_application_network_lb_https_tls_listener_recommended_security_policy

  with lbs as (
    select arn, title, region, account_id, tags, _ctx, 'application'::text as lb_type
    from aws_ec2_application_load_balancer
    union all
    select arn, title, region, account_id, tags, _ctx, 'network'::text as lb_type
    from aws_ec2_network_load_balancer
  ),
  listeners as (
    select
      l.arn as listener_arn,
      l.load_balancer_arn as lb_arn,
      l.protocol,
      l.port,
      l.ssl_policy,
      lb.title,
      lb.region,
      lb.account_id,
      lb.tags,
      lb._ctx,
      lb.lb_type
    from aws_ec2_load_balancer_listener l
    join lbs lb on lb.arn = l.load_balancer_arn
  )
select
  l.listener_arn as resource,
  case
    when l.lb_type = 'application' and l.protocol = 'HTTPS'
      then case when l.ssl_policy is null or not (l.ssl_policy = ANY($1::text[])) then 'alarm' else 'ok' end
    when l.lb_type = 'network' and l.protocol = 'TLS'
      then case when l.ssl_policy is null or not (l.ssl_policy = ANY($1::text[])) then 'alarm' else 'ok' end
    else 'alarm'
  end as status,
  case
    when l.lb_type = 'application' and l.protocol = 'HTTPS' and l.ssl_policy is null
      then l.title || ' listener ' || l.port || ' uses HTTPS with no SSL policy.'
    when l.lb_type = 'application' and l.protocol = 'HTTPS' and not (l.ssl_policy = ANY($1::text[]))
      then l.title || ' listener ' || l.port || ' uses HTTPS with non-recommended policy ' || l.ssl_policy || '.'
    when l.lb_type = 'application' and l.protocol = 'HTTPS'
      then l.title || ' listener ' || l.port || ' uses HTTPS with recommended policy ' || l.ssl_policy || '.'
    when l.lb_type = 'network' and l.protocol = 'TLS' and l.ssl_policy is null
      then l.title || ' listener ' || l.port || ' uses TLS with no SSL policy.'
    when l.lb_type = 'network' and l.protocol = 'TLS' and not (l.ssl_policy = ANY($1::text[]))
      then l.title || ' listener ' || l.port || ' uses TLS with non-recommended policy ' || l.ssl_policy || '.'
    when l.lb_type = 'network' and l.protocol = 'TLS'
      then l.title || ' listener ' || l.port || ' uses TLS with recommended policy ' || l.ssl_policy || '.'
    when l.lb_type = 'application'
      then l.title || ' listener ' || l.port || ' uses ' || lower(l.protocol) || ' (expected HTTPS).'
    when l.lb_type = 'network'
      then l.title || ' listener ' || l.port || ' uses ' || lower(l.protocol) || ' (expected TLS).'
  end as reason
  
  , region, account_id
from
  listeners l;