SQL storage

Persistent state for lindir lives in PostgreSQL or MySQL/MariaDB. Development can use memory://.

Choice at deploy time

Full sample files in the Lindir tree (identical keys; only engine/DSN and bootstrap hardening differ): config.example.json (memory://), config.postgres.example.json, config.mysql.example.json. Field-by-field reference: Operator handbook §4.3.

memory:// (also bare memory or empty DSN) always uses the in-process store; dbEngine is ignored for that DSN.

PostgreSQL example

{
  "dbEngine": "postgres",
  "dbDsn": "postgresql://lindir:YOUR_PASSWORD@127.0.0.1:5432/lindir"
}

Also accepted: postgres://... or libpq keywords host=... user=... password=... dbname=... port=....

CREATE USER lindir WITH PASSWORD 'YOUR_PASSWORD';
CREATE DATABASE lindir OWNER lindir;

The role used in dbDsn must be able to create objects in schema public. Prefer OWNER lindir as above. On PostgreSQL 15+, if the database was created by another role and only CONNECT / GRANT ALL ON DATABASE was given, migrations fail with permission denied for schema public. Fix as a superuser:

-- connect to the lindir database first
GRANT USAGE, CREATE ON SCHEMA public TO lindir;
ALTER DATABASE lindir OWNER TO lindir;  -- optional but simplest long-term

Requires libpq at runtime (libpq5 / postgresql-libs). The server uses dpq2 dynamic-unmanaged with a vendored vendor/derelict-pq that loads newer libpq symbols optionally — so Ubuntu/Debian libpq (without PostgreSQL 17 APIs) still links and runs.

Live SQL tests

Unit tests use memory:// unless you set:

export LINDIR_TEST_PG_DSN='postgresql://lindir:lindir@127.0.0.1:5432/lindir'
export LINDIR_TEST_MYSQL_DSN='mysql://lindir:lindir@127.0.0.1:3306/lindir'
export LINDIR_TEST_MARIADB_DSN='mysql://lindir:lindir@127.0.0.1:3307/lindir'  # optional
dub test --compiler="${COMPILER:-ldc2}"   # ldc2 | dmd | gdc

Greenfield migrate gate (wipe + apply all sql/migrations/ on PostgreSQL, MySQL 8, and MariaDB — catch reserved-word / DDL bugs before merge):

./scripts/integration/sql/run.sh

See scripts/integration/sql/README.md. Run this before merging migration changes.

Multi-OS compile + PostgreSQL + MySQL smoke (server, client, CLI):

./scripts/integration/platform/run.sh ubuntu   # or: all

See scripts/integration/platform/README.md.

MySQL / MariaDB example

{
  "dbEngine": "mysql",
  "dbDsn": "mysql://lindir:YOUR_PASSWORD@127.0.0.1:3306/lindir"
}

Also accepted: mariadb://... or mysql-native form host=127.0.0.1;port=3306;user=lindir;pwd=...;db=lindir.

Create the database and user before starting lindir:

CREATE DATABASE lindir CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'lindir'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
GRANT ALL PRIVILEGES ON lindir.* TO 'lindir'@'%';
FLUSH PRIVILEGES;

MySQL / MariaDB: max_allowed_packet

Fleet inventory and vulnerability feed caches (Arch AVG, Debian DSA list, Ubuntu/OSV JSON) are large. mysql-native sends big parameters with COM_STMT_SEND_LONG_DATA, but MariaDB/MySQL still enforce max_allowed_packet on the whole value (not per chunk). Defaults (often 16 MiB) are too small; Lindir does not change this at runtime (max_allowed_packet is GLOBAL-only / read-only as SESSION on current MariaDB).

Set 64 MiB (or higher) on the database server before using Vulns Refresh or large inventory syncs:

1. Create a server config snippet (path varies by distro):

# /etc/my.cnf.d/lindir.cnf   (MariaDB on many RPM/Arch layouts)
# /etc/mysql/mariadb.conf.d/99-lindir.cnf   (Debian/Ubuntu MariaDB)
[mysqld]
max_allowed_packet = 64M

2. Restart the database:

sudo systemctl restart mariadb
# or: sudo systemctl restart mysqld

3. Confirm:

SHOW VARIABLES LIKE 'max_allowed_packet';
-- Value should be >= 67108864

Optional one-shot until the next restart (needs a privileged DB account):

SET GLOBAL max_allowed_packet = 67108864;

If you see errors similar to Parameter … longer than ‘max_allowed_packet’ bytes during vuln feed refresh, raise this setting and retry.

After a MariaDB major upgrade, run sudo mariadb-upgrade -u root -p if the server reports Column count of mysql.proc is wrong (error 1558).

MySQL / MariaDB: nullable timestamps

MariaDB in strict mode rejects empty string for TIMESTAMP NULL / DATETIME NULL (Incorrect datetime value: ''). Repos must bind the SQL_NULL sentinel from lindir.storage.database for “no value yet” fields such as last_used_at, started_at, finished_at, retired_at, locked_until. Do not pass D null or "" for those parameters — the MySQL driver turns those into non-null ''.

Driver status

Migrating data between databases

Use lindir-migrate to copy all Lindir tables from one database to another. Supported directions: PostgreSQL ↔ MySQL/MariaDB, and same-engine copies.

Stop lindir-server (and any other writers) for the duration of the migrate so row counts stay consistent. Create an empty target database first, then:

./scripts/migrate-db.sh \
  --from-engine mysql --from-dsn 'mysql://lindir:YOUR_PASSWORD@127.0.0.1:3306/lindir' \
  --to-engine postgres --to-dsn 'postgresql://lindir:YOUR_PASSWORD@127.0.0.1:5432/lindir' \
  --wipe

Or build/run directly:

dub build --config=migrate --compiler="${COMPILER:-ldc2}"   # ldc2 | dmd | gdc
./lindir-migrate --help

The tool applies migrations on the target, copies every known table row-for-row through the same drivers the server uses (so groups quoting, large TEXT, and nullable timestamps are handled), then verifies COUNT(*) per table.

When the target is MySQL/MariaDB, set max_allowed_packet high enough for inventory and vuln-cache payloads (see above) before running the migrate.

When the target is PostgreSQL, the DSN role must own the database or have GRANT USAGE, CREATE ON SCHEMA public (see PostgreSQL setup above). Otherwise migrations fail with permission denied for schema public.

Point the server config at the new dbEngine / dbDsn after a successful run.

After a migrate to a new host/URL, point enrolled agents at the new serverUrl (same hostname / enrollmentSecret — no re-enroll). Procedure: Client — Moving clients to a new server.

Schema

Versioned SQL under sql/migrations/. Migrations run automatically on server start.

Empty string → SQL NULL handling for MySQL is intentional in repos (timestamps / optional fields).