#1134 Optional DB read replica for dashboard and node list reads Pull request opened by javuto Optional DB read replica for dashboard and node list reads Problem Every osctrl service sends all database traffic to one primary. The dashboard and the node list read heavily but can tolerate a little replication lag. They compete with the write path (check-ins, query results, enrollment) for the primary. Change Each service can now open a second connection pool to a read replica. It is off by default, and only reads that are explicitly marked use it. Setting (per service,

db.read

in

tls.yml

/

api.yml

) •

enabled

(default

false

),

host

,

port

,

username

,

password

,

sslmode

,

maxIdleConns

,

maxOpenConns

. • Matching flags and environment variables:

--db-read-*

and

DB_READ_*

. • Empty fields inherit the primary's value. Type, database name and connection lifetime always follow the primary. • Not supported with SQLite (the service refuses to start). Backend (

pkg/backend

) •

DBManager.Read

is the replica pool when enabled. Otherwise it is the primary

*gorm.DB

itself, so callers can use it unconditionally. • If the replica can't be reached at startup, startup fails or retries the same way as for the primary. The primary pool is closed on that failure, so retries don't leak connections. •

Check()

, and therefore the DB health monitor, pings both pools. Routing (explicit, per call site) •

NodeManager

,

Queries

and

AuditLogManager

gain a

ReadDB

field and a

Replica()

method.

Replica()

returns a read-only copy backed by the replica, or the manager itself when no replica is configured. • The copies carry no node cache, no query dispatch cache and no event publisher, and audit recording is off, so a replica read can't cause side effects. • osctrl-api routes these handler calls to the replica: • Dashboard: node stats, platform counts, osquery version counts, active query and carve counts, and the environment activity chart. • Node lists: active, inactive and all nodes, and the paged and searchable list. • Everything else stays on the primary: authentication, enrollment, node-key lookups, permissions, query and carve lifecycle, TLS alerts, query target expansion and the CLI. That includes other callers of the same manager methods. • osctrl-tls accepts the setting and connects, but routes nothing to the replica yet, and logs a warning saying so. Security and operational impact • With the setting off, behaviour is unchanged. • Replica reads may be a moment out of date. Only display paths use them, never authorization or a read in a write flow. • A replica outage marks the service degraded through the DB health check, even though the primary still serves writes. • The replica password is handled the same way as the primary DB password. The existing exposure of the

db

section through

service_config

, and the debug-level config log in osctrl-tls, is not addressed here. Validation •

go vet ./...

and

go test ./...

pass. • New tests: • replica config inheritance and overrides, and refusal with SQLite; •

Read

defaulting to the primary connection when disabled; • startup failing when the replica is unreachable; •

NodeManager.Replica()

reading from the replica while the manager keeps reading the primary; • the replica copies of

Queries

and

AuditLogManager

carrying no cache or publisher, and never recording audit entries. • Not validated: a real Postgres or MySQL replica, and replication lag behaviour. jmpsec/osctrl