Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-8680

Index recommendation will not allow use of partitioned CRLs

    Details

    • Issue discovered during:
      Ad Hoc
    • Sprint:
      EJBCA Team Alice - 2020 w13, EJBCA Team Alice - 2020 w16, EJBCA Team Alice - 2020 w20, EJBCA Team Alice - 2020 w23

      Description

      When setting up a CA with partitioned CRLs on a system using default index recommendations from doc/sql-scripts/create-index-ejbca.sql the crldata_idx3 UNIQUEness is violated and the CA creation fails.

      Server log output:

      Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "crldata_idx3"
        Detail: Key (crlnumber, issuerdn)=(1, CN=...) already exists.
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
      	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
      	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
      	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
      	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
      	at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:121)
      	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
      	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
      	... 282 more
      

      The uniqueness of this index prevents (on a database level) a CA from ever issuing two CRLs with the same CRLNumber, while the CRLNumber is per CA and scope (partition).

      Nullable consideration

      CRLData.crlPartitionIndex is nullable.

      On Postgres and probably other databases:
      ...two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, ...
      which implies that CREATE UNIQUE INDEX crldata_idx3 ON CRLData (cRLNumber, issuerDN, crlPartitionIndex); would still not guarantee uniqueness for non-partitioned CRLs.

      Current queries and indexes

      doc/sql-scripts/create-index-ejbca.sql defines:

      -- unique to ensure that no two CRLs with the same CRLnumber from the same issuer is created
      CREATE UNIQUE INDEX crldata_idx3 ON CRLData (cRLNumber, issuerDN);
      -- Index to ensure CRL generation is not slowed down when looking for the next CRL Number, even of you have hundreds of thousands of old CRLs in the DB
      CREATE INDEX crldata_idx4 ON CRLData (issuerDN,deltaCRLIndicator,cRLNumber);
      

      org.cesecore.certificates.crl.CRLData defines the following JPQL queries:

      Retrieve a specific CRL:
      issuerDN, crlNumber, crlPartitionIndex

      SELECT a FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.crlNumber=:crlNumber AND a.crlPartitionIndex=:crlPartitionIndex
      SELECT a FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.crlNumber=:crlNumber AND (a.crlPartitionIndex=:crlPartitionIndex or a.crlPartitionIndex is NULL)
      

      Get the latest CRLNumber (as quickly as possible):
      issuerDN, deltaCRLIndicator, crlPartitionIndex(, crlNumber)

      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator>0 AND a.crlPartitionIndex=:crlPartitionIndex
      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator>0  AND (a.crlPartitionIndex=:crlPartitionIndex OR a.crlPartitionIndex IS NULL)
      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator=-1 AND a.crlPartitionIndex=:crlPartitionIndex
      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator=-1 AND (a.crlPartitionIndex=:crlPartitionIndex OR a.crlPartitionIndex IS NULL)
      

      deltaCRLIndicator is either -1 (full) or 1 (delta). This was also the case when this feature was added under ECA-595. → ECA-8682

      Non-production system test queries where the number of CRLs for an issuer is assumed to be small and ECA-8681:
      issuerDN

      SELECT a FROM CRLData a WHERE a.issuerDN=:issuerDN
      SELECT a.crlNumber FROM CRLData a WHERE a.issuerDN=:issuerDN
      

      Potential solution: Dropping "UNIQUE"

      Indexing on nullable columns seems risky to keep it database agnostic we will not be able to

      • guarantee uniqueness on the database level for the combination (issuerDN, crlPartitionIndex, crlNumber)
      • retrieve crlNumber directly from the index on database where such optimization is enabled.

      →Normal indexing where we want to limit the search space in the CRLData table

      --CREATE UNIQUE INDEX crldata_idx3 ON CRLData (cRLNumber, issuerDN);
      CREATE INDEX crldata_idx4 ON CRLData (issuerDN,deltaCRLIndicator,cRLNumber);
      CREATE INDEX crldata_idx5 ON CRLData (cRLNumber, issuerDN);
      

      Retrieve a specific CRL:

      SELECT a FROM CRLData a WHERE a.crlNumber=:crlNumber AND a.issuerDN=:issuerDN AND a.crlPartitionIndex=:crlPartitionIndex 
      SELECT a FROM CRLData a WHERE a.crlNumber=:crlNumber AND a.issuerDN=:issuerDN AND (a.crlPartitionIndex=0 OR a.crlPartitionIndex IS NULL)
      

      → Expected table search space using crldata_idx5 is the number of CRL-partitions for the CA) rows

      Get the latest CRLNumber:

      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator=:deltaCRLIndicator AND a.crlPartitionIndex=:crlPartitionIndex
      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.deltaCRLIndicator=:deltaCRLIndicator AND (a.crlPartitionIndex=0 OR a.crlPartitionIndex IS NULL)
      

      → Expected table search space using crldata_idx4 is sum(all delta or full CRLs from the CA in all partitions) rows.

      Partially, the expected slowness of getting the latest CRLNumber could be mitigated with more intelligent caching in client facing interfaces like the RFC4387 CRLStore.

      Methods for retrieving a specific CRL should tolerate and warn if multiple CRLs for the same issuerDn, crlPartitionIndex and crlNumber exists. It seems reasonable that the first issuance "wins".

      Potential solution: Ensure that crlPartitionIndex is always non-null.

      We could change the value of crlPartitionIndex=0 (or NULL) to {{-1}]. This would require a (for many) painful postupgrade and write to every single row in the CRLData table and is the reason why it was choosen as nullable in the first place.

      --CREATE UNIQUE INDEX crldata_idx3 ON CRLData (cRLNumber, issuerDN);
      --CREATE INDEX crldata_idx4 ON CRLData (issuerDN,deltaCRLIndicator,cRLNumber);
      CREATE UNIQUE INDEX crldata_idx5 ON CRLData (cRLNumber, issuerDN, crlPartitionIndex);
      CREATE INDEX crldata_idx6 ON CRLData (issuerDN,crlPartitionIndex,deltaCRLIndicator,cRLNumber);
      

      Retrieve a specific CRL:

      SELECT a FROM CRLData a WHERE a.crlNumber=:crlNumber AND a.issuerDN=:issuerDN AND a.crlPartitionIndex=:crlPartitionIndex 
      

      → Expected table search space using crldata_idx5 is 1) row

      Get the latest CRLNumber:

      SELECT MAX(a.crlNumber) FROM CRLData a WHERE a.issuerDN=:issuerDN AND a.crlPartitionIndex=:crlPartitionIndex AND a.deltaCRLIndicator=:deltaCRLIndicator
      

      → Expected table search space using crldata_idx6 is sum(all delta or full CRLs from the CA in a partition) rows (or 0 a MAX operation on the index itself where the db supports it).

      A combination of starting to use -1 instead of 0/NULL for crlPartitionIndex and automatic purge make this solution realistic long term.

      What to do?

      Minor release (new ticket):
      Detect unique index on CRLData, similar to how we do it for Allow Certificate serialnr override in Certificate Profiles (See StartupSingletonSessionBean). If unique index is present and  "Use CRL partitions" is not enabled, disable the checkbox and display info message. Not needed, see below

      Feature release:

      • For all new CRLs set crlPartitionIndex to -1 (for non-partition CRLs CAs).
      • Update JPQL to efficiently use new indices. Use crlPartitionIndex 0 or null as JPQL fallback query. Not needed, because of UNIQUE INDEX
      • Handle non-unique result while fetching CRLData (choose truth + warn admin when result isn't unique). Not needed, because of UNIQUE INDEX
      • Add soft uniqueness chceck before persisting CRL. Not needed, because of UNIQUE INDEX
      • New recommended index:
        CREATE INDEX crldata_idx5 ON CRLData (issuerDN,crlPartitionIndex,deltaCRLIndicator,cRLNumber); 

        Using indexes from "Ensure that crlPartitionIndex is always non-null" applied on post-upgrade to 7.4.0.

      • Remove old recommend indices. (Without unique constraints future async deployments will be easier). Removed on post-upgrade to 7.4.0
      • Update src/upgrade/ sql scripts as documentation. (Drop old CRLData indexes + alter tabel IF)
      • Verify index query plan (analyze SELECT....) of new JPQL and index.
      • Remove instructions for manually applying index from the documentation.

        Related work:

      • Improve caching in client facing interfaces
      • Provide automatic database maintenance features to allow the numbers of rows in CRLData to be kept small and minimize the impact of potential post-upgrades for many customers

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bastianf Bastian Fredriksson
              Reporter:
              johan Johan Eklund
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 1 week
                  1w
                  Remaining:
                  Time Spent - 2 days, 5 hours Remaining Estimate - 2 days, 3 hours
                  2d 3h
                  Logged:
                  Time Spent - 2 days, 5 hours Remaining Estimate - 2 days, 3 hours
                  2d 5h