#1072 Add hot-path indexes and attribute osquery log batches to the authenticated node Pull request opened by javuto Add hot-path indexes and attribute osquery log batches to the authenticated node Indexes Adds 13 secondary indexes, each backing a query verified to run on every poll or check-in: | Table | Columns | Serves | | ----------------------------------------- | -------------------------------- | ----------------------------------------------- | | osquery_nodes | environment; hostname; localname | stats, paged node list, node detail/lookup | | node_queries | query_id, status | query completion check (osctrl-tls, per result) | | distributed_queries | environment_id, type, created_at | query lists, active counts | | osquery_status_data / osquery_result_data | uuid, created_at | node log pages, activity buckets | | osquery_query_data | name, created_at | query results page, CSV export | | audit_logs | created_at | audit page | | carved_files | environment_id, query_name | carves list | | tagged_nodes | node_id; admin_tag_id | tags per node page, tag counts | These are created by a new

dbutil.EnsureIndexes

helper rather than GORM struct tags. On MySQL, tagging an existing untagged string column flips its type from

longtext

to

varchar(191)

, and

AutoMigrate

then ALTERs the column on every install. That rebuilds the table, and fails or truncates on values longer than 191 characters. How the helper builds them: • Postgres:

CREATE INDEX CONCURRENTLY

, so writes are never blocked. An invalid index left by an interrupted build is dropped and rebuilt, unless another session is still building it. • MySQL: online DDL. A prefix length is applied only to columns that are

TEXT

on the live schema; a prefix on a short

varchar

is an error. • Where it runs: in the background at osctrl-api and osctrl-tls startup, never from osctrl-cli, where an exit mid-build would leave an invalid index. Log-table indexes are built when the logger DB is opened, since it can be a separate database.

last_seen

,

ip_address

and

bytes_received

are deliberately left unindexed. Every check-in rewrites them, and keeping them out of indexes preserves Postgres HOT updates. A guard test enforces this. Security: log batches trusted the sender-written

hostIdentifier

osctrl-tls authenticates each log POST by

node_key

, but then attributed the batch to the

hostIdentifier

inside it, which the sender controls. Any enrolled node could: • overwrite another node's metadata (hostname, user, hashes) and inflate its

bytes_received

• plant status/result rows under another node's UUID in the DB log sink • fire alerts attributed to another node, or evade node-scoped rules targeting itself

ProcessLogs

now takes the authenticated node and uses it for: • the metadata update, which is now

UpdateMetadata(nodeID, …)

by primary key • the UUID handed to every sink, including the S3 object key • DB log row attribution • alert attribution and node-scope checks;

AlertMatcher.MatchResultLogs/MatchStatusLogs

now take the sender's UUID External sinks still receive the raw batch, whose entries contain the sender-written

hostIdentifier

. Consumers should key on the exporter-supplied identity. Also fixed Alert detail for status logs was chosen via Go map iteration. The node page's "errors reported by this node" preset therefore reported an empty detail ~25% of the time, and its test was flaky (8/30 failures). Fields are now matched in a fixed order (message → filename → version); 0/200 failures. Validation •

go build ./...

,

go vet ./...

clean; all packages pass

go test ./...

. •

golangci-lint

reports only a pre-existing goimports issue in

pkg/queries/events_test.go

. • New tests cover: • index creation: idempotence, table prefixes, continuing past a failed index, and every declared index building against its real model • the HOT guard on

osquery_nodes

• the end-to-end attribution regression (node ATTACKER sending a batch claiming VICTIM), mutation-checked to fail against the old sink behaviour • alert framing/evasion • Not yet run: the Postgres/MySQL index tests (CONCURRENTLY build, invalid-index rebuild, MySQL TEXT prefix). They are gated on

OSCTRL_TEST_POSTGRES_DSN

/ `OSCTRL_TEST_MYSQL_DSN`; run with: go test ./pkg/dbutil/ ./pkg/nodes/ -run 'ExternalBackend|MySQLTextPrefix|InvalidPostgresIndex' -v Notes for reviewers • On upgrade, index builds run once in the background; large log tables may take a while on first start. Builds never block writes. • The single-column

uuid

indexes on the status/result log tables are now redundant with the new

(uuid, created_at)

ones. They're left in place because dropping the struct tag would trigger the MySQL column-type ALTER described above. jmpsec/osctrl