Query: ssl_server_configuration_checks

with domain_list as (
  select $1 as domain, $1 || ':443' as address
),
check_insecure_protocol as (
  select
    address,
    count(*)
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and version in ('TLS v1.0', 'TLS v1.1')
    and handshake_completed
  group by address
),
check_insecure_cipher as (
  select
    address,
    count(*)
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and cipher_suite_name in ('TLS_RSA_WITH_RC4_128_SHA', 'TLS_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_RSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_ECDSA_WITH_RC4_128_SHA', 'TLS_ECDHE_RSA_WITH_RC4_128_SHA', 'TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256')
    and handshake_completed
  group by address
),
check_pfs_cipher as (
  select
    address,
    count(*)
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and cipher_suite_name in ('TLS_AES_128_GCM_SHA256', 'TLS_AES_256_GCM_SHA384', 'TLS_CHACHA20_POLY1305_SHA256', 'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256', 'TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256', 'TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256')
    and handshake_completed
  group by address
),
all_ecdhe_ciphers as (
  select
    address,
    version,
    cipher_suite_name
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and version in ('TLS v1.3', 'TLS v1.2')
    and cipher_suite_name in ('TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256', 'TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384', 'TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256', 'TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384', 'TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256', 'TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256', 'TLS_AES_128_GCM_SHA256', 'TLS_AES_256_GCM_SHA384', 'TLS_CHACHA20_POLY1305_SHA256')
    and handshake_completed
),
check_rc4_cipher as (
  select
    address,
    count(*)
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and version in ('TLS v1.2', 'TLS v1.1', 'TLS v1.0')
    and cipher_suite_name in ('TLS_RSA_WITH_RC4_128_SHA', 'TLS_ECDHE_ECDSA_WITH_RC4_128_SHA', 'TLS_ECDHE_RSA_WITH_RC4_128_SHA')
    and handshake_completed
  group by address
),
check_cbc_cipher as (
  select
    address,
    count(*)
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and version in ('TLS v1.2', 'TLS v1.1', 'TLS v1.0')
    and cipher_suite_name in ('TLS_RSA_WITH_AES_128_CBC_SHA', 'TLS_RSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA', 'TLS_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_RSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256')
    and handshake_completed
  group by address
)
select
  'Use complete chain of trusted certificates' as "Recommendation",
  case
    when chain @> '[{"is_certificate_authority": true}]' then '✅'
    when jsonb_array_length(chain) >= 2 then '✅'
    else '❌'
  end as "Status",
  common_name || ' has ' || jsonb_array_length(chain) || ' certificate(s) along with the server certificates. An invalid certificate chain effectively renders the server certificate invalid and results in browser warnings. End-entity SSL/TLS certificates are generally signed by intermediate certificates rather than a CA’s root key. It is recommended to use two or more certificates to build a complete chain of trust.' as "Result"
from
  net_certificate
where
  domain = $1
UNION
select
  'Use secure protocols' as "Recommendation",
  case
    when i.address is null or i.count < 1 then '✅'
    else '❌'
  end as "Status",
  case
    when i.address is null or i.count < 1 then d.domain || ' doesn''t support insecure protocols.'
    else d.domain || ' supports insecure protocols.'
  end
    || ' There are six protocols in the SSL/TLS family: SSL v2, SSL v3, TLS v1.0, TLS v1.1, TLS v1.2, and TLS v1.3. It is recommended to use secure protocols (e.g., TLS v1.2, TLS v1.3) since these versions offer modern authenticated encryption, improved latency and don''t have obsolete features like cipher suites. TLS v1.0 and TLS v1.1 are legacy protocols and shouldn''t be used.' as "Result"
from
  domain_list as d
  left join check_insecure_protocol as i on d.address = i.address
UNION
select
  'Use secure cipher suites' as "Recommendation",
  case
    when i.address is null or i.count < 1 then '✅'
    else '❌'
  end as "Status",
  case
    when i.address is null or i.count < 1 then d.domain || ' uses secure cipher suites.'
    else d.domain || ' does not use secure cipher suites.'
  end
    || ' A cipher suite is a set of cryptographic algorithms. The set of algorithms that cipher suites usually contain includes: a key exchange algorithm, a bulk encryption algorithm, and a message authentication code (MAC) algorithm. It is recommended to use secure ciphers like Authenticated Encryption with Associated Data (AEAD) cipher suites and Perfect Forward Secrecy (PFS) ciphers. The following cipher suites are considered insecure: TLS_RSA_WITH_RC4_128_SHA, TLS_RSA_WITH_3DES_EDE_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_ECDSA_WITH_RC4_128_SHA, TLS_ECDHE_RSA_WITH_RC4_128_SHA, TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256.' as "Result"
from
  domain_list as d
  left join check_insecure_cipher as i on d.address = i.address
UNION
select
  'Use perfect forward secrecy' as "Recommendation",
  case
    when i.address is not null and i.count > 1 then '✅'
    else '❌'
  end as "Status",
  case
    when i.address is not null and i.count > 1 then d.domain || ' cipher suites provide forward secrecy.'
    else d.domain || ' cipher suites do not provide forward secrecy.'
  end
    || ' In cryptography, forward secrecy (FS), also known as perfect forward secrecy (PFS), is a feature of specific key agreement protocols that gives assurances that session keys will not be compromised even if long-term secrets are used in the session key exchange are compromised.' as "Result"
from
  domain_list as d
  left join check_pfs_cipher as i on d.address = i.address
UNION
  select
    'Use strong key exchange mechanism' as "Recommendation",
    case
      when (select count(*) from all_ecdhe_ciphers where address = d.address and version = 'TLS v1.3') > 0 then '✅'
      when (select count(*) from all_ecdhe_ciphers where address = d.address and version = 'TLS v1.2') > 0 then '✅'
else '❌'
    end as "Status",
    case
      when (select count(*) from all_ecdhe_ciphers where address = d.address and version = 'TLS v1.3') > 0 or (select count(*) from all_ecdhe_ciphers where address = d.address and version = 'TLS v1.2' and split_part(cipher_suite_name, '_', 2) = 'ECDHE') > 0
    then d.domain || ' uses strong key exchange mechanism.'
    else d.domain || ' does not use strong key exchange mechanism.'
    end
      || ' It is recommended to use a strong key exchange mechanism to keep data being transferred across the network more secure. Both parties agree on a single cipher suite and generate the session keys (symmetric keys) to encrypt and decrypt the information during an SSL session.' as "Result"
  from
    domain_list as d
UNION
  select
    'Avoid using RC4 ciphers' as "Recommendation",
    case
      when i.address is null then '✅'
      when i.count < 1 then '✅'
      else '❌'
    end as "Status",
    case
      when i.address is null or i.count < 1 then d.domain || ' does not use RC4 cipher suites.'
      else d.domain || ' uses RC4 cipher suites.'
    end
      || ' RC4 is a stream cipher and is more malleable than common block ciphers. Encryption is vulnerable to cyber-attacks if not used together with a strong message authentication code (MAC). RC4 is demonstrably broken, weak and unsafe to use in TLS as currently implemented.' as "Result"
  from
    domain_list as d
    left join check_rc4_cipher as i on d.address = i.address
UNION
  select
    'Avoid using CBC ciphers' as "Recommendation",
    case
      when i.address is null then '✅'
      when i.count < 1 then '✅'
      else '❌'
    end as "Status",
    case
      when i.address is null or i.count < 1 then d.domain || ' does not use CBC cipher suites.'
      else d.domain || ' uses CBC cipher suites.'
    end
      || ' Cipher block chaining (CBC) is a mode of operation for a block cipher in which a sequence of bits are encrypted as a single unit, or block, with a cipher key applied to the entire block. The problem with CBC mode is that the decryption of blocks is dependent on the previous ciphertext block, which means attackers can manipulate the decryption of a block by tampering with the previous block using the commutative property of XOR. If the server uses TLS 1.2 or TLS 1.1, or TLS 1.0 with CBC cipher modes, there is a chance that the server gets vulnerable to Zombie POODLE, GOLDENDOODLE, 0-Length OpenSSL and Sleeping POODLE.' as "Result"
  from
    domain_list as d
    left join check_cbc_cipher as i on d.address = i.address