#! /usr/bin/perl -w
#
# @(#)$Id$
# rev 1 EXPERIMENTAL - FOR DEMONSTRATION PURPOSES ONLY
#
# monitor CT logs at crt.sh via SQL queries for recent issues in
# your domain(s)
# default: nikhef.nl (of course :)
# prerequisited: the psql PostgreSQL client
#
# - standard blurb -----------------------------------------------------------
# Copyright 2020 David Groep, Nikhef, Amsterdam
# 
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#     http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ----------------------------------------------------------------------------
#
use strict;
use POSIX;
use Getopt::Long qw(:config no_ignore_case bundling);
use vars qw/ $help $verb $starttime $domain $days /;

local $starttime;
local $domain="nikhef.nl";
local $days=2;

my $help           = undef;
my $verb           = 0;

GetOptions( 
    "s|starttime=s" => \$starttime, 
    "d|days=i" => \$days, 
    "h|help" => \$help, 
    "verbose|v+" => \$verb 
    );

$help and die <<EOF;
Usage: $0 [-s starttime YYYY-mm-dd] [-d days] [-h] domain ...

Description:
  dump the certificates issued from public CAs since _starttime_
  for our domains (default: $domain).

  -s YYYY-mm-dd      start time of query (overrides --days)
  -d n               list only new certs in last n days

EOF

$starttime=POSIX::strftime("%Y-%m-%d",gmtime(time()-($days*24*3600))) unless $starttime;
push @ARGV,$domain unless $#ARGV>=0;

my $psql;
open $psql,"|psql -h crt.sh -p 5432 -U guest certwatch" or die "Cannot connect to crt.sh: $!\n";
print $psql "\\pset tuples_only\n";

foreach my $domain ( @ARGV ) {
print $psql "\\echo '===== Certificates issued for $domain logged since $starttime ====='\n";


print $psql <<EOF;
WITH ci AS (
    SELECT min(sub.CERTIFICATE_ID) ID,
           min(sub.ISSUER_CA_ID) ISSUER_CA_ID,
           array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES,
           x509_commonName(sub.CERTIFICATE) COMMON_NAME,
           x509_notBefore(sub.CERTIFICATE) NOT_BEFORE,
           x509_notAfter(sub.CERTIFICATE) NOT_AFTER,
           encode(x509_serialNumber(sub.CERTIFICATE), 'hex') SERIAL_NUMBER
        FROM (SELECT *
                  FROM certificate_and_identities cai
                  WHERE 
                      plainto_tsquery('certwatch', '$domain') \@\@ identities(cai.CERTIFICATE)
                      AND cai.NAME_VALUE ILIKE ('%$domain%')
                      AND cai.NAME_TYPE = 'san:dNSName' -- dNSName
                      AND coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) >= date_trunc('year', now() AT TIME ZONE 'UTC')
                      AND x509_notAfter(cai.CERTIFICATE) >= now() AT TIME ZONE 'UTC'
                  LIMIT 10000
             ) sub
        GROUP BY sub.CERTIFICATE
)
SELECT ci.ISSUER_CA_ID CA_ID,
        substring(ca.NAME for 20) ISSUER_NAME,
        ci.COMMON_NAME,
        array_to_string(ci.NAME_VALUES, chr(10)) NAME_VALUE,
        ci.ID ID,
        to_char(le.ENTRY_TIMESTAMP,'YYYY-MM-DD') CT_ENTRY,
        to_char(ci.NOT_AFTER,'YYYY-MM-DD') VALID_UNTIL
    FROM ci
            LEFT JOIN LATERAL (
                SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP
                    FROM ct_log_entry ctle
                    WHERE ctle.CERTIFICATE_ID = ci.ID
            ) le ON TRUE,
         ca
    WHERE ci.ISSUER_CA_ID = ca.ID
        AND le.ENTRY_TIMESTAMP >= '$starttime'
    ORDER BY le.ENTRY_TIMESTAMP DESC NULLS LAST;
EOF
sleep(1);

}

close $psql;