bgpwtf/cccampix/verifier/model/pgp.go

package model

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"time"
)

func (s *sqlModel) UpdatePGPKey(ctx context.Context, key *PeerPGPKey) error {
	q := `
		INSERT INTO peer_pgp_keys
			(peer_id, fingerprint, time_created, state)
		SELECT
			peers.id, :fingerprint, :time_created, 'unchecked'
		FROM peers
		WHERE peers.asn = :asn
		ON CONFLICT (peer_id)
		DO UPDATE SET
			fingerprint = :fingerprint,
			time_created = :time_created,
			state = 'unchecked'
		WHERE peer_pgp_keys.fingerprint != excluded.fingerprint
	`
	data := &sqlPeerPGPKey{
		Fingerprint: key.Fingerprint,
		ASN:         fmt.Sprintf("%d", key.PeerASN),
		TimeCreated: time.Now().UnixNano(),
	}
	if _, err := s.db.NamedExecContext(ctx, q, data); err != nil {
		return fmt.Errorf("INSERT peer_pgp_keys: %v", err)
	}
	return nil
}

func (s *sqlModel) GetPGPKeysRequiringAttention(ctx context.Context) ([]*PeerPGPKey, error) {
	q := `
		SELECT
			peer_pgp_keys.fingerprint "fingerprint",
			peer_pgp_keys.state "state",
			peers.asn "asn"
		FROM peer_pgp_keys
		LEFT JOIN peers
		ON peers.id = peer_pgp_keys.peer_id
		WHERE
			peer_pgp_keys.state = 'unchecked'
		OR
			peer_pgp_keys.state = 'known'
		OR (
			peer_pgp_keys.state = 'unknown' AND
			peer_pgp_keys.time_created > $1
		)
	`

	data := []sqlPeerPGPKey{}
	timestamp := time.Now().Add(-time.Hour).UnixNano()
	if err := s.db.SelectContext(ctx, &data, q, timestamp); err != nil {
		return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err)
	}

	res := make([]*PeerPGPKey, len(data))
	for i, datum := range data {
		asn, err := strconv.ParseInt(datum.ASN, 10, 64)
		if err != nil {
			return nil, fmt.Errorf("data corruption: peer_pgp_keys as ASN %q", datum.ASN)
		}
		res[i] = &PeerPGPKey{
			Fingerprint: datum.Fingerprint,
			State:       datum.State,
			PeerASN:     asn,
		}
	}

	return res, nil
}

func (s *sqlModel) ValidatePGPKeys(ctx context.Context, positive, negative []string) error {
	tx := s.db.MustBeginTx(ctx, &sql.TxOptions{})
	defer tx.Rollback()

	timestamp := time.Now().UnixNano()

	for _, p := range positive {
		q := `
			UPDATE
				peer_pgp_keys
			SET
				state = 'known',
				time_created = $2
			WHERE
				fingerprint = $1	
		`

		if _, err := tx.ExecContext(ctx, q, p, timestamp); err != nil {
			return fmt.Errorf("UPDATE peer_pgp_keys: %v", err)
		}
	}

	for _, n := range negative {
		q := `
			UPDATE
				peer_pgp_keys
			SET
				state = 'unknown',
				time_created = $2
			WHERE
				fingerprint = $1	
		`

		if _, err := tx.ExecContext(ctx, q, n, timestamp); err != nil {
			return fmt.Errorf("UPDATE peer_pgp_keys: %v", err)
		}
	}

	return tx.Commit()
}

func (s *sqlModel) GetPeerPGPKey(ctx context.Context, asn int64) (*PeerPGPKey, error) {
	q := `
		SELECT peer_pgp_keys.fingerprint
		FROM peer_pgp_keys
		LEFT JOIN peers
		ON peers.id = peer_pgp_keys.peer_id
		WHERE peers.asn = $1
	`
	data := []*PeerPGPKey{}
	if err := s.db.SelectContext(ctx, &data, q, asn); err != nil {
		return nil, fmt.Errorf("SELECT peer_pgp_keys: %v", err)
	}

	if len(data) != 1 {
		return nil, fmt.Errorf("wrong number of peer_pgp_keys (%d)", len(data))
	}

	return data[0], nil
}