PostgreSQL backend

This page shows how to run libspiffy on a server with PostgreSQL as its storage: the event journal, the read models and, optionally, encrypted xpubs. Mobile and desktop apps use Isar instead.

Choose the backend

Pass StorageBackend.postgres and a PostgresConfig to initialize():

bin/server.dart

// Sketch: storage options only.
import 'package:libspiffy/libspiffy.dart';

final libspiffy = LibSpiffyActorSystem();
await libspiffy.initialize(
  storageBackend: StorageBackend.postgres,
  postgresConfig: PostgresConfig.fromConnectionString(
    'postgresql://wallet:secret@db.internal:5432/wallets?sslmode=verify-full',
  ),
  secureStorage: secureStorage, // see below
  networkType: 'main',
);

On start, libspiffy:

1. runs the schema migrations (PostgresMigrations.migrate()), under a PostgreSQL advisory lock so that several instances starting together do not race;
2. opens the event store (PostgresEventStore) and the read-model storage (PostgresWalletStorage), each with its own connection pool.

shutdown() closes both.

The StorageBackend values are isar (the default), postgres and inMemory (tests only).

PostgresConfig

From a connection string

PostgresConfig.fromConnectionString(
  'postgresql://user:pass@localhost:5432/wallets?sslmode=disable',
  maxConnections: 10,
);

The scheme is postgresql:// or postgres://. Query parameters:

Parameter	Effect
sslmode	disable; allow, prefer or require (all become require); verify-ca or verify-full (both become verify-full). Any other value throws ArgumentError.
sslrootcert	Path to the PEM certificate authority that signed the server’s certificate.
schema	Schema to use (default public).
application_name	Application name for monitoring (default libspiffy).

fromConnectionString also takes maxConnections, connectionTimeout, idleTimeout and maxConnectionAge as named parameters.

With the constructor

PostgresConfig(
  host: 'db.internal',
  database: 'wallets',
  username: 'wallet',
  password: Platform.environment['DB_PASSWORD'],
  sslRootCertPath: '/etc/ssl/db-ca.pem', // selects verify-full
);

Field	Default	Notes
host, database	required
port	5432
username, password	null
sslMode	SslMode.require	SslMode comes from package:postgres.
enableSsl	—	Shorthand: false means SslMode.disable. Ignored when sslMode is given.
sslRootCertPath / sslRootCertBytes	null	A CA to verify the server against. Given without an sslMode, the mode becomes SslMode.verifyFull.
securityContext	null	A SecurityContext passed to the driver as is (for mutual TLS, for instance). Wins over the two above.
maxConnections	10	Pool size.
connectionTimeout	30 s
idleTimeout	10 min	Applied as the server’s idle_session_timeout (PostgreSQL 14 and later; ignored by older servers). Duration.zero turns it off.
maxConnectionAge	1 h
schema	public	Any other schema is set as the search_path of every connection. It must already exist.
applicationName	libspiffy

TLS

TLS is on by default. SslMode.require encrypts but accepts any certificate; use verify-full (or give a root certificate) in production. A local server without TLS needs ?sslmode=disable or enableSsl: false; disable sends everything, the password included, in clear.

toString() and toConnectionString() leave the password out, so a config is safe to log.

Tables

The migrations live in lib/src/storage/postgres/migrations/ (v001 to v029 in 5.0.0) and are recorded in schema_migrations. They create, among others: event_envelopes, snapshot_envelopes, projection_checkpoints, wallet_metadata, addresses, bitcoin_transactions, bitcoin_utxos, block_headers, merkle_proofs, invoices, payment_channels, deferred_payments, pending_receives and secure_secrets.

Secure storage on a server

If you pass no secureStorage with the PostgreSQL backend, libspiffy falls back to InMemorySecureStorage and logs a SEVERE record. Mnemonics, WIFs and xprivs then live in a Dart map and are lost on restart: after a restart every wallet exists and none can sign. Always pass a persistent SecureStorage.

PostgresSecureStorage holds xpubs only

PostgresSecureStorage stores secrets in the secure_secrets table, encrypted with AES-256-GCM under keys derived from a master key with HKDF. It is built for watch-only (xpub) wallets:

• it stores only keys starting with wallet_xpub_ and wallet_hdpubkey_, which is what CreateWalletCommand with an xpub writes;
• every private-key method (setMnemonic, setWIF, setXPriv, setPrivateKey, the identity and account-metadata methods) throws UnimplementedError.

So a server that uses it can create xpub wallets, which can derive addresses and receive but cannot sign. If your server must sign, implement SecureStorage yourself over a store you trust (an HSM, a secrets manager).

import 'dart:io';
import 'package:libspiffy/coordinator.dart';
import 'package:libspiffy/libspiffy.dart';

final config = PostgresConfig.fromConnectionString(Platform.environment['DATABASE_URL']!);
final pool = await config.createPool();

final secureStorage = await PostgresSecureStorage.create(
  pool: pool,
  masterKeyBase64: Platform.environment['LIBSPIFFY_MASTER_KEY']!,
);

await libspiffy.initialize(
  storageBackend: StorageBackend.postgres,
  postgresConfig: config,
  secureStorage: secureStorage,
);

// ask() completes when the read model holds the wallet, and throws
// CoordinatorFailure if it was not created.
final created = await libspiffy.coordinator.ask(CreateWalletCommand(
  walletId: 'merchant-1',
  name: 'Merchant 1',
  xpub: 'xpub6...',
));
print(created.rootAddress);

The pool you create is yours: close it after libspiffy.shutdown(). The secure_secrets table is created by libspiffy’s migrations, so the first initialize() must have run before the storage is used.

The master key

The master key is 32 random bytes, base64-encoded. PostgresSecureStorage.create throws ArgumentError if it does not decode to 32 bytes.

openssl rand -base64 32

EncryptionService.generateMasterKeyBase64() does the same from Dart. Keep the key out of the code, the repository and the command line. Inject it at runtime: a systemd EnvironmentFile with mode 600, a Docker secret file, or a secrets manager. doc/postgres-secure-storage-guide.md in the libspiffy repository has worked examples for systemd, Docker, Vault Agent and DigitalOcean.

Rotating the key

Every row records the key version it was encrypted under, and reads pick the matching key. To rotate:

final storage = await PostgresSecureStorage.create(
  pool: pool,
  masterKeyBase64: newKey,
  keyVersion: 2,
  previousMasterKeysBase64: {1: oldKey}, // rows under version 1 stay readable
);

final moved = await storage.reencryptToCurrentKey(); // one transaction

New writes use version 2 at once. reencryptToCurrentKey() re-encrypts every xpub and hdpubkey row that is not under the current version and returns how many rows changed. It changes nothing and throws SecureStorageException if any row cannot be decrypted. Once it returns, you can drop the old key from previousMasterKeysBase64.

What differs from Isar

ARC broadcast retries are stored in Isar. With the PostgreSQL backend there is no Isar instance, so the durable retry queue is disabled and a warning is logged. See Network, ARC and data sources.

Running libspiffy’s PostgreSQL tests

The tests in test/storage/postgres/ are tagged postgres and need a running server. They read POSTGRES_HOST (default localhost), POSTGRES_PORT (5432), POSTGRES_DATABASE (libspiffy_test), POSTGRES_USER (postgres) and POSTGRES_PASSWORD (postgres), and connect without TLS.

docker run -d --name libspiffy-postgres \
  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=libspiffy_test -p 5432:5432 postgres:16

POSTGRES_DATABASE=libspiffy_test dart test --tags=postgres test/storage/postgres/

dart test --exclude-tags=postgres skips them. See Testing and localnet for the rest of the suite.