Database and audit logging

Run PostgreSQL for io.sign, back it up, and record an audit trail of database activity

io.sign keeps its vaults, signers, requests, and settings in PostgreSQL. The webhooks service uses a second database. Neither database holds key shares: those stay on your signers.

Requirements

  • PostgreSQL 17 or later.
  • A database user with DDL privileges (CREATE, ALTER, and DROP on the target schema) to run migrations. See Migrations and upgrades.
  • TLS between io.sign and the database. Set DB_SSL_ENABLED=true.
  • Optionally, a read replica. Set the DB_READER_* variables to send reads to it.

For high availability, run the database with a standby in a second availability zone and automatic failover.

Connection pool and timeout settings are in the configuration reference.

Back up the database

Back up both databases with point-in-time recovery, and test restoring them. Take an extra backup before every upgrade, because rolling back a migration can drop data. See Upgrades and rollback.

A database backup doesn't back up your keys. To recover keys, see Disaster recovery.

Record an audit trail with pgAudit

pgAudit records who changed what in the database, to PostgreSQL's log. To turn it on:

  1. Install the pgAudit extension. Managed PostgreSQL services such as Amazon RDS and Azure Database for PostgreSQL provide it as a supported extension.

  2. Load it at server start, in postgresql.conf or your service's parameter group:

    shared_preload_libraries = 'pgaudit'
  3. Restart PostgreSQL, then create the extension in each io.sign database:

    CREATE EXTENSION IF NOT EXISTS pgaudit;
  4. Choose what to log, and add context to each log line:

    pgaudit.log = 'ddl, role, misc, misc_set, write'
    pgaudit.log_parameter = on
    log_line_prefix = '%m [pid=%p] %q[db=%d, usr=%u, client=%h, app=%a, session=%c, line=%l, tx=%x] '
    log_connections = all   # PostgreSQL 18 only*

    * log_connections = all is valid only on PostgreSQL 18 and later. On PostgreSQL 17, use log_connections = on.

These settings log schema changes, role changes, and every write, with the statement's parameters. Ship PostgreSQL's logs to your log store, and restrict who can read them: with pgaudit.log_parameter on, they contain the data that was written.

Record client IP addresses

io.sign records the caller's IP address with each action it audits. Behind a load balancer or proxy, the address io.sign sees is the proxy's, not the caller's. To record the caller's address, list your proxies:

AUDIT_TRUSTED_PROXY_CIDRS=10.0.0.0/16

io.sign then takes the caller's address from the X-Forwarded-For header that those proxies add. It ignores the header on requests from any other address, so a caller can't spoof it. If you leave the variable unset, io.sign records the address of whatever connects to it. Set the same value on the webhooks service, which records addresses in the same way.


Did this page help you?