Configuring a JPA notary backend

Prior to using the JPA notary, the database must be prepared. This can be performed using the Corda Database Management Tool. If preferred, the required tables can be manually created. See below for example database scripts. Note that in these examples, a database named “corda” is created to house the tables - this is purely for example purposes. The database name could be any string supported by your database vendor - ensure that the configuration matches the database name.

Supported databases for highly available mode

The JPA notary uses the Java Persistence API (JPA) interface to connect to the notary state database. For performance and ease of operation, the recommended database is CockroachDB 20.1.6. The full set of supported configurations is listed in the Platform support matrix.

Using the Corda Database Management Tool

If using the Corda Database Management Tool to perform initial schema setup, take note of the following:

• Always specify the command as the first parameter. This would be either dry-run or execute-migration
• Specify the mode as being JPA_NOTARY by using the command-line parameter --mode=JPA_NOTARY
• Ensure that the configuration file used is correct, as detailed in the section below.

Use the dry-run command to generate SQL scripts which could be inspected prior to being run. Alternatively, use the execute-migration command to prepare the database, including table creation. Note that users and databases are not created. Thus, the database must already exist.

DBM Tool configuration file format

The configuration file used as an input to the Database Management Tool should closely resemble that of the notary itself. Only some minor changes may be needed. Take note of the following:

• The dataSourceClassName property must be provided.
• The dataSource.url property must be provided and should be identical to that used by the notary itself.
• The username and password needed for access to the database must be stored as dataSource.user and dataSource.password respectively.
• Any unused configuration parameters will be ignored.

Below is an example configuration file for the Database Management Tool:

notary {
    validating = false
    jpa {
        dataSourceProperties {
            dataSource.url = "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST={host 1 IP address})(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST={host 2 IP address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service name})))"
            dataSource.user = {username}
            dataSource.password = {password}
            dataSourceClassName = "oracle.jdbc.pool.OracleDataSource"
        }
    }
}

If the Corda Database Management Tool’s dry-run mode is used, the databasechangelog and databasechangeloglock tables must already exist and the database user would need read and write permissions. If the tool’s execute-migration mode is used, the database user would require schema modification rights. For more information, see Corda Database Management Tool.

Database users

R3 recommends creating one database user with schema modification rights so as to be able to create the schema objects necessary for the operation of the notary. However, this user should not be used for the operation of the notary for security reasons. R3 recommends the creation of a user with more limited permissions for the operation of the notary. This would be set in the configuration of the notary in the dataSourceProperties section.

Database Tables

Notary Committed States

The collection of spent states, used to detect double spend attempts.

Notary Committed Transactions

The collection of notarised transactions, used to re-notarise transactions that don’t get recorded into the collection of spent states because they only reference states or are time window issue transactions that don’t spend any states.

Notary Request Log

The request log, used to record the request signatures of the requesting parties.

Notary Double-Spend Table

The double-spend table includes records of transactions that attempted a double-spend.

Configuring the notary backend - CockroachDB

The JPA notary service is tested against CockroachDB 20.1.6. CockroachDB’s documentation page explains the installation in detail.

Some information specific to the configuration of the JPA notary to interact with CockroachDB is covered below.

Database setup

To create the database, a user with administrative permissions is required. CockroachDB automatically creates a root user during setup. This root user is the only user with administrative permissions, and so is the only user able to create databases. Only CockroachDB Enterprise supports the creation of administrative users besides root. The CockroachDB root user can only authenticate with certificates and is unable to authenticate via passwords.

Open a terminal window on one of the machines on which CockroachDB is installed. Connect to the SQL interface of the database with the following command. Note the command is an example and assumes that Cockroach has been installed to /opt/roach. Make sure to specify

the correct path for your certificates.

sudo cockroach sql --certs-dir=/opt/roach/certs

Once connected to CockroachDB, create the database and tables required. Note that the database name can be changed from “corda”, but the table names must be left as-is. If the database name is changed, make sure to change the JDBC URL in the configuration file to match.

create database if not exists corda;

create table corda.notary_committed_states (
  state_ref varchar(73) not null,
  consuming_transaction_id varchar(64) not null,
  constraint id1 primary key (state_ref)
  );

create table corda.notary_committed_transactions (
  transaction_id varchar(64) not null,
  constraint id2 primary key (transaction_id)
  );

create table corda.notary_request_log (
  id varchar(76) not null,
  consuming_transaction_id varchar(64),
  requesting_party_name varchar(255),
  request_timestamp timestamp not null,
  request_signature bytes not null,
  worker_node_x500_name varchar(255),
  constraint id3 primary key (id),
  index (consuming_transaction_id)
  );

create table corda.notary_double_spends (
  state_ref varchar(73) not null,
  request_timestamp timestamp not null,
  consuming_transaction_id varchar(64) not null,
  constraint id4 primary key (state_ref, consuming_transaction_id),
  index (state_ref, request_timestamp, consuming_transaction_id)
  );

Database user setup

Once the database and tables have been created, create a user with restricted rights that the notary worker will use to log in to the database. This user will only be able to insert and read data. It will not be able to delete or update data, nor will it be able to modify any schemas. Ensure that the database name is correct if it was changed in the previous step. The username can be changed if desired - ensure that the configuration file is updated to match.

create user if not exists corda;

grant select on database corda to corda;
grant insert on database corda to corda;

grant select on table corda.* to corda;
grant insert on table corda.* to corda;

Generating a client certificate

R3 recommends that the CockroachDB installation be configured to use SSL for secure connections. This will require certificates to be generated for the database user that Corda uses to connect to CockroachDB. When generating the certificates, make sure that PKCS8 certificates are also generated. An example bash command is given below.

sudo cockroach cert create-client corda --certs-dir=/opt/roach/certs --ca-key=/opt/roach/my-safe-directory/ca.key --also-generate-pkcs8-key

Once generated, ensure that the certificates are accessible by the user that is being used to run the Corda process. Additionally, the same user has to have access to the key in PKCS8 format used to create above certificate.

JDBC driver

The PostgresSQL driver should be used when attempting to connect the JPA notary to CockroachDB. The JPA notary service has been tested with driver version 42.2.7. This JAR file should be placed in the drivers folder.

Connection string

The properties specifying the location of the client certificates must be passed in via the JDBC connection string. It is not possible to pass them in as configuration properties. See below for an example connection string.

dataSource.url="jdbc:postgresql://<CockroachDB-node1-IP-address>:26257,<CockroachDB-node2-IP-address>:26257,<CockroachDB-node3-IP-address>:26257/corda?sslmode=require&sslrootcert=certificates/ca.crt&sslcert=certificates/client.corda.crt&sslkey=certificates/client.corda.key.pk8"

Refer to the section Configuring the notary worker nodes for more details on configuring the JPA notary.

Configuring notary backend - Oracle RAC 19c

The JPA notary service is tested against Oracle RAC, with Oracle database version 19c. Oracle’s documentation page explains the installation in detail.

Some information specific to the configuration of the JPA notary to interact with Oracle RAC is covered below.

Database setup

R3 recommends that a pluggable database be created to house the notary data. This can be done by opening a terminal window on the Oracle machine and running the following command in order to start sqlplus, the Oracle SQL command line tool.

sudo su - oracle
sqlplus / as sysdba

With sqlplus running, create a pluggable database using the following command. The database name, administrative username and password can all be changed as needed.

CREATE PLUGGABLE DATABASE corda_pdb ADMIN USER corda_adm IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE corda_pdb OPEN instances=ALL;

Once the database is created, connect to it with the following command:

ALTER SESSION SET CONTAINER = corda_pdb;

While still connected to the newly created pluggable database, run the following command in order to determine the service name. The service name forms part of the JDBC URL for the database and is necessary for either the Corda Database Management Tool or the notary worker to connect to the database. Note that the service name can be specified manually during the creation of the pluggable database.

SELECT SERVICE_NAME FROM gv$session WHERE sid IN (SELECT sid FROM V$MYSTAT);

Now the necessary tables can be created. Note that this step can be performed using the Corda Database Management Tool if desired. If not, the following script can be used. Note that this script must be run on the pluggable database created above.

create table corda_adm.notary_committed_states (
  state_ref varchar(73) not null,
  consuming_transaction_id varchar(64) not null,
  constraint id1 primary key (state_ref)
  );

create table corda_adm.notary_committed_transactions (
  transaction_id varchar(64) not null,
  constraint id2 primary key (transaction_id)
  );

create table corda_adm.notary_request_log (
  id varchar(76) not null,
  consuming_transaction_id varchar(64),
  requesting_party_name varchar(255),
  request_timestamp timestamp not null,
  request_signature RAW(1024) not null,
  worker_node_x500_name varchar(255),
  constraint id3 primary key (id)
  );

create table corda_adm.notary_double_spends (
  state_ref varchar(73) not null,
  request_timestamp timestamp not null,
  consuming_transaction_id varchar(64) not null,
  constraint id4 primary key (state_ref)
  );

Once you have created the tables, you must add the following indexes:

create index tx_idx on corda_adm.notary_request_log(consuming_transaction_id)

create index state_ts_tx_idx on corda_adm.notary_double_spends (state_ref,request_timestamp,consuming_transaction_id)

Database user setup

Once the database and tables have been created, create a user with restricted rights that the notary worker will use to log in to the database. This user will be a local user with access rights only to the pluggable database created above. Ensure that the container for the sqlplus session is still the Corda pluggable database as created above - this will make sure that the user created belongs to the pluggable database. The username can be changed if desired - ensure that the configuration file is updated to match.

This user will only be able to insert and read data. It will not be able to delete or update data, nor will it be able to modify any schemas. Ensure that the database name is correct if it was changed in the previous step.

ALTER SESSION SET CONTAINER = corda_pdb;
CREATE USER corda_pdb_user IDENTIFIED BY Password1 CONTAINER=CURRENT;

GRANT CREATE SESSION to corda_pdb_user CONTAINER=CURRENT;

GRANT SELECT, INSERT ON corda_adm.notary_committed_states TO corda_pdb_user;
GRANT SELECT, INSERT ON corda_adm.notary_committed_transactions TO corda_pdb_user;
GRANT SELECT, INSERT ON corda_adm.notary_request_log TO corda_pdb_user;

Lastly, you must grant user rights:

GRANT SELECT, INSERT ON corda_adm.notary_double_spends TO corda_pdb_user;

JDBC driver

The ojdbc8 driver should be used when connecting to Oracle RAC database 19c. This JAR file should be placed in the drivers folder.

Connection string

Below is an example connection string for use with an Oracle RAC database. Note that more than 2 host IP addresses may be specified if desired. It is important to use the correct service name.

dataSource.url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST={host 1 IP address})(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST={host 2 IP address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service name})))"