Query: ssl_certificate_report

with domain_list as (
  select distinct domain from net_dns_record where domain in ($1) order by domain
)
select
  'SSL certificate should be valid' as "Recommendation",
  case
    when now() < not_before then '❌'
    else '✅'
  end as "Status",
  case
    when now() < not_before then 'Certificate is not yet valid.'
    else 'Certificate is valid.'
  end  || ' It is recommended that the certificate is not being used before the time when the certificate is valid from.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'SSL certificate should not be expired' as "Recommendation",
  case
    when now() > not_after then '❌'
    else '✅'
  end as "Status",
  case
    when now() > not_after then 'Certificate is expired.'
    else 'Certificate is yet to expire in ' || date_trunc('day', age(not_after, now())) || '.'
  end  || ' SSL certificates ensure secure connections between a server and other web entities and provide validation that a browser is indeed communicating with a validated website server. Once it expires, your website is no longer recognized on the web as safe and secure and it is vulnerable to cyber-attacks.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'SSL certificate should not be self-signed' as "Recommendation",
  case
    when common_name = issuer_name then '❌'
    else '✅'
  end as "Status",
  case
    when common_name = issuer_name then 'Certificate is self-signed.'
    else 'Certificate is not self-signed.'
  end  || ' Self-signed certificates contain private and public keys within the same entity, and they cannot be revoked, thus making it difficult to detect security compromises. It is recommended not to use self-signed certificate since it encourage dangerous public browsing behavior.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'SSL certificate should not be revoked' as "Recommendation",
  case
    when revoked then '❌'
    else '✅'
  end as "Status",
  case
    when revoked then 'Certificate was revoked.'
    else 'Certificate is not revoked.'
  end  || ' Check for certificate revocation on a server describes if the certificate being used has been revoked by the certificate authority before it was set to expire. It is recommended not to use revoked certificate since they are no longer trustworthy.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'SSL certificate should not use insecure certificate algorithms (e.g., MD2, MD5, SHA1)' as "Recommendation",
  case
    when signature_algorithm like any (array['%SHA1%', '%MD2%', '%MD5%']) then '❌'
    else '✅'
  end as "Status",
  'Certificate uses ' || signature_algorithm || ' signature algorithm(s). MD2 and MD5 are part of the Message Digest Algorithm family which was created to verify the integrity of any message or file that is hashed. It has been cryptographically broken which means they are vulnerable to collision attacks and hence considered insecure. Also SHA1 is considered cryptographically weak. It is recommended not to use these insecure signatures.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'SSL server should have CAA record for certificate' as "Recommendation",
  case (select count(*) from net_dns_record where domain = $1 and type = 'CAA')
    when null then '❌'
    when 0 then '❌'
    else '✅'
  end as "Status",
  case (select count(*) from net_dns_record where domain = $1 and type = 'CAA')
    when null then 'CAA record not found.'
    when 0 then 'CAA record not found.'
    else 'CAA record found.'
  end
    || ' The CAA record is a type of DNS record used to provide additional confirmation for the Certification Authority (CA) when validating an SSL certificate. With CAA in place, the attack surface for fraudulent certificates is reduced, effectively making sites more secure.' as "Result"
from
  domain_list