Source file src/github.com/letsencrypt/boulder/sa/saro.go

Documentation: github.com/letsencrypt/boulder/sa

     1  package sa
     2  
     3  import (
     4  	"context"
     5  	"errors"
     6  	"fmt"
     7  	"math/big"
     8  	"net"
     9  	"regexp"
    10  	"strings"
    11  	"sync"
    12  	"time"
    13  
    14  	"github.com/jmhodges/clock"
    15  	"github.com/prometheus/client_golang/prometheus"
    16  	"google.golang.org/protobuf/types/known/emptypb"
    17  	"google.golang.org/protobuf/types/known/timestamppb"
    18  	"gopkg.in/go-jose/go-jose.v2"
    19  
    20  	"github.com/letsencrypt/boulder/core"
    21  	corepb "github.com/letsencrypt/boulder/core/proto"
    22  	"github.com/letsencrypt/boulder/db"
    23  	berrors "github.com/letsencrypt/boulder/errors"
    24  	bgrpc "github.com/letsencrypt/boulder/grpc"
    25  	"github.com/letsencrypt/boulder/identifier"
    26  	blog "github.com/letsencrypt/boulder/log"
    27  	sapb "github.com/letsencrypt/boulder/sa/proto"
    28  )
    29  
    30  var (
    31  	validIncidentTableRegexp = regexp.MustCompile(`^incident_[0-9a-zA-Z_]{1,100}$`)
    32  )
    33  
    34  type certCountFunc func(ctx context.Context, db db.Selector, domain string, timeRange *sapb.Range) (int64, time.Time, error)
    35  
    36  // SQLStorageAuthorityRO defines a read-only subset of a Storage Authority
    37  type SQLStorageAuthorityRO struct {
    38  	sapb.UnimplementedStorageAuthorityReadOnlyServer
    39  
    40  	dbReadOnlyMap  *db.WrappedMap
    41  	dbIncidentsMap *db.WrappedMap
    42  
    43  	// For RPCs that generate multiple, parallelizable SQL queries, this is the
    44  	// max parallelism they will use (to avoid consuming too many MariaDB
    45  	// threads).
    46  	parallelismPerRPC int
    47  
    48  	// lagFactor is the amount of time we're willing to delay before retrying a
    49  	// request that may have failed due to replication lag. For example, a user
    50  	// might create a new account and then immediately create a new order, but
    51  	// validating that new-order request requires reading their account info from
    52  	// a read-only database replica... which may not have their brand new data
    53  	// yet. This value should be less than, but about the same order of magnitude
    54  	// as, the observed database replication lag.
    55  	lagFactor time.Duration
    56  
    57  	// We use function types here so we can mock out this internal function in
    58  	// unittests.
    59  	countCertificatesByName certCountFunc
    60  
    61  	clk clock.Clock
    62  	log blog.Logger
    63  
    64  	// lagFactorCounter is a Prometheus counter that tracks the number of times
    65  	// we've retried a query inside of GetRegistration, GetOrder, and
    66  	// GetAuthorization2 due to replication lag. It is labeled by method name
    67  	// and whether data from the retry attempt was found, notfound, or some
    68  	// other error was encountered.
    69  	lagFactorCounter *prometheus.CounterVec
    70  }
    71  
    72  // NewSQLStorageAuthorityRO provides persistence using a SQL backend for
    73  // Boulder. It will modify the given borp.DbMap by adding relevant tables.
    74  func NewSQLStorageAuthorityRO(
    75  	dbReadOnlyMap *db.WrappedMap,
    76  	dbIncidentsMap *db.WrappedMap,
    77  	stats prometheus.Registerer,
    78  	parallelismPerRPC int,
    79  	lagFactor time.Duration,
    80  	clk clock.Clock,
    81  	logger blog.Logger,
    82  ) (*SQLStorageAuthorityRO, error) {
    83  	lagFactorCounter := prometheus.NewCounterVec(prometheus.CounterOpts{
    84  		Name: "sa_lag_factor",
    85  		Help: "A counter of SA lagFactor checks labelled by method and pass/fail",
    86  	}, []string{"method", "result"})
    87  	stats.MustRegister(lagFactorCounter)
    88  
    89  	ssaro := &SQLStorageAuthorityRO{
    90  		dbReadOnlyMap:     dbReadOnlyMap,
    91  		dbIncidentsMap:    dbIncidentsMap,
    92  		parallelismPerRPC: parallelismPerRPC,
    93  		lagFactor:         lagFactor,
    94  		clk:               clk,
    95  		log:               logger,
    96  		lagFactorCounter:  lagFactorCounter,
    97  	}
    98  
    99  	ssaro.countCertificatesByName = ssaro.countCertificates
   100  
   101  	return ssaro, nil
   102  }
   103  
   104  // GetRegistration obtains a Registration by ID
   105  func (ssa *SQLStorageAuthorityRO) GetRegistration(ctx context.Context, req *sapb.RegistrationID) (*corepb.Registration, error) {
   106  	if req == nil || req.Id == 0 {
   107  		return nil, errIncompleteRequest
   108  	}
   109  
   110  	model, err := selectRegistration(ctx, ssa.dbReadOnlyMap, "id", req.Id)
   111  	if db.IsNoRows(err) && ssa.lagFactor != 0 {
   112  		// GetRegistration is often called to validate a JWK belonging to a brand
   113  		// new account whose registrations table row hasn't propagated to the read
   114  		// replica yet. If we get a NoRows, wait a little bit and retry, once.
   115  		ssa.clk.Sleep(ssa.lagFactor)
   116  		model, err = selectRegistration(ctx, ssa.dbReadOnlyMap, "id", req.Id)
   117  		if err != nil {
   118  			if db.IsNoRows(err) {
   119  				ssa.lagFactorCounter.WithLabelValues("GetRegistration", "notfound").Inc()
   120  			} else {
   121  				ssa.lagFactorCounter.WithLabelValues("GetRegistration", "other").Inc()
   122  			}
   123  		} else {
   124  			ssa.lagFactorCounter.WithLabelValues("GetRegistration", "found").Inc()
   125  		}
   126  	}
   127  	if err != nil {
   128  		if db.IsNoRows(err) {
   129  			return nil, berrors.NotFoundError("registration with ID '%d' not found", req.Id)
   130  		}
   131  		return nil, err
   132  	}
   133  
   134  	return registrationModelToPb(model)
   135  }
   136  
   137  func (ssa *SQLStorageAuthority) GetRegistration(ctx context.Context, req *sapb.RegistrationID) (*corepb.Registration, error) {
   138  	return ssa.SQLStorageAuthorityRO.GetRegistration(ctx, req)
   139  }
   140  
   141  // GetRegistrationByKey obtains a Registration by JWK
   142  func (ssa *SQLStorageAuthorityRO) GetRegistrationByKey(ctx context.Context, req *sapb.JSONWebKey) (*corepb.Registration, error) {
   143  	if req == nil || len(req.Jwk) == 0 {
   144  		return nil, errIncompleteRequest
   145  	}
   146  
   147  	var jwk jose.JSONWebKey
   148  	err := jwk.UnmarshalJSON(req.Jwk)
   149  	if err != nil {
   150  		return nil, err
   151  	}
   152  
   153  	sha, err := core.KeyDigestB64(jwk.Key)
   154  	if err != nil {
   155  		return nil, err
   156  	}
   157  	model, err := selectRegistration(ctx, ssa.dbReadOnlyMap, "jwk_sha256", sha)
   158  	if err != nil {
   159  		if db.IsNoRows(err) {
   160  			return nil, berrors.NotFoundError("no registrations with public key sha256 %q", sha)
   161  		}
   162  		return nil, err
   163  	}
   164  
   165  	return registrationModelToPb(model)
   166  }
   167  
   168  func (ssa *SQLStorageAuthority) GetRegistrationByKey(ctx context.Context, req *sapb.JSONWebKey) (*corepb.Registration, error) {
   169  	return ssa.SQLStorageAuthorityRO.GetRegistrationByKey(ctx, req)
   170  }
   171  
   172  // incrementIP returns a copy of `ip` incremented at a bit index `index`,
   173  // or in other words the first IP of the next highest subnet given a mask of
   174  // length `index`.
   175  // In order to easily account for overflow, we treat ip as a big.Int and add to
   176  // it. If the increment overflows the max size of a net.IP, return the highest
   177  // possible net.IP.
   178  func incrementIP(ip net.IP, index int) net.IP {
   179  	bigInt := new(big.Int)
   180  	bigInt.SetBytes([]byte(ip))
   181  	incr := new(big.Int).Lsh(big.NewInt(1), 128-uint(index))
   182  	bigInt.Add(bigInt, incr)
   183  	// bigInt.Bytes can be shorter than 16 bytes, so stick it into a
   184  	// full-sized net.IP.
   185  	resultBytes := bigInt.Bytes()
   186  	if len(resultBytes) > 16 {
   187  		return net.ParseIP("ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff")
   188  	}
   189  	result := make(net.IP, 16)
   190  	copy(result[16-len(resultBytes):], resultBytes)
   191  	return result
   192  }
   193  
   194  // ipRange returns a range of IP addresses suitable for querying MySQL for the
   195  // purpose of rate limiting using a range that is inclusive on the lower end and
   196  // exclusive at the higher end. If ip is an IPv4 address, it returns that address,
   197  // plus the one immediately higher than it. If ip is an IPv6 address, it applies
   198  // a /48 mask to it and returns the lowest IP in the resulting network, and the
   199  // first IP outside of the resulting network.
   200  func ipRange(ip net.IP) (net.IP, net.IP) {
   201  	ip = ip.To16()
   202  	// For IPv6, match on a certain subnet range, since one person can commonly
   203  	// have an entire /48 to themselves.
   204  	maskLength := 48
   205  	// For IPv4 addresses, do a match on exact address, so begin = ip and end =
   206  	// next higher IP.
   207  	if ip.To4() != nil {
   208  		maskLength = 128
   209  	}
   210  
   211  	mask := net.CIDRMask(maskLength, 128)
   212  	begin := ip.Mask(mask)
   213  	end := incrementIP(begin, maskLength)
   214  
   215  	return begin, end
   216  }
   217  
   218  // CountRegistrationsByIP returns the number of registrations created in the
   219  // time range for a single IP address.
   220  func (ssa *SQLStorageAuthorityRO) CountRegistrationsByIP(ctx context.Context, req *sapb.CountRegistrationsByIPRequest) (*sapb.Count, error) {
   221  	if len(req.Ip) == 0 || req.Range.EarliestNS == 0 || req.Range.LatestNS == 0 {
   222  		return nil, errIncompleteRequest
   223  	}
   224  
   225  	var count int64
   226  	err := ssa.dbReadOnlyMap.SelectOne(
   227  		ctx,
   228  		&count,
   229  		`SELECT COUNT(*) FROM registrations
   230  		 WHERE
   231  		 initialIP = :ip AND
   232  		 :earliest < createdAt AND
   233  		 createdAt <= :latest`,
   234  		map[string]interface{}{
   235  			"ip":       req.Ip,
   236  			"earliest": time.Unix(0, req.Range.EarliestNS),
   237  			"latest":   time.Unix(0, req.Range.LatestNS),
   238  		})
   239  	if err != nil {
   240  		return nil, err
   241  	}
   242  	return &sapb.Count{Count: count}, nil
   243  }
   244  
   245  func (ssa *SQLStorageAuthority) CountRegistrationsByIP(ctx context.Context, req *sapb.CountRegistrationsByIPRequest) (*sapb.Count, error) {
   246  	return ssa.SQLStorageAuthorityRO.CountRegistrationsByIP(ctx, req)
   247  }
   248  
   249  // CountRegistrationsByIPRange returns the number of registrations created in
   250  // the time range in an IP range. For IPv4 addresses, that range is limited to
   251  // the single IP. For IPv6 addresses, that range is a /48, since it's not
   252  // uncommon for one person to have a /48 to themselves.
   253  func (ssa *SQLStorageAuthorityRO) CountRegistrationsByIPRange(ctx context.Context, req *sapb.CountRegistrationsByIPRequest) (*sapb.Count, error) {
   254  	if len(req.Ip) == 0 || req.Range.EarliestNS == 0 || req.Range.LatestNS == 0 {
   255  		return nil, errIncompleteRequest
   256  	}
   257  
   258  	var count int64
   259  	beginIP, endIP := ipRange(req.Ip)
   260  	err := ssa.dbReadOnlyMap.SelectOne(
   261  		ctx,
   262  		&count,
   263  		`SELECT COUNT(*) FROM registrations
   264  		 WHERE
   265  		 :beginIP <= initialIP AND
   266  		 initialIP < :endIP AND
   267  		 :earliest < createdAt AND
   268  		 createdAt <= :latest`,
   269  		map[string]interface{}{
   270  			"earliest": time.Unix(0, req.Range.EarliestNS),
   271  			"latest":   time.Unix(0, req.Range.LatestNS),
   272  			"beginIP":  beginIP,
   273  			"endIP":    endIP,
   274  		})
   275  	if err != nil {
   276  		return nil, err
   277  	}
   278  	return &sapb.Count{Count: count}, nil
   279  }
   280  
   281  func (ssa *SQLStorageAuthority) CountRegistrationsByIPRange(ctx context.Context, req *sapb.CountRegistrationsByIPRequest) (*sapb.Count, error) {
   282  	return ssa.SQLStorageAuthorityRO.CountRegistrationsByIPRange(ctx, req)
   283  }
   284  
   285  // CountCertificatesByNames counts, for each input domain, the number of
   286  // certificates issued in the given time range for that domain and its
   287  // subdomains. It returns a map from domains to counts and a timestamp. The map
   288  // of domains to counts is guaranteed to contain an entry for each input domain,
   289  // so long as err is nil. The timestamp is the earliest time a certificate was
   290  // issued for any of the domains during the provided range of time. Queries will
   291  // be run in parallel. If any of them error, only one error will be returned.
   292  func (ssa *SQLStorageAuthorityRO) CountCertificatesByNames(ctx context.Context, req *sapb.CountCertificatesByNamesRequest) (*sapb.CountByNames, error) {
   293  	if len(req.Names) == 0 || req.Range.EarliestNS == 0 || req.Range.LatestNS == 0 {
   294  		return nil, errIncompleteRequest
   295  	}
   296  
   297  	work := make(chan string, len(req.Names))
   298  	type result struct {
   299  		err      error
   300  		count    int64
   301  		earliest time.Time
   302  		domain   string
   303  	}
   304  	results := make(chan result, len(req.Names))
   305  	for _, domain := range req.Names {
   306  		work <- domain
   307  	}
   308  	close(work)
   309  	var wg sync.WaitGroup
   310  	ctx, cancel := context.WithCancel(ctx)
   311  	defer cancel()
   312  	// We may perform up to 100 queries, depending on what's in the certificate
   313  	// request. Parallelize them so we don't hit our timeout, but limit the
   314  	// parallelism so we don't consume too many threads on the database.
   315  	for i := 0; i < ssa.parallelismPerRPC; i++ {
   316  		wg.Add(1)
   317  		go func() {
   318  			defer wg.Done()
   319  			for domain := range work {
   320  				select {
   321  				case <-ctx.Done():
   322  					results <- result{err: ctx.Err()}
   323  					return
   324  				default:
   325  				}
   326  				count, earliest, err := ssa.countCertificatesByName(ctx, ssa.dbReadOnlyMap, domain, req.Range)
   327  				if err != nil {
   328  					results <- result{err: err}
   329  					// Skip any further work
   330  					cancel()
   331  					return
   332  				}
   333  				results <- result{
   334  					count:    count,
   335  					earliest: earliest,
   336  					domain:   domain,
   337  				}
   338  			}
   339  		}()
   340  	}
   341  	wg.Wait()
   342  	close(results)
   343  
   344  	// Set earliest to the latest possible time, so that we can find the
   345  	// earliest certificate in the results.
   346  	earliest := timestamppb.New(time.Unix(0, req.Range.LatestNS))
   347  	counts := make(map[string]int64)
   348  	for r := range results {
   349  		if r.err != nil {
   350  			return nil, r.err
   351  		}
   352  		counts[r.domain] = r.count
   353  		if !r.earliest.IsZero() && r.earliest.Before(earliest.AsTime()) {
   354  			earliest = timestamppb.New(r.earliest)
   355  		}
   356  	}
   357  
   358  	// If we didn't find any certificates in the range, earliest should be set
   359  	// to a zero value.
   360  	if len(counts) == 0 {
   361  		earliest = &timestamppb.Timestamp{}
   362  	}
   363  	return &sapb.CountByNames{Counts: counts, Earliest: earliest}, nil
   364  }
   365  
   366  func (ssa *SQLStorageAuthority) CountCertificatesByNames(ctx context.Context, req *sapb.CountCertificatesByNamesRequest) (*sapb.CountByNames, error) {
   367  	return ssa.SQLStorageAuthorityRO.CountCertificatesByNames(ctx, req)
   368  }
   369  
   370  func ReverseName(domain string) string {
   371  	labels := strings.Split(domain, ".")
   372  	for i, j := 0, len(labels)-1; i < j; i, j = i+1, j-1 {
   373  		labels[i], labels[j] = labels[j], labels[i]
   374  	}
   375  	return strings.Join(labels, ".")
   376  }
   377  
   378  // GetSerialMetadata returns metadata stored alongside the serial number,
   379  // such as the RegID whose certificate request created that serial, and when
   380  // the certificate with that serial will expire.
   381  func (ssa *SQLStorageAuthorityRO) GetSerialMetadata(ctx context.Context, req *sapb.Serial) (*sapb.SerialMetadata, error) {
   382  	if req == nil || req.Serial == "" {
   383  		return nil, errIncompleteRequest
   384  	}
   385  
   386  	if !core.ValidSerial(req.Serial) {
   387  		return nil, fmt.Errorf("invalid serial %q", req.Serial)
   388  	}
   389  
   390  	recordedSerial := recordedSerialModel{}
   391  	err := ssa.dbReadOnlyMap.SelectOne(
   392  		ctx,
   393  		&recordedSerial,
   394  		"SELECT * FROM serials WHERE serial = ?",
   395  		req.Serial,
   396  	)
   397  	if err != nil {
   398  		if db.IsNoRows(err) {
   399  			return nil, berrors.NotFoundError("serial %q not found", req.Serial)
   400  		}
   401  		return nil, err
   402  	}
   403  
   404  	return &sapb.SerialMetadata{
   405  		Serial:         recordedSerial.Serial,
   406  		RegistrationID: recordedSerial.RegistrationID,
   407  		CreatedNS:      recordedSerial.Created.UnixNano(),
   408  		Created:        timestamppb.New(recordedSerial.Created),
   409  		ExpiresNS:      recordedSerial.Expires.UnixNano(),
   410  		Expires:        timestamppb.New(recordedSerial.Expires),
   411  	}, nil
   412  }
   413  
   414  func (ssa *SQLStorageAuthority) GetSerialMetadata(ctx context.Context, req *sapb.Serial) (*sapb.SerialMetadata, error) {
   415  	return ssa.SQLStorageAuthorityRO.GetSerialMetadata(ctx, req)
   416  }
   417  
   418  // GetCertificate takes a serial number and returns the corresponding
   419  // certificate, or error if it does not exist.
   420  func (ssa *SQLStorageAuthorityRO) GetCertificate(ctx context.Context, req *sapb.Serial) (*corepb.Certificate, error) {
   421  	if req == nil || req.Serial == "" {
   422  		return nil, errIncompleteRequest
   423  	}
   424  	if !core.ValidSerial(req.Serial) {
   425  		return nil, fmt.Errorf("invalid certificate serial %s", req.Serial)
   426  	}
   427  
   428  	cert, err := SelectCertificate(ctx, ssa.dbReadOnlyMap, req.Serial)
   429  	if db.IsNoRows(err) {
   430  		return nil, berrors.NotFoundError("certificate with serial %q not found", req.Serial)
   431  	}
   432  	if err != nil {
   433  		return nil, err
   434  	}
   435  	return bgrpc.CertToPB(cert), nil
   436  }
   437  
   438  func (ssa *SQLStorageAuthority) GetCertificate(ctx context.Context, req *sapb.Serial) (*corepb.Certificate, error) {
   439  	return ssa.SQLStorageAuthorityRO.GetCertificate(ctx, req)
   440  }
   441  
   442  // GetCertificateStatus takes a hexadecimal string representing the full 128-bit serial
   443  // number of a certificate and returns data about that certificate's current
   444  // validity.
   445  func (ssa *SQLStorageAuthorityRO) GetCertificateStatus(ctx context.Context, req *sapb.Serial) (*corepb.CertificateStatus, error) {
   446  	if req.Serial == "" {
   447  		return nil, errIncompleteRequest
   448  	}
   449  	if !core.ValidSerial(req.Serial) {
   450  		err := fmt.Errorf("invalid certificate serial %s", req.Serial)
   451  		return nil, err
   452  	}
   453  
   454  	certStatus, err := SelectCertificateStatus(ctx, ssa.dbReadOnlyMap, req.Serial)
   455  	if db.IsNoRows(err) {
   456  		return nil, berrors.NotFoundError("certificate status with serial %q not found", req.Serial)
   457  	}
   458  	if err != nil {
   459  		return nil, err
   460  	}
   461  
   462  	return bgrpc.CertStatusToPB(certStatus), nil
   463  }
   464  
   465  func (ssa *SQLStorageAuthority) GetCertificateStatus(ctx context.Context, req *sapb.Serial) (*corepb.CertificateStatus, error) {
   466  	return ssa.SQLStorageAuthorityRO.GetCertificateStatus(ctx, req)
   467  }
   468  
   469  // GetRevocationStatus takes a hexadecimal string representing the full serial
   470  // number of a certificate and returns a minimal set of data about that cert's
   471  // current validity.
   472  func (ssa *SQLStorageAuthorityRO) GetRevocationStatus(ctx context.Context, req *sapb.Serial) (*sapb.RevocationStatus, error) {
   473  	if req.Serial == "" {
   474  		return nil, errIncompleteRequest
   475  	}
   476  	if !core.ValidSerial(req.Serial) {
   477  		return nil, fmt.Errorf("invalid certificate serial %s", req.Serial)
   478  	}
   479  
   480  	status, err := SelectRevocationStatus(ctx, ssa.dbReadOnlyMap, req.Serial)
   481  	if err != nil {
   482  		if db.IsNoRows(err) {
   483  			return nil, berrors.NotFoundError("certificate status with serial %q not found", req.Serial)
   484  		}
   485  		return nil, err
   486  	}
   487  
   488  	return status, nil
   489  }
   490  
   491  func (ssa *SQLStorageAuthority) GetRevocationStatus(ctx context.Context, req *sapb.Serial) (*sapb.RevocationStatus, error) {
   492  	return ssa.SQLStorageAuthorityRO.GetRevocationStatus(ctx, req)
   493  }
   494  
   495  func (ssa *SQLStorageAuthorityRO) CountOrders(ctx context.Context, req *sapb.CountOrdersRequest) (*sapb.Count, error) {
   496  	if req.AccountID == 0 || req.Range.EarliestNS == 0 || req.Range.LatestNS == 0 {
   497  		return nil, errIncompleteRequest
   498  	}
   499  
   500  	return countNewOrders(ctx, ssa.dbReadOnlyMap, req)
   501  }
   502  
   503  func (ssa *SQLStorageAuthority) CountOrders(ctx context.Context, req *sapb.CountOrdersRequest) (*sapb.Count, error) {
   504  	return ssa.SQLStorageAuthorityRO.CountOrders(ctx, req)
   505  }
   506  
   507  // CountFQDNSets counts the total number of issuances, for a set of domains,
   508  // that occurred during a given window of time.
   509  func (ssa *SQLStorageAuthorityRO) CountFQDNSets(ctx context.Context, req *sapb.CountFQDNSetsRequest) (*sapb.Count, error) {
   510  	if req.WindowNS == 0 || len(req.Domains) == 0 {
   511  		return nil, errIncompleteRequest
   512  	}
   513  
   514  	var count int64
   515  	err := ssa.dbReadOnlyMap.SelectOne(
   516  		ctx,
   517  		&count,
   518  		`SELECT COUNT(*) FROM fqdnSets
   519  		WHERE setHash = ?
   520  		AND issued > ?`,
   521  		core.HashNames(req.Domains),
   522  		ssa.clk.Now().Add(-time.Duration(req.WindowNS)),
   523  	)
   524  	return &sapb.Count{Count: count}, err
   525  }
   526  
   527  func (ssa *SQLStorageAuthority) CountFQDNSets(ctx context.Context, req *sapb.CountFQDNSetsRequest) (*sapb.Count, error) {
   528  	return ssa.SQLStorageAuthorityRO.CountFQDNSets(ctx, req)
   529  }
   530  
   531  // FQDNSetTimestampsForWindow returns the issuance timestamps for each
   532  // certificate, issued for a set of domains, during a given window of time,
   533  // starting from the most recent issuance.
   534  func (ssa *SQLStorageAuthorityRO) FQDNSetTimestampsForWindow(ctx context.Context, req *sapb.CountFQDNSetsRequest) (*sapb.Timestamps, error) {
   535  	if req.WindowNS == 0 || len(req.Domains) == 0 {
   536  		return nil, errIncompleteRequest
   537  	}
   538  	type row struct {
   539  		Issued time.Time
   540  	}
   541  	var rows []row
   542  	_, err := ssa.dbReadOnlyMap.Select(
   543  		ctx,
   544  		&rows,
   545  		`SELECT issued FROM fqdnSets 
   546  		WHERE setHash = ?
   547  		AND issued > ?
   548  		ORDER BY issued DESC`,
   549  		core.HashNames(req.Domains),
   550  		ssa.clk.Now().Add(-time.Duration(req.WindowNS)),
   551  	)
   552  	if err != nil {
   553  		return nil, err
   554  	}
   555  
   556  	var resultsNS []int64
   557  	var results []*timestamppb.Timestamp
   558  	for _, i := range rows {
   559  		resultsNS = append(resultsNS, i.Issued.UnixNano())
   560  		results = append(results, timestamppb.New(i.Issued))
   561  	}
   562  	return &sapb.Timestamps{TimestampsNS: resultsNS, Timestamps: results}, nil
   563  }
   564  
   565  func (ssa *SQLStorageAuthority) FQDNSetTimestampsForWindow(ctx context.Context, req *sapb.CountFQDNSetsRequest) (*sapb.Timestamps, error) {
   566  	return ssa.SQLStorageAuthorityRO.FQDNSetTimestampsForWindow(ctx, req)
   567  }
   568  
   569  // FQDNSetExists returns a bool indicating if one or more FQDN sets |names|
   570  // exists in the database
   571  func (ssa *SQLStorageAuthorityRO) FQDNSetExists(ctx context.Context, req *sapb.FQDNSetExistsRequest) (*sapb.Exists, error) {
   572  	if len(req.Domains) == 0 {
   573  		return nil, errIncompleteRequest
   574  	}
   575  	exists, err := ssa.checkFQDNSetExists(ctx, ssa.dbReadOnlyMap.SelectOne, req.Domains)
   576  	if err != nil {
   577  		return nil, err
   578  	}
   579  	return &sapb.Exists{Exists: exists}, nil
   580  }
   581  
   582  func (ssa *SQLStorageAuthority) FQDNSetExists(ctx context.Context, req *sapb.FQDNSetExistsRequest) (*sapb.Exists, error) {
   583  	return ssa.SQLStorageAuthorityRO.FQDNSetExists(ctx, req)
   584  }
   585  
   586  // oneSelectorFunc is a func type that matches both borp.Transaction.SelectOne
   587  // and borp.DbMap.SelectOne.
   588  type oneSelectorFunc func(ctx context.Context, holder interface{}, query string, args ...interface{}) error
   589  
   590  // checkFQDNSetExists uses the given oneSelectorFunc to check whether an fqdnSet
   591  // for the given names exists.
   592  func (ssa *SQLStorageAuthorityRO) checkFQDNSetExists(ctx context.Context, selector oneSelectorFunc, names []string) (bool, error) {
   593  	namehash := core.HashNames(names)
   594  	var exists bool
   595  	err := selector(
   596  		ctx,
   597  		&exists,
   598  		`SELECT EXISTS (SELECT id FROM fqdnSets WHERE setHash = ? LIMIT 1)`,
   599  		namehash,
   600  	)
   601  	return exists, err
   602  }
   603  
   604  // PreviousCertificateExists returns true iff there was at least one certificate
   605  // issued with the provided domain name, and the most recent such certificate
   606  // was issued by the provided registration ID. This method is currently only
   607  // used to determine if a certificate has previously been issued for a given
   608  // domain name in order to determine if validations should be allowed during
   609  // the v1 API shutoff.
   610  // TODO(#5816): Consider removing this method, as it has no callers.
   611  func (ssa *SQLStorageAuthorityRO) PreviousCertificateExists(ctx context.Context, req *sapb.PreviousCertificateExistsRequest) (*sapb.Exists, error) {
   612  	if req.Domain == "" || req.RegID == 0 {
   613  		return nil, errIncompleteRequest
   614  	}
   615  
   616  	exists := &sapb.Exists{Exists: true}
   617  	notExists := &sapb.Exists{Exists: false}
   618  
   619  	// Find the most recently issued certificate containing this domain name.
   620  	var serial string
   621  	err := ssa.dbReadOnlyMap.SelectOne(
   622  		ctx,
   623  		&serial,
   624  		`SELECT serial FROM issuedNames
   625  		WHERE reversedName = ?
   626  		ORDER BY notBefore DESC
   627  		LIMIT 1`,
   628  		ReverseName(req.Domain),
   629  	)
   630  	if err != nil {
   631  		if db.IsNoRows(err) {
   632  			return notExists, nil
   633  		}
   634  		return nil, err
   635  	}
   636  
   637  	// Check whether that certificate was issued to the specified account.
   638  	var count int
   639  	err = ssa.dbReadOnlyMap.SelectOne(
   640  		ctx,
   641  		&count,
   642  		`SELECT COUNT(*) FROM certificates
   643  		WHERE serial = ?
   644  		AND registrationID = ?`,
   645  		serial,
   646  		req.RegID,
   647  	)
   648  	if err != nil {
   649  		// If no rows found, that means the certificate we found in issuedNames wasn't
   650  		// issued by the registration ID we are checking right now, but is not an
   651  		// error.
   652  		if db.IsNoRows(err) {
   653  			return notExists, nil
   654  		}
   655  		return nil, err
   656  	}
   657  	if count > 0 {
   658  		return exists, nil
   659  	}
   660  	return notExists, nil
   661  }
   662  
   663  func (ssa *SQLStorageAuthority) PreviousCertificateExists(ctx context.Context, req *sapb.PreviousCertificateExistsRequest) (*sapb.Exists, error) {
   664  	return ssa.SQLStorageAuthorityRO.PreviousCertificateExists(ctx, req)
   665  }
   666  
   667  // GetOrder is used to retrieve an already existing order object
   668  func (ssa *SQLStorageAuthorityRO) GetOrder(ctx context.Context, req *sapb.OrderRequest) (*corepb.Order, error) {
   669  	if req == nil || req.Id == 0 {
   670  		return nil, errIncompleteRequest
   671  	}
   672  
   673  	txn := func(tx db.Executor) (interface{}, error) {
   674  		omObj, err := tx.Get(ctx, orderModel{}, req.Id)
   675  		if err != nil {
   676  			if db.IsNoRows(err) {
   677  				return nil, berrors.NotFoundError("no order found for ID %d", req.Id)
   678  			}
   679  			return nil, err
   680  		}
   681  		if omObj == nil {
   682  			return nil, berrors.NotFoundError("no order found for ID %d", req.Id)
   683  		}
   684  
   685  		order, err := modelToOrder(omObj.(*orderModel))
   686  		if err != nil {
   687  			return nil, err
   688  		}
   689  
   690  		orderExp := time.Unix(0, order.ExpiresNS)
   691  		if orderExp.Before(ssa.clk.Now()) {
   692  			return nil, berrors.NotFoundError("no order found for ID %d", req.Id)
   693  		}
   694  
   695  		v2AuthzIDs, err := authzForOrder(ctx, tx, order.Id)
   696  		if err != nil {
   697  			return nil, err
   698  		}
   699  		order.V2Authorizations = v2AuthzIDs
   700  
   701  		names, err := namesForOrder(ctx, tx, order.Id)
   702  		if err != nil {
   703  			return nil, err
   704  		}
   705  		// The requested names are stored reversed to improve indexing performance. We
   706  		// need to reverse the reversed names here before giving them back to the
   707  		// caller.
   708  		reversedNames := make([]string, len(names))
   709  		for i, n := range names {
   710  			reversedNames[i] = ReverseName(n)
   711  		}
   712  		order.Names = reversedNames
   713  
   714  		// Calculate the status for the order
   715  		status, err := statusForOrder(ctx, tx, order, ssa.clk.Now())
   716  		if err != nil {
   717  			return nil, err
   718  		}
   719  		order.Status = status
   720  
   721  		return order, nil
   722  	}
   723  
   724  	output, err := db.WithTransaction(ctx, ssa.dbReadOnlyMap, txn)
   725  	if (db.IsNoRows(err) || errors.Is(err, berrors.NotFound)) && ssa.lagFactor != 0 {
   726  		// GetOrder is often called shortly after a new order is created, sometimes
   727  		// before the order or its associated rows have propagated to the read
   728  		// replica yet. If we get a NoRows, wait a little bit and retry, once.
   729  		ssa.clk.Sleep(ssa.lagFactor)
   730  		output, err = db.WithTransaction(ctx, ssa.dbReadOnlyMap, txn)
   731  		if err != nil {
   732  			if db.IsNoRows(err) || errors.Is(err, berrors.NotFound) {
   733  				ssa.lagFactorCounter.WithLabelValues("GetOrder", "notfound").Inc()
   734  			} else {
   735  				ssa.lagFactorCounter.WithLabelValues("GetOrder", "other").Inc()
   736  			}
   737  		} else {
   738  			ssa.lagFactorCounter.WithLabelValues("GetOrder", "found").Inc()
   739  		}
   740  	}
   741  	if err != nil {
   742  		return nil, err
   743  	}
   744  
   745  	order, ok := output.(*corepb.Order)
   746  	if !ok {
   747  		return nil, fmt.Errorf("casting error in GetOrder")
   748  	}
   749  
   750  	return order, nil
   751  }
   752  
   753  func (ssa *SQLStorageAuthority) GetOrder(ctx context.Context, req *sapb.OrderRequest) (*corepb.Order, error) {
   754  	return ssa.SQLStorageAuthorityRO.GetOrder(ctx, req)
   755  }
   756  
   757  // GetOrderForNames tries to find a **pending** or **ready** order with the
   758  // exact set of names requested, associated with the given accountID. Only
   759  // unexpired orders are considered. If no order meeting these requirements is
   760  // found a nil corepb.Order pointer is returned.
   761  func (ssa *SQLStorageAuthorityRO) GetOrderForNames(ctx context.Context, req *sapb.GetOrderForNamesRequest) (*corepb.Order, error) {
   762  
   763  	if req.AcctID == 0 || len(req.Names) == 0 {
   764  		return nil, errIncompleteRequest
   765  	}
   766  
   767  	// Hash the names requested for lookup in the orderFqdnSets table
   768  	fqdnHash := core.HashNames(req.Names)
   769  
   770  	// Find a possibly-suitable order. We don't include the account ID or order
   771  	// status in this query because there's no index that includes those, so
   772  	// including them could require the DB to scan extra rows.
   773  	// Instead, we select one unexpired order that matches the fqdnSet. If
   774  	// that order doesn't match the account ID or status we need, just return
   775  	// nothing. We use `ORDER BY expires ASC` because the index on
   776  	// (setHash, expires) is in ASC order. DESC would be slightly nicer from a
   777  	// user experience perspective but would be slow when there are many entries
   778  	// to sort.
   779  	// This approach works fine because in most cases there's only one account
   780  	// issuing for a given name. If there are other accounts issuing for the same
   781  	// name, it just means order reuse happens less often.
   782  	var result struct {
   783  		OrderID        int64
   784  		RegistrationID int64
   785  	}
   786  	var err error
   787  	err = ssa.dbReadOnlyMap.SelectOne(ctx, &result, `
   788  					SELECT orderID, registrationID
   789  					FROM orderFqdnSets
   790  					WHERE setHash = ?
   791  					AND expires > ?
   792  					ORDER BY expires ASC
   793  					LIMIT 1`,
   794  		fqdnHash, ssa.clk.Now())
   795  
   796  	if db.IsNoRows(err) {
   797  		return nil, berrors.NotFoundError("no order matching request found")
   798  	} else if err != nil {
   799  		return nil, err
   800  	}
   801  
   802  	if result.RegistrationID != req.AcctID {
   803  		return nil, berrors.NotFoundError("no order matching request found")
   804  	}
   805  
   806  	// Get the order
   807  	order, err := ssa.GetOrder(ctx, &sapb.OrderRequest{Id: result.OrderID})
   808  	if err != nil {
   809  		return nil, err
   810  	}
   811  	// Only return a pending or ready order
   812  	if order.Status != string(core.StatusPending) &&
   813  		order.Status != string(core.StatusReady) {
   814  		return nil, berrors.NotFoundError("no order matching request found")
   815  	}
   816  	return order, nil
   817  }
   818  
   819  func (ssa *SQLStorageAuthority) GetOrderForNames(ctx context.Context, req *sapb.GetOrderForNamesRequest) (*corepb.Order, error) {
   820  	return ssa.SQLStorageAuthorityRO.GetOrderForNames(ctx, req)
   821  }
   822  
   823  // GetAuthorization2 returns the authz2 style authorization identified by the provided ID or an error.
   824  // If no authorization is found matching the ID a berrors.NotFound type error is returned.
   825  func (ssa *SQLStorageAuthorityRO) GetAuthorization2(ctx context.Context, req *sapb.AuthorizationID2) (*corepb.Authorization, error) {
   826  	if req.Id == 0 {
   827  		return nil, errIncompleteRequest
   828  	}
   829  	obj, err := ssa.dbReadOnlyMap.Get(ctx, authzModel{}, req.Id)
   830  	if db.IsNoRows(err) && ssa.lagFactor != 0 {
   831  		// GetAuthorization2 is often called shortly after a new order is created,
   832  		// sometimes before the order's associated authz rows have propagated to the
   833  		// read replica yet. If we get a NoRows, wait a little bit and retry, once.
   834  		ssa.clk.Sleep(ssa.lagFactor)
   835  		obj, err = ssa.dbReadOnlyMap.Get(ctx, authzModel{}, req.Id)
   836  		if err != nil {
   837  			if db.IsNoRows(err) {
   838  				ssa.lagFactorCounter.WithLabelValues("GetAuthorization2", "notfound").Inc()
   839  			} else {
   840  				ssa.lagFactorCounter.WithLabelValues("GetAuthorization2", "other").Inc()
   841  			}
   842  		} else {
   843  			ssa.lagFactorCounter.WithLabelValues("GetAuthorization2", "found").Inc()
   844  		}
   845  	}
   846  	if err != nil {
   847  		return nil, err
   848  	}
   849  	if obj == nil {
   850  		return nil, berrors.NotFoundError("authorization %d not found", req.Id)
   851  	}
   852  	return modelToAuthzPB(*(obj.(*authzModel)))
   853  }
   854  
   855  func (ssa *SQLStorageAuthority) GetAuthorization2(ctx context.Context, req *sapb.AuthorizationID2) (*corepb.Authorization, error) {
   856  	return ssa.SQLStorageAuthorityRO.GetAuthorization2(ctx, req)
   857  }
   858  
   859  // authzModelMapToPB converts a mapping of domain name to authzModels into a
   860  // protobuf authorizations map
   861  func authzModelMapToPB(m map[string]authzModel) (*sapb.Authorizations, error) {
   862  	resp := &sapb.Authorizations{}
   863  	for k, v := range m {
   864  		authzPB, err := modelToAuthzPB(v)
   865  		if err != nil {
   866  			return nil, err
   867  		}
   868  		resp.Authz = append(resp.Authz, &sapb.Authorizations_MapElement{Domain: k, Authz: authzPB})
   869  	}
   870  	return resp, nil
   871  }
   872  
   873  // GetAuthorizations2 returns any valid or pending authorizations that exist for the list of domains
   874  // provided. If both a valid and pending authorization exist only the valid one will be returned.
   875  func (ssa *SQLStorageAuthorityRO) GetAuthorizations2(ctx context.Context, req *sapb.GetAuthorizationsRequest) (*sapb.Authorizations, error) {
   876  	if len(req.Domains) == 0 || req.RegistrationID == 0 || req.NowNS == 0 {
   877  		return nil, errIncompleteRequest
   878  	}
   879  	var authzModels []authzModel
   880  	params := []interface{}{
   881  		req.RegistrationID,
   882  		statusUint(core.StatusValid),
   883  		statusUint(core.StatusPending),
   884  		time.Unix(0, req.NowNS),
   885  		identifierTypeToUint[string(identifier.DNS)],
   886  	}
   887  
   888  	for _, name := range req.Domains {
   889  		params = append(params, name)
   890  	}
   891  
   892  	query := fmt.Sprintf(
   893  		`SELECT %s FROM authz2
   894  			USE INDEX (regID_identifier_status_expires_idx)
   895  			WHERE registrationID = ? AND
   896  			status IN (?,?) AND
   897  			expires > ? AND
   898  			identifierType = ? AND
   899  			identifierValue IN (%s)`,
   900  		authzFields,
   901  		db.QuestionMarks(len(req.Domains)),
   902  	)
   903  
   904  	_, err := ssa.dbReadOnlyMap.Select(
   905  		ctx,
   906  		&authzModels,
   907  		query,
   908  		params...,
   909  	)
   910  	if err != nil {
   911  		return nil, err
   912  	}
   913  
   914  	if len(authzModels) == 0 {
   915  		return &sapb.Authorizations{}, nil
   916  	}
   917  
   918  	authzModelMap := make(map[string]authzModel)
   919  	for _, am := range authzModels {
   920  		existing, present := authzModelMap[am.IdentifierValue]
   921  		if !present || uintToStatus[existing.Status] == core.StatusPending && uintToStatus[am.Status] == core.StatusValid {
   922  			authzModelMap[am.IdentifierValue] = am
   923  		}
   924  	}
   925  
   926  	return authzModelMapToPB(authzModelMap)
   927  }
   928  
   929  func (ssa *SQLStorageAuthority) GetAuthorizations2(ctx context.Context, req *sapb.GetAuthorizationsRequest) (*sapb.Authorizations, error) {
   930  	return ssa.SQLStorageAuthorityRO.GetAuthorizations2(ctx, req)
   931  }
   932  
   933  // GetPendingAuthorization2 returns the most recent Pending authorization with
   934  // the given identifier, if available. This method only supports DNS identifier types.
   935  // TODO(#5816): Consider removing this method, as it has no callers.
   936  func (ssa *SQLStorageAuthorityRO) GetPendingAuthorization2(ctx context.Context, req *sapb.GetPendingAuthorizationRequest) (*corepb.Authorization, error) {
   937  	if req.RegistrationID == 0 || req.IdentifierValue == "" || req.ValidUntilNS == 0 {
   938  		return nil, errIncompleteRequest
   939  	}
   940  	var am authzModel
   941  	err := ssa.dbReadOnlyMap.SelectOne(
   942  		ctx,
   943  		&am,
   944  		fmt.Sprintf(`SELECT %s FROM authz2 WHERE
   945  			registrationID = :regID AND
   946  			status = :status AND
   947  			expires > :validUntil AND
   948  			identifierType = :dnsType AND
   949  			identifierValue = :ident
   950  			ORDER BY expires ASC
   951  			LIMIT 1 `, authzFields),
   952  		map[string]interface{}{
   953  			"regID":      req.RegistrationID,
   954  			"status":     statusUint(core.StatusPending),
   955  			"validUntil": time.Unix(0, req.ValidUntilNS),
   956  			"dnsType":    identifierTypeToUint[string(identifier.DNS)],
   957  			"ident":      req.IdentifierValue,
   958  		},
   959  	)
   960  	if err != nil {
   961  		if db.IsNoRows(err) {
   962  			return nil, berrors.NotFoundError("pending authz not found")
   963  		}
   964  		return nil, err
   965  	}
   966  	return modelToAuthzPB(am)
   967  }
   968  
   969  func (ssa *SQLStorageAuthority) GetPendingAuthorization2(ctx context.Context, req *sapb.GetPendingAuthorizationRequest) (*corepb.Authorization, error) {
   970  	return ssa.SQLStorageAuthorityRO.GetPendingAuthorization2(ctx, req)
   971  }
   972  
   973  // CountPendingAuthorizations2 returns the number of pending, unexpired authorizations
   974  // for the given registration.
   975  func (ssa *SQLStorageAuthorityRO) CountPendingAuthorizations2(ctx context.Context, req *sapb.RegistrationID) (*sapb.Count, error) {
   976  	if req.Id == 0 {
   977  		return nil, errIncompleteRequest
   978  	}
   979  
   980  	var count int64
   981  	err := ssa.dbReadOnlyMap.SelectOne(ctx, &count,
   982  		`SELECT COUNT(*) FROM authz2 WHERE
   983  		registrationID = :regID AND
   984  		expires > :expires AND
   985  		status = :status`,
   986  		map[string]interface{}{
   987  			"regID":   req.Id,
   988  			"expires": ssa.clk.Now(),
   989  			"status":  statusUint(core.StatusPending),
   990  		},
   991  	)
   992  	if err != nil {
   993  		return nil, err
   994  	}
   995  	return &sapb.Count{Count: count}, nil
   996  }
   997  
   998  func (ssa *SQLStorageAuthority) CountPendingAuthorizations2(ctx context.Context, req *sapb.RegistrationID) (*sapb.Count, error) {
   999  	return ssa.SQLStorageAuthorityRO.CountPendingAuthorizations2(ctx, req)
  1000  }
  1001  
  1002  // GetValidOrderAuthorizations2 is used to find the valid, unexpired authorizations
  1003  // associated with a specific order and account ID.
  1004  func (ssa *SQLStorageAuthorityRO) GetValidOrderAuthorizations2(ctx context.Context, req *sapb.GetValidOrderAuthorizationsRequest) (*sapb.Authorizations, error) {
  1005  	if req.AcctID == 0 || req.Id == 0 {
  1006  		return nil, errIncompleteRequest
  1007  	}
  1008  
  1009  	// The authz2 and orderToAuthz2 tables both have a column named "id", so we
  1010  	// need to be explicit about which table's "id" column we want to select.
  1011  	qualifiedAuthzFields := strings.Split(authzFields, " ")
  1012  	for i, field := range qualifiedAuthzFields {
  1013  		if field == "id," {
  1014  			qualifiedAuthzFields[i] = "authz2.id,"
  1015  			break
  1016  		}
  1017  	}
  1018  
  1019  	var ams []authzModel
  1020  	_, err := ssa.dbReadOnlyMap.Select(
  1021  		ctx,
  1022  		&ams,
  1023  		fmt.Sprintf(`SELECT %s FROM authz2
  1024  			LEFT JOIN orderToAuthz2 ON authz2.ID = orderToAuthz2.authzID
  1025  			WHERE authz2.registrationID = :regID AND
  1026  			authz2.expires > :expires AND
  1027  			authz2.status = :status AND
  1028  			orderToAuthz2.orderID = :orderID`,
  1029  			strings.Join(qualifiedAuthzFields, " "),
  1030  		),
  1031  		map[string]interface{}{
  1032  			"regID":   req.AcctID,
  1033  			"expires": ssa.clk.Now(),
  1034  			"status":  statusUint(core.StatusValid),
  1035  			"orderID": req.Id,
  1036  		},
  1037  	)
  1038  	if err != nil {
  1039  		return nil, err
  1040  	}
  1041  
  1042  	byName := make(map[string]authzModel)
  1043  	for _, am := range ams {
  1044  		if uintToIdentifierType[am.IdentifierType] != string(identifier.DNS) {
  1045  			return nil, fmt.Errorf("unknown identifier type: %q on authz id %d", am.IdentifierType, am.ID)
  1046  		}
  1047  		existing, present := byName[am.IdentifierValue]
  1048  		if !present || am.Expires.After(existing.Expires) {
  1049  			byName[am.IdentifierValue] = am
  1050  		}
  1051  	}
  1052  
  1053  	return authzModelMapToPB(byName)
  1054  }
  1055  
  1056  func (ssa *SQLStorageAuthority) GetValidOrderAuthorizations2(ctx context.Context, req *sapb.GetValidOrderAuthorizationsRequest) (*sapb.Authorizations, error) {
  1057  	return ssa.SQLStorageAuthorityRO.GetValidOrderAuthorizations2(ctx, req)
  1058  }
  1059  
  1060  // CountInvalidAuthorizations2 counts invalid authorizations for a user expiring
  1061  // in a given time range. This method only supports DNS identifier types.
  1062  func (ssa *SQLStorageAuthorityRO) CountInvalidAuthorizations2(ctx context.Context, req *sapb.CountInvalidAuthorizationsRequest) (*sapb.Count, error) {
  1063  	if req.RegistrationID == 0 || req.Hostname == "" || req.Range.EarliestNS == 0 || req.Range.LatestNS == 0 {
  1064  		return nil, errIncompleteRequest
  1065  	}
  1066  
  1067  	var count int64
  1068  	err := ssa.dbReadOnlyMap.SelectOne(
  1069  		ctx,
  1070  		&count,
  1071  		`SELECT COUNT(*) FROM authz2 WHERE
  1072  		registrationID = :regID AND
  1073  		status = :status AND
  1074  		expires > :expiresEarliest AND
  1075  		expires <= :expiresLatest AND
  1076  		identifierType = :dnsType AND
  1077  		identifierValue = :ident`,
  1078  		map[string]interface{}{
  1079  			"regID":           req.RegistrationID,
  1080  			"dnsType":         identifierTypeToUint[string(identifier.DNS)],
  1081  			"ident":           req.Hostname,
  1082  			"expiresEarliest": time.Unix(0, req.Range.EarliestNS),
  1083  			"expiresLatest":   time.Unix(0, req.Range.LatestNS),
  1084  			"status":          statusUint(core.StatusInvalid),
  1085  		},
  1086  	)
  1087  	if err != nil {
  1088  		return nil, err
  1089  	}
  1090  	return &sapb.Count{Count: count}, nil
  1091  }
  1092  
  1093  func (ssa *SQLStorageAuthority) CountInvalidAuthorizations2(ctx context.Context, req *sapb.CountInvalidAuthorizationsRequest) (*sapb.Count, error) {
  1094  	return ssa.SQLStorageAuthorityRO.CountInvalidAuthorizations2(ctx, req)
  1095  }
  1096  
  1097  // GetValidAuthorizations2 returns the latest authorization for all
  1098  // domain names that the account has authorizations for. This method
  1099  // only supports DNS identifier types.
  1100  func (ssa *SQLStorageAuthorityRO) GetValidAuthorizations2(ctx context.Context, req *sapb.GetValidAuthorizationsRequest) (*sapb.Authorizations, error) {
  1101  	if len(req.Domains) == 0 || req.RegistrationID == 0 || req.NowNS == 0 {
  1102  		return nil, errIncompleteRequest
  1103  	}
  1104  
  1105  	query := fmt.Sprintf(
  1106  		`SELECT %s FROM authz2 WHERE
  1107  			registrationID = ? AND
  1108  			status = ? AND
  1109  			expires > ? AND
  1110  			identifierType = ? AND
  1111  			identifierValue IN (%s)`,
  1112  		authzFields,
  1113  		db.QuestionMarks(len(req.Domains)),
  1114  	)
  1115  
  1116  	params := []interface{}{
  1117  		req.RegistrationID,
  1118  		statusUint(core.StatusValid),
  1119  		time.Unix(0, req.NowNS),
  1120  		identifierTypeToUint[string(identifier.DNS)],
  1121  	}
  1122  	for _, domain := range req.Domains {
  1123  		params = append(params, domain)
  1124  	}
  1125  
  1126  	var authzModels []authzModel
  1127  	_, err := ssa.dbReadOnlyMap.Select(
  1128  		ctx,
  1129  		&authzModels,
  1130  		query,
  1131  		params...,
  1132  	)
  1133  	if err != nil {
  1134  		return nil, err
  1135  	}
  1136  
  1137  	authzMap := make(map[string]authzModel, len(authzModels))
  1138  	for _, am := range authzModels {
  1139  		// Only allow DNS identifiers
  1140  		if uintToIdentifierType[am.IdentifierType] != string(identifier.DNS) {
  1141  			continue
  1142  		}
  1143  		// If there is an existing authorization in the map only replace it with one
  1144  		// which has a later expiry.
  1145  		if existing, present := authzMap[am.IdentifierValue]; present && am.Expires.Before(existing.Expires) {
  1146  			continue
  1147  		}
  1148  		authzMap[am.IdentifierValue] = am
  1149  	}
  1150  	return authzModelMapToPB(authzMap)
  1151  }
  1152  
  1153  func (ssa *SQLStorageAuthority) GetValidAuthorizations2(ctx context.Context, req *sapb.GetValidAuthorizationsRequest) (*sapb.Authorizations, error) {
  1154  	return ssa.SQLStorageAuthorityRO.GetValidAuthorizations2(ctx, req)
  1155  }
  1156  
  1157  // KeyBlocked checks if a key, indicated by a hash, is present in the blockedKeys table
  1158  func (ssa *SQLStorageAuthorityRO) KeyBlocked(ctx context.Context, req *sapb.KeyBlockedRequest) (*sapb.Exists, error) {
  1159  	if req == nil || req.KeyHash == nil {
  1160  		return nil, errIncompleteRequest
  1161  	}
  1162  
  1163  	var id int64
  1164  	err := ssa.dbReadOnlyMap.SelectOne(ctx, &id, `SELECT ID FROM blockedKeys WHERE keyHash = ?`, req.KeyHash)
  1165  	if err != nil {
  1166  		if db.IsNoRows(err) {
  1167  			return &sapb.Exists{Exists: false}, nil
  1168  		}
  1169  		return nil, err
  1170  	}
  1171  
  1172  	return &sapb.Exists{Exists: true}, nil
  1173  }
  1174  
  1175  func (ssa *SQLStorageAuthority) KeyBlocked(ctx context.Context, req *sapb.KeyBlockedRequest) (*sapb.Exists, error) {
  1176  	return ssa.SQLStorageAuthorityRO.KeyBlocked(ctx, req)
  1177  }
  1178  
  1179  // IncidentsForSerial queries each active incident table and returns every
  1180  // incident that currently impacts `req.Serial`.
  1181  func (ssa *SQLStorageAuthorityRO) IncidentsForSerial(ctx context.Context, req *sapb.Serial) (*sapb.Incidents, error) {
  1182  	if req == nil {
  1183  		return nil, errIncompleteRequest
  1184  	}
  1185  
  1186  	var activeIncidents []incidentModel
  1187  	_, err := ssa.dbReadOnlyMap.Select(ctx, &activeIncidents, `SELECT * FROM incidents WHERE enabled = 1`)
  1188  	if err != nil {
  1189  		if db.IsNoRows(err) {
  1190  			return &sapb.Incidents{}, nil
  1191  		}
  1192  		return nil, err
  1193  	}
  1194  
  1195  	var incidentsForSerial []*sapb.Incident
  1196  	for _, i := range activeIncidents {
  1197  		var count int
  1198  		err := ssa.dbIncidentsMap.SelectOne(ctx, &count, fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE serial = ?",
  1199  			i.SerialTable), req.Serial)
  1200  		if err != nil {
  1201  			if db.IsNoRows(err) {
  1202  				continue
  1203  			}
  1204  			return nil, err
  1205  		}
  1206  		if count > 0 {
  1207  			incident := incidentModelToPB(i)
  1208  			incidentsForSerial = append(incidentsForSerial, &incident)
  1209  		}
  1210  
  1211  	}
  1212  	if len(incidentsForSerial) == 0 {
  1213  		return &sapb.Incidents{}, nil
  1214  	}
  1215  	return &sapb.Incidents{Incidents: incidentsForSerial}, nil
  1216  }
  1217  
  1218  func (ssa *SQLStorageAuthority) IncidentsForSerial(ctx context.Context, req *sapb.Serial) (*sapb.Incidents, error) {
  1219  	return ssa.SQLStorageAuthorityRO.IncidentsForSerial(ctx, req)
  1220  }
  1221  
  1222  // SerialsForIncident queries the provided incident table and returns the
  1223  // resulting rows as a stream of `*sapb.IncidentSerial`s. An `io.EOF` error
  1224  // signals that there are no more serials to send. If the incident table in
  1225  // question contains zero rows, only an `io.EOF` error is returned. The
  1226  // IncidentSerial messages returned may have the zero-value for their OrderID,
  1227  // RegistrationID, and LastNoticeSent fields, if those are NULL in the database.
  1228  func (ssa *SQLStorageAuthorityRO) SerialsForIncident(req *sapb.SerialsForIncidentRequest, stream sapb.StorageAuthorityReadOnly_SerialsForIncidentServer) error {
  1229  	if req.IncidentTable == "" {
  1230  		return errIncompleteRequest
  1231  	}
  1232  
  1233  	// Check that `req.IncidentTable` is a valid incident table name.
  1234  	if !validIncidentTableRegexp.MatchString(req.IncidentTable) {
  1235  		return fmt.Errorf("malformed table name %q", req.IncidentTable)
  1236  	}
  1237  
  1238  	selector, err := db.NewMappedSelector[incidentSerialModel](ssa.dbIncidentsMap)
  1239  	if err != nil {
  1240  		return fmt.Errorf("initializing db map: %w", err)
  1241  	}
  1242  
  1243  	rows, err := selector.QueryFrom(stream.Context(), req.IncidentTable, "")
  1244  	if err != nil {
  1245  		return fmt.Errorf("starting db query: %w", err)
  1246  	}
  1247  	defer rows.Close()
  1248  
  1249  	for rows.Next() {
  1250  		// Scan the row into the model. Note: the fields must be passed in the
  1251  		// same order as the columns returned by the query above.
  1252  		ism, err := rows.Get()
  1253  		if err != nil {
  1254  			return err
  1255  		}
  1256  
  1257  		ispb := &sapb.IncidentSerial{
  1258  			Serial: ism.Serial,
  1259  		}
  1260  		if ism.RegistrationID != nil {
  1261  			ispb.RegistrationID = *ism.RegistrationID
  1262  		}
  1263  		if ism.OrderID != nil {
  1264  			ispb.OrderID = *ism.OrderID
  1265  		}
  1266  		if ism.LastNoticeSent != nil {
  1267  			ispb.LastNoticeSentNS = ism.LastNoticeSent.UnixNano()
  1268  			ispb.LastNoticeSent = timestamppb.New(*ism.LastNoticeSent)
  1269  		}
  1270  
  1271  		err = stream.Send(ispb)
  1272  		if err != nil {
  1273  			return err
  1274  		}
  1275  	}
  1276  
  1277  	err = rows.Err()
  1278  	if err != nil {
  1279  		return err
  1280  	}
  1281  	return nil
  1282  }
  1283  
  1284  func (ssa *SQLStorageAuthority) SerialsForIncident(req *sapb.SerialsForIncidentRequest, stream sapb.StorageAuthority_SerialsForIncidentServer) error {
  1285  	return ssa.SQLStorageAuthorityRO.SerialsForIncident(req, stream)
  1286  }
  1287  
  1288  // GetRevokedCerts gets a request specifying an issuer and a period of time,
  1289  // and writes to the output stream the set of all certificates issued by that
  1290  // issuer which expire during that period of time and which have been revoked.
  1291  // The starting timestamp is treated as inclusive (certs with exactly that
  1292  // notAfter date are included), but the ending timestamp is exclusive (certs
  1293  // with exactly that notAfter date are *not* included).
  1294  func (ssa *SQLStorageAuthorityRO) GetRevokedCerts(req *sapb.GetRevokedCertsRequest, stream sapb.StorageAuthorityReadOnly_GetRevokedCertsServer) error {
  1295  	if req.ShardIdx != 0 {
  1296  		return ssa.getRevokedCertsFromRevokedCertificatesTable(req, stream)
  1297  	} else {
  1298  		return ssa.getRevokedCertsFromCertificateStatusTable(req, stream)
  1299  	}
  1300  }
  1301  
  1302  func (ssa *SQLStorageAuthority) GetRevokedCerts(req *sapb.GetRevokedCertsRequest, stream sapb.StorageAuthority_GetRevokedCertsServer) error {
  1303  	return ssa.SQLStorageAuthorityRO.GetRevokedCerts(req, stream)
  1304  }
  1305  
  1306  // getRevokedCertsFromRevokedCertificatesTable uses the new revokedCertificates
  1307  // table to implement GetRevokedCerts. It must only be called when the request
  1308  // contains a non-zero ShardIdx.
  1309  func (ssa *SQLStorageAuthorityRO) getRevokedCertsFromRevokedCertificatesTable(req *sapb.GetRevokedCertsRequest, stream sapb.StorageAuthorityReadOnly_GetRevokedCertsServer) error {
  1310  	if req.ShardIdx == 0 {
  1311  		return errors.New("can't select shard 0 from revokedCertificates table")
  1312  	}
  1313  
  1314  	atTime := time.Unix(0, req.RevokedBeforeNS)
  1315  
  1316  	clauses := `
  1317  		WHERE issuerID = ?
  1318  		AND shardIdx = ?
  1319  		AND notAfterHour >= ?`
  1320  	params := []interface{}{
  1321  		req.IssuerNameID,
  1322  		req.ShardIdx,
  1323  		// Round the expiry down to the nearest hour, to take advantage of our
  1324  		// smaller index while still capturing at least as many certs as intended.
  1325  		time.Unix(0, req.ExpiresAfterNS).Truncate(time.Hour),
  1326  	}
  1327  
  1328  	selector, err := db.NewMappedSelector[revokedCertModel](ssa.dbReadOnlyMap)
  1329  	if err != nil {
  1330  		return fmt.Errorf("initializing db map: %w", err)
  1331  	}
  1332  
  1333  	rows, err := selector.QueryContext(stream.Context(), clauses, params...)
  1334  	if err != nil {
  1335  		return fmt.Errorf("reading db: %w", err)
  1336  	}
  1337  
  1338  	defer func() {
  1339  		err := rows.Close()
  1340  		if err != nil {
  1341  			ssa.log.AuditErrf("closing row reader: %s", err)
  1342  		}
  1343  	}()
  1344  
  1345  	for rows.Next() {
  1346  		row, err := rows.Get()
  1347  		if err != nil {
  1348  			return fmt.Errorf("reading row: %w", err)
  1349  		}
  1350  
  1351  		// Double-check that the cert wasn't revoked between the time at which we're
  1352  		// constructing this snapshot CRL and right now. If the cert was revoked
  1353  		// at-or-after the "atTime", we'll just include it in the next generation
  1354  		// of CRLs.
  1355  		if row.RevokedDate.After(atTime) || row.RevokedDate.Equal(atTime) {
  1356  			continue
  1357  		}
  1358  
  1359  		err = stream.Send(&corepb.CRLEntry{
  1360  			Serial:      row.Serial,
  1361  			Reason:      int32(row.RevokedReason),
  1362  			RevokedAtNS: row.RevokedDate.UnixNano(),
  1363  			RevokedAt:   timestamppb.New(row.RevokedDate),
  1364  		})
  1365  		if err != nil {
  1366  			return fmt.Errorf("sending crl entry: %w", err)
  1367  		}
  1368  	}
  1369  
  1370  	err = rows.Err()
  1371  	if err != nil {
  1372  		return fmt.Errorf("iterating over row reader: %w", err)
  1373  	}
  1374  
  1375  	return nil
  1376  }
  1377  
  1378  // getRevokedCertsFromCertificateStatusTable uses the new old certificateStatus
  1379  // table to implement GetRevokedCerts.
  1380  func (ssa *SQLStorageAuthorityRO) getRevokedCertsFromCertificateStatusTable(req *sapb.GetRevokedCertsRequest, stream sapb.StorageAuthorityReadOnly_GetRevokedCertsServer) error {
  1381  	atTime := time.Unix(0, req.RevokedBeforeNS)
  1382  
  1383  	clauses := `
  1384  		WHERE notAfter >= ?
  1385  		AND notAfter < ?
  1386  		AND issuerID = ?
  1387  		AND status = ?`
  1388  	params := []interface{}{
  1389  		time.Unix(0, req.ExpiresAfterNS),
  1390  		time.Unix(0, req.ExpiresBeforeNS),
  1391  		req.IssuerNameID,
  1392  		core.OCSPStatusRevoked,
  1393  	}
  1394  
  1395  	selector, err := db.NewMappedSelector[crlEntryModel](ssa.dbReadOnlyMap)
  1396  	if err != nil {
  1397  		return fmt.Errorf("initializing db map: %w", err)
  1398  	}
  1399  
  1400  	rows, err := selector.QueryContext(stream.Context(), clauses, params...)
  1401  	if err != nil {
  1402  		return fmt.Errorf("reading db: %w", err)
  1403  	}
  1404  
  1405  	defer func() {
  1406  		err := rows.Close()
  1407  		if err != nil {
  1408  			ssa.log.AuditErrf("closing row reader: %s", err)
  1409  		}
  1410  	}()
  1411  
  1412  	for rows.Next() {
  1413  		row, err := rows.Get()
  1414  		if err != nil {
  1415  			return fmt.Errorf("reading row: %w", err)
  1416  		}
  1417  
  1418  		// Double-check that the cert wasn't revoked between the time at which we're
  1419  		// constructing this snapshot CRL and right now. If the cert was revoked
  1420  		// at-or-after the "atTime", we'll just include it in the next generation
  1421  		// of CRLs.
  1422  		if row.RevokedDate.After(atTime) || row.RevokedDate.Equal(atTime) {
  1423  			continue
  1424  		}
  1425  
  1426  		err = stream.Send(&corepb.CRLEntry{
  1427  			Serial:      row.Serial,
  1428  			Reason:      int32(row.RevokedReason),
  1429  			RevokedAtNS: row.RevokedDate.UnixNano(),
  1430  			RevokedAt:   timestamppb.New(row.RevokedDate),
  1431  		})
  1432  		if err != nil {
  1433  			return fmt.Errorf("sending crl entry: %w", err)
  1434  		}
  1435  	}
  1436  
  1437  	err = rows.Err()
  1438  	if err != nil {
  1439  		return fmt.Errorf("iterating over row reader: %w", err)
  1440  	}
  1441  
  1442  	return nil
  1443  }
  1444  
  1445  // GetMaxExpiration returns the timestamp of the farthest-future notAfter date
  1446  // found in the certificateStatus table. This provides an upper bound on how far
  1447  // forward operations that need to cover all currently-unexpired certificates
  1448  // have to look.
  1449  func (ssa *SQLStorageAuthorityRO) GetMaxExpiration(ctx context.Context, req *emptypb.Empty) (*timestamppb.Timestamp, error) {
  1450  	var model struct {
  1451  		MaxNotAfter *time.Time `db:"maxNotAfter"`
  1452  	}
  1453  	err := ssa.dbReadOnlyMap.SelectOne(
  1454  		ctx,
  1455  		&model,
  1456  		"SELECT MAX(notAfter) AS maxNotAfter FROM certificateStatus",
  1457  	)
  1458  	if err != nil {
  1459  		return nil, fmt.Errorf("selecting max notAfter: %w", err)
  1460  	}
  1461  	if model.MaxNotAfter == nil {
  1462  		return nil, errors.New("certificateStatus table notAfter column is empty")
  1463  	}
  1464  	return timestamppb.New(*model.MaxNotAfter), err
  1465  }
  1466  
  1467  func (ssa *SQLStorageAuthority) GetMaxExpiration(ctx context.Context, req *emptypb.Empty) (*timestamppb.Timestamp, error) {
  1468  	return ssa.SQLStorageAuthorityRO.GetMaxExpiration(ctx, req)
  1469  }
  1470  
  1471  // Health implements the grpc.checker interface.
  1472  func (ssa *SQLStorageAuthorityRO) Health(ctx context.Context) error {
  1473  	err := ssa.dbReadOnlyMap.SelectOne(ctx, new(int), "SELECT 1")
  1474  	if err != nil {
  1475  		return err
  1476  	}
  1477  	return nil
  1478  }
  1479  

View as plain text