turbot/net_insights

Query: ssl_certificate_report

Usage

powerpipe query net_insights.query.ssl_certificate_report

SQL

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

Params

ArgsNameDefaultDescriptionVariable
$1domain_input

    Dashboards

    The query is used in the dashboards: