Query: ssl_certificate_record

select
  common_name as "Common Name",
  (select string_agg(alt_name, ', ') from jsonb_array_elements_text(dns_names) as alt_name) as "Alternative Names",
  serial_number as "Serial Number",
  TO_CHAR(not_before, 'Dy, DD Mon YYYY HH24:MI:SS TZ') as "Valid From",
  TO_CHAR(not_after, 'Dy, DD Mon YYYY HH24:MI:SS TZ') || ' (expires in ' || date_trunc('day', age(not_after, now())) || ')' as "Valid Until",
  public_key_algorithm || ' ' || public_key_length || ' bits' as "Key",
  issuer_name as "Issuer",
  signature_algorithm as "Signature Algorithm",
  case
    when revoked then 'Revoked'
    else 'Not Revoked'
  end as "Revocation Status",
  case (select count(*) from net_dns_record where domain = $1 and type = 'CAA')
    when null then 'No'
    when 0 then 'No'
    else 'Yes'
  end as "DNS CAA"
from
  net_certificate
where
  domain = $1;