Database CLI

ENTERPRISE  This is a SignServer Enterprise feature.

The SignServer Database CLI uses Java Persistence API (JPA) to connect to the SignServer database. The CLI is configured by the dbcli.* properties in signserver_cli.properties.

The CLI needs to have the JDBC driver of the selected database available on the classpath. This can be achieved by setting the OPTIONAL_CLASSPATH environment variable when calling the bin/signserver-db script or by putting the JAR under lib/ext/jdbc/ and calling it jdbc.jar. The last method is required to be able to run the JUnit tests.

Sample usage:

bin/signserver-db audit

Verify Auditlog Command

The command verifies logs from all nodes starting at sequence number 0, or only from the specified nodes and optional sequence number offsets.

Usage:

verifylog -all verifylog [-node NODENAME[:OFFSET] ...]

If the nodename is suffixed with a colon and a number, then the verification for that node is started from that sequence number. The JDBC connector of the database might have to be put on the classpath, see the following example.

Examples:

signserver-db audit verifylog -all
signserver-db audit verifylog -node server1 -node server2:708
OPTIONAL_CLASSPATH=/usr/share/java/mysql-connector-java.jar signserver-db audit verifylog -all

The command can be run to verify the signature of every entry in the auditlog as well as checking the sequence number in order to make sure that it does not contain any gaps.

The keys used to verify the log are configured in conf/databaseprotection.properties.

Sample output:

2017-07-04 15:29:10,073 INFO  [VerifyLogCommand] The following nodes exists: [server1, server2]
2017-07-04 15:29:10,074 INFO  [VerifyLogCommand] Start sequences: {server1=0, server2=0}
2017-07-04 15:29:10,635 INFO  [SoftCryptoToken] Invalid authentication code. Token id=400.
2017-07-04 15:29:10,716 INFO  [SoftCryptoToken] Activated Crypto Token with ID 400.
2017-07-04 15:29:10,722 INFO  [VerifyLogCommand] Progress: node=server1 rowCount=3
2017-07-04 15:29:10,722 INFO  [VerifyLogCommand] Last sequence number: 2
2017-07-04 15:29:10,745 INFO  [VerifyLogCommand] Progress: node=server2 rowCount=11
2017-07-04 15:29:10,745 INFO  [VerifyLogCommand] Last sequence number: 7
2017-07-04 15:29:10,747 INFO  [VerifyLogCommand] Audit log validation completed in 0 seconds. 11 rows found. Errors: 0 Warnings: 0
2017-07-04 15:29:10,747 INFO  [VerifyLogCommand] Verification finished with success
2017-07-04 15:29:10,747 INFO  [VerifyLogCommand] Last sequences: {server1=2, server2=7}

Archiving of the audit log

As the Database CLI audit verifylog command checks for gaps in the sequence numbering, entries from the audit log cannot be archived/moved to other storage without special care. However, since the command now supports the option to specify from which sequence number to start the verification, the following scheme for archiving is proposed:

  1. Run the command to verify the complete log audit log:
    audit verifylog -all

    Note down the last sequence number for each node, for example:
    Last sequences: {server1=2, server2=7}
  2. All entries from 0 up to the entry before the last can now be archived and removed from the database. In the example, that would be entry 0 and 1 for node server1 and entries from 0 to 6 for node server2.

  3. The next time the log should be verified, start the verification from the last sequence numbers:
    audit verifylog -node server1:2 -node server2:7


    Again, note down the last sequence number for each node, and all entries up to the sequence number before the last for each node can now be archived.

It is important to not remove the row with the last sequence number for a node, as this would result in the sequence numbering starting again from 0 for that node, if the application is restarted.