Configure Canton with PostgreSQL

Example configuration

This example shows a PostgreSQL storage configuration for a Sequencer, Mediator, and Participant Node all running on a local PostgreSQL database instance on port 5432.

# Configures sequencer1, mediator1, and participant1 with locally running PostgreSQL storage.
canton {
  sequencers.sequencer1.storage {
    type = postgres
    config {
      dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
      properties = {
        serverName = "localhost"
        databaseName = "sequencer1_db"
        portNumber = "5432"
        user = "sequencer1"
        password = "pgpass"
      }
    }
  }
  mediators.mediator1.storage {
    type = postgres
    config {
      dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
      properties = {
        serverName = "localhost"
        databaseName = "mediator1_db"
        portNumber = "5432"
        user = "mediator1"
        password = "pgpass"
      }
    }
  }
  participants.participant1.storage {
    type = postgres
    config {
      dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
      properties = {
        serverName = "localhost"
        databaseName = "participant1_db"
        portNumber = "5432"
        user = "participant1"
        password = "pgpass"
      }
    }
  }
}

Configure the connection pool

Canton uses HikariCP for connection pooling. This is configured in storage.config. We recommend this article on how to choose the size of the pool.

Note

The setup of dataSourceClassName and properties for PostgreSQL are discussed in the next section.

The set of pool properties that may be set is given below, the descriptions of the properties can be found in the associated get/set method descriptions for HikariConfig.

allowPoolSuspension	catalog	connectionInitSql
connectionTestQuery	connectionTimeout	dataSourceClassName
idleTimeout	initializationFailTimeout	isolateInternalQueries
leakDetectionThreshold	maxLifetime	maximumPoolSize
minimumIdle	poolName	properties
readOnly	registerMbeans	schema
validationTimeout

Configure the PostgreSQL data source

To create a connection HikariCP uses the data-source dataSourceClassName configured using the properties in properties. We recommend using the org.postgresql.ds.PGSimpleDataSource data-source configured with the following properties:

• serverName

• databaseName

• portNumber

• user

• password

You can find the details of additional supported properties by reviewing the associated get/set method descriptions for PGSimpleDataSource.

Use environment variables in configuration

You can use environment variables to configure the PostgreSQL data-source properties. This is useful for sensitive information like passwords or when you want to avoid hardcoding values in your configuration files.

In this example all the database properties are set using environment variables. The environment variables are prefixed with SEQUENCER1_ to avoid conflicts with other configurations.

config {
  dataSourceClassName = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    serverName = ${SEQUENCER1_SERVER}
    databaseName = ${SEQUENCER1_DB}
    portNumber = ${SEQUENCER1_PORT}
    user = ${SEQUENCER1_USER}
    password = ${SEQUENCER1_PASSWORD}
  }

Share database configuration across nodes

This example shows how PureConfig can be used to share common database configuration across multiple nodes in a Canton setup.

# Postgres persistence configuration mixin
#
# This file defines a shared configuration resources. You can mix it into your configuration by
# refer to the shared storage resource and add the database name.
#
# Example:
#   participant1 {
#     storage = ${_shared.storage}
#     storage.config.properties.databaseName = "participant1"
#   }
#
# The user and password is not set. You want to either change this configuration file or pass
# the settings in via environment variables POSTGRES_USER and POSTGRES_PASSWORD.
#
_shared {
    storage {
        type = postgres
        config {
            dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
            properties = {
                serverName = "localhost"
                # the next line will override above "serverName" in case the environment variable POSTGRES_HOST exists
                # which makes it optional
                serverName = ${?POSTGRES_HOST}
                portNumber = "5432"
                portNumber = ${?POSTGRES_PORT}
                # user and password are required
                user = ${POSTGRES_USER}
                password = ${POSTGRES_PASSWORD}
            }
        }
        parameters {
            # If defined, will configure the number of database connections per node.
            # Please note that the number of connections can be fine tuned for participant nodes (see participant.conf)
            max-connections = ${?POSTGRES_NUM_CONNECTIONS}
            # If true, then database migrations will be applied on startup automatically
            # Otherwise, you will have to run the migration manually using participant.db.migrate()
            migrate-and-start = false
            # If true (default), then the node will fail to start if it can not connect to the database.
            # The setting is useful during initial deployment to get immediate feedback when the
            # database is not available.
            # In a production setup, you might want to set this to false to allow uncoordinated startups between
            # the database and the node.
            fail-fast-on-startup = true
        }
    }
}

Use SSL

Configure SSL using the following PGSimpleDataSource properties.

ssl = true

Verify both the SSL certificate and verify the hostname

sslmode= “verify-ca”

Check the certificate chain up to the root certificate stored on the client.

sslrootcert = “path/to/root.cert”

Optionally set this to set with path to root certificate.

For more details on how to configure SSL in PostgreSQL, see the PostgreSQL SSL documentation.

Use mTLS

To configure mutual TLS (mTLS) you can use the following additional properties:

• sslcert = “path/to/client-cert.pem”

• sslkey = “path/to/client-key.p12”

Set up the PostgreSQL database

A separate database is required for each Canton node. Create the database before starting Canton.

Note

The canton distribution provides a script, config/utils/postgres/db.sh, to help create the database and users.

Create the database

Databases must be created with UTF8 encoding to ensure proper handling of Unicode characters. The following SQL command creates a database named participant1_db with UTF8 encoding:

create database participant1_db encoding = 'UTF8';

Create a database user

The database user configured in the data-source properties must have the necessary permissions to create and modify the database schema, in addition to reading and writing data.

The following SQL commands create a user named participant1_user with a password and grant all privileges on the database:

create user participant1_user with password 'change-me';
grant all privileges on database participant1_db to participant1_user;

Operations

Optimize storage

See Storage Optimization.

Backup

See Backup and Restore.

Setup HA

See High Availability Usage.

Use a cloud hosted database

You can use a cloud-hosted PostgreSQL database, such as Amazon RDS, Google Cloud SQL, or Azure Database for PostgreSQL.

Please refer to the documentation of the respective cloud provider for details on how to set up and configure and secure a PostgreSQL database.