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;