IF Commons GitHub

sql-osquery

0058Active

0058-sql-osquery · owner if · domain data

Requirements

FR-001checkpassing

The schema MUST be imported from osquery's published schema of a version, never written by hand: tools/osquery-schema.ts MUST write the file of a version from a checkout of osquery/osquery-site, trimmed to the fields the importer reads, with where it came from (the repository, the commit, the path, the SHA-256 of the file it was trimmed from) and its licence; readSchema MUST read it as a value and refuse a file that is not osquery's or a column of a type osquery's schema does not have.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: the published schema of a version is read, never written by hand: the file says where it came from (the repository, the commit, the SHA-256 of the file it was trimmed from, the licence), the tool that makes it keeps only the fields the importer reads, and a file that is not osquery's is refused

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/mod.ts, data/sql/extensions/osquery/schema.ts, data/tools/osquery-schema.ts

Tests: passed: suite/osquery.test.ts the published schema of a version is read, never written by hand: the file says where it c

FR-002checkpassing

importSchema MUST make a logical schema of the published schema through adopt (0055), stamped with the version of osquery (osquery.version on the schema and the domain named by it), with each table carrying the platforms it exists on and whether it is evented, each column whether it is hidden, required or an index and, where it exists on fewer platforms than its table, which; a platform given MUST leave out the tables and columns that do not exist on it; and the schema MUST make the same tables on SQLite, DuckDB and PostgreSQL.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: the schema is imported through adopt into a logical schema that is stamped with the version, carries the platforms of each table and of the columns that exist on fewer, and makes the same tables on each dialect; a platform leaves out what is not there

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/schema.ts

Tests: passed: suite/osquery.test.ts the schema is imported through adopt into a logical schema that is stamped with the versio

FR-003checkpassing

The columns that hold personal data (the users and who is logged in, the groups a user is in, their keys, their shell history, their browser extensions, the network addresses and names that identify them) MUST be marked with the DPV-PD category and what they are held for, from a table of rules that is data; every rule MUST name a table and a column that osquery's published schema has; no other column is personal.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: the columns that hold personal data are marked with the DPV-PD category and what they are held for, from a table of rules; the other columns are not

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/classify.ts, data/sql/extensions/osquery/schema.ts

Tests: passed: suite/osquery.test.ts the columns that hold personal data are marked with the DPV-PD category and what they are

Cited by: Data, sql/extensions/osquery/classify, sql/extensions/osquery/mod

FR-004checkpassing

The driver MUST run only a SELECT, or a WITH that ends in one, of one statement: anything that writes, more than one statement, an empty statement and anything else MUST be refused as an error value before a process is started, with words in strings and comments not counted as code; exec MUST always be refused.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: only a SELECT of one statement is run: anything that writes, more than one statement, an empty one and what is not a SELECT are refused as error values before a process is started, words in strings and comments are not code, and a table that acts on the world is refused

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/driver.ts, data/sql/extensions/osquery/mod.ts

Tests: passed: suite/osquery.test.ts only a SELECT of one statement is run: anything that writes, more than one statement, an e

Cited by: Data, sql/extensions/osquery/driver

FR-005checkpassing

A table that acts on the world and not only reports it (carves, curl, curl_certificate) and any table the caller denies MUST be refused as an error value that names it, before a process is started.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: only a SELECT of one statement is run: anything that writes, more than one statement, an empty one and what is not a SELECT are refused as error values before a process is started, words in strings and comments are not code, and a table that acts on the world is refused

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/driver.ts

Tests: passed: suite/osquery.test.ts only a SELECT of one statement is run: anything that writes, more than one statement, an e

FR-006checkpassing

A SELECT MUST be run as osqueryi --json "<sql>" through the function the caller gives that runs a program (so that the library depends on no runtime, and ssh host osqueryi is a prefix), its rows MUST have the types of the schema (osquery prints every value as text), a table that is not on the platform MUST be a typed error that says where it is, and what osquery says (a table it does not have, a failure, output that is not a list of rows) MUST be an error value; the driver MUST reject with the typed error.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: a SELECT runs as `osqueryi --json`, its rows have the types of the schema, a table that is not on the platform is a typed error that says where it is, and what osquery says (a missing table, a failure, output that is not JSON) is an error value; suite/osquery.test.ts: where osqueryi is installed it is run for real: the machine's own users are rows of the types the schema gives

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/driver.ts

Tests: passed: suite/osquery.test.ts a SELECT runs as `osqueryi --json`, its rows have the types of the schema, a table that is; passed: suite/osquery.test.ts where osqueryi is installed it is run for real: the machine's own users are rows of the ty

Cited by: Data, sql/extensions/osquery/driver

FR-007checkpassing

A machine MUST be usable as a target of a fan-out (0059): osqueryTarget MUST give its name, its platform and the read-only driver, and redactPersonal MUST hide the columns that FR-003 marks as personal, so that a result of many machines does not carry what identifies a person.

Enforced (check): .github: cd commons/data && bun run verify — suite/federate.test.ts: machines asked through osquery are targets: a table that is on one platform and not another is a typed error for the machine that lacks it and rows for the one that has it, the merge has both, and personal columns can be hidden

Proved by: data/suite/federate.test.ts

Tests: passed: suite/federate.test.ts machines asked through osquery are targets: a table that is on one platform and not anothe

FR-008checkpassing

The tables of a schema for a platform MUST be made into a catalog of the SQL language library by osqueryCatalog (the columns that are not hidden, with the type osquery gives them), so that a statement can be bound to the tables of the machine before it is sent; a table that the platform does not have MUST be left out.

Enforced (check): .github: cd commons/data && bun run verify — suite/osquery.test.ts: the schema is made into a catalog for a platform: a table of the platform is there with its columns, one that the platform lacks is not

Proved by: data/suite/osquery.test.ts

Written in: data/sql/extensions/osquery/catalog.ts

Tests: passed: suite/osquery.test.ts the schema is made into a catalog for a platform: a table of the platform is there with it

Cited by: Data, sql/extensions/osquery/catalog

Out of scope

  • The osqueryd daemon, scheduled queries, packs and fleet TLS; the check object of GRC and the machine verbs of headless, which are other libraries' and use this one.
  • Running a query on a machine of another platform than the one asked: the platform is the caller's to give.
  • Installing osquery: the tests answer the program runs with a function of their own where osqueryi is not on the path and run it for real where it is.

Edge cases

  • A name after FROM that is not a table of osquery (a common table expression, an alias) is left to osquery, which says if it is not there, per FR-006.
  • A column that two tables type differently is left as osquery printed it, per FR-006.
  • A table the schema does not name on a newer osquery is unknown-table from osquery's own message, per FR-006.

Assumptions

  • The schema files are of the versions of osquery that are published; a newer version is another file made by the tool, not an edit.
  • The classification is of the columns whose value identifies a person or what they do; it is a table of rules that a review changes, and FR-003's check holds it to the real schema.

Open questions

OQ-1open question

Whether the classification is also given as a governance model of its own, so that the register of personal data of the documentation site (0057) lists osquery's columns beside the arca models.

Key entities

  • OsquerySchema - the published schema of one version of osquery, with its provenance.
  • Importer - importSchema: the schema made a logical schema through adopt.
  • Driver - the read-only Driver over osqueryi --json.

Success criteria

SC-001success criterion

An agent can ask a machine for its users with a typed SELECT, gets rows of the right types, cannot change the machine, and is told as a value when a table does not exist on that platform.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/osquery/catalog, sql/extensions/osquery/classify, sql/extensions/osquery/driver, sql/extensions/osquery/mod, sql/extensions/osquery/schema

Titles, headings, exports, tables, columns and requirements. Esc closes.