Operator governance

Operator governance controls the people and tools that administer the database. It is separate from the application-user auth providers, claims, grants, and endpoint quotas described in Application access SQL.

Secure first boot

Use a persistent data directory and a mounted secret file:

nyxdb \
  --host=127.0.0.1 \
  --port=7510 \
  --data-dir=/srv/nyxdb/data \
  --require-auth \
  --admin-password-file=/run/secrets/nyxdb-admin-password

On a boot where the user catalog is empty, the engine creates admin with role admin through the normal journaled user path. Secret-source precedence is:

1. --admin-password-file=<path>;
2. NYXDB_ADMIN_PASSWORD_FILE;
3. --admin-password=<password>;
4. NYXDB_ADMIN_PASSWORD; then
5. a generated 24-character password printed once when no override exists.

The file forms are preferred because they keep the secret out of process arguments and environment capture. Trailing newlines are stripped; an explicit missing or empty file is fatal. The seed runs only when no user exists. With --data-dir, it persists and is not recreated on the next boot; without persistence, the catalog and seed are ephemeral. A configured file source is still read and validated on every process start even after seeding. Keep it mounted and readable, or remove the flag/environment source from the reviewed startup command once provisioning is complete.

Manage database users

Database users are operator/login identities stored in the engine catalog.

CREATE USER analyst
PASSWORD 'initial-secret'
ROLE 'readonly';

ALTER USER analyst PASSWORD 'rotated-secret';
ALTER USER analyst ROLE 'operations';

SHOW USERS;

DROP USER analyst;

Plaintext passwords are hashed before canonical DDL is journaled. Replay uses the hash form without re-hashing. SHOW USERS and system.users expose only:

name, role, created_at, uuid, version

There is never a password or hash column. Password changes invalidate the credential-verification cache. The server also throttles repeated failed operator authentication attempts; clients should not build aggressive retry loops around auth failed.

The operator wire auth payload is connection-scoped:

OP_AUTH 0x09 payload := "\0system\0" user "\0" password

Success returns authenticated\n; a missing user, wrong password, or throttle refusal returns the same sanitized auth failed. Every socket a console or client opens must authenticate independently.

Privilege model

An authenticated operator role is re-resolved from the current user catalog on privileged operations. A changed role therefore takes effect without trusting the role captured at login; a deleted user fails closed on subsequent privileged work.

Two grant domains

The syntax makes the trust boundary visible.

Application domain

GRANT read, subscribe
ON VIEW customer_fills
TO ROLE 'customer';

REVOKE subscribe
ON VIEW customer_fills
FROM ROLE 'customer';

SHOW GRANTS;
SELECT * FROM system.grants;

This domain authorizes the role claim returned by an application auth provider. It does not authorize database operators. Although the catalog grammar accepts table, view, and materialized-view targets plus five verbs, the current end-user wire consumes only read and subscribe grants on logical views. Other application-policy rows are future-facing vocabulary, not a current table, materialized-view, or write invocation path.

Operator domain

GRANT read, subscribe
ON TABLE fills
TO OPERATOR ROLE 'readonly';

REVOKE subscribe
ON TABLE fills
FROM OPERATOR ROLE 'readonly';

SHOW OPERATOR GRANTS;
SELECT * FROM system.operator_grants;

This domain authorizes roles from system.users. It does not authorize application users. Operator reads are checked against every scanned base relation, so a view grant alone does not bypass an ungranted base table. system.* relations remain readable by any authenticated operator for observability. The same checks apply to the canonical stored query behind operator-class endpoint/view execute and subscribe operations; those calls remain exempt from application endpoint quotas.

Both grammars accept read, subscribe, insert, delete, and definition, but the current named non-admin operator enforcement path uses read and subscribe. Writes, DDL, GRANT/REVOKE, user management, and block ingest require the admin-implicit manage privilege; manage is not separately grantable in this release. Do not treat an insert or delete catalog row as delegated write access.

Policies are keyed by relation UUID and journal as canonical DDL. Drop-and-create creates a new identity and therefore requires a new grant review.

Governance audit stream

system.governance_log is a live, append-shaped operational relation:

SELECT ts_ms, event, user, role, relation, relation_uuid,
       dimension, allowed, observed, source, event_id
FROM system.governance_log
ORDER BY ts_ms DESC
LIMIT 200;

STREAM SELECT *
FROM system.governance_log;

Representative events include:

The table is volatile: it is not journaled or restored, and restart begins with an empty log. The current fixed count capacity is 10,000 rows with drop-oldest behavior. Export or stream it to durable monitoring if audit retention is a requirement. A live tail also receives retention retractions; consumers must handle them as maintained-result changes rather than assuming an append-only external log.

Detailed operator diagnostics also land in system.query_log. Application-user wire errors remain sanitized and should be correlated with operator telemetry by time, relation identity, and deployment revision.

Password-bearing CREATE USER and ALTER USER statements are replaced with the fixed label <operator credential statement redacted> before query-profile publication. The raw secret, canonical hash, and original credential length are therefore absent from live system.queries, retained system.query_log, and parser profiler text, including malformed password clauses. Unrelated SQL that merely reads a column named password remains verbatim.

Rollout procedure

1. Pin and stage the exact revision. Exercise operator login, a denied non-admin read, application auth, endpoint execution, subscription, revocation, and quota enforcement against the release artifact.
2. Keep the listener private. Bind loopback or a private address. Add reviewed TLS termination, Origin policy, network ACLs, and connection protection before any untrusted client path.
3. Persist and back up. Configure --data-dir, take a backup, and prove a restore before changing access policy.
4. Provision the admin through a secret file. Keep the launch credential out of image layers, argv, and environment capture. Separately verify that the exact release emits only <operator credential statement redacted> for password-bearing CREATE USER and ALTER USER telemetry before using those statements operationally.
5. Enable --require-auth. Verify an unauthenticated query receives authentication required, while one-time capability negotiation, ping, and both operator and end-user auth remain available.
6. Create named operators. Give daily users non-admin roles. Keep emergency admin access narrow and monitored.
7. Grant the minimum read surface. Use TO OPERATOR ROLE; verify that a same-named application TO ROLE grant gives the operator nothing.
8. Update every client socket. The console and any custom operator client must replay operator auth after reconnect and on every stream connection.
9. Export audit data. Tail governance and query logs before enabling application traffic, because the in-engine governance log does not survive restart.
10. Run negative tests. Prove raw SQL and write operations fail on an end-user connection, a non-admin cannot manage or write, revoked sessions terminate, and quota breaches do not cause reconnect storms. For every claimless endpoint/view, prove a named non-admin operator is denied until all scanned base relations carry the required operator grant.
11. Drain before published-object changes. Reach zero live endpoint subscribers and terminate remaining transports before replace/drop, claim, provider, predicate, or limit changes. Existing subscriptions do not rebind or revoke, and endpoint drop removes their governor/metering state.

Rotation and incident response

• Rotate an operator password with ALTER USER; reconnect clients with the new secret and verify the old password fails.
• Demote a user role before broad grant removal when an immediate reduction is required, then verify the next privileged operation uses the new role.
• Drop a compromised operator user and terminate its client connections at the network/session layer as part of containment.
• Revoke an application session by removing it from the streaming auth-gate result; clients receive auth revoked and must obtain a new token.
• Do not edit tenant, role, or identity claims in place and assume an existing subscription changed. Its bindings stay frozen. Remove the old token, issue a new one, and require reconnect and resubscribe.
• Tighten a query-driven limit by updating its source data. Concurrent subscription overage is killed newest-first; observe the killed event.
• Do not “fix” a denial by enabling default-open mode. Restore the precise grant or credential contract instead.

Review checklist

• --require-auth is present in the reviewed startup command.
• Native TLS absence and the external wss:// boundary are documented.
• Admin provisioning uses a mounted secret file.
• Daily operator accounts are named and non-admin.
• Application and operator grants have separate owners and reviews.
• Non-admin write and management denials are tested.
• Claimless endpoint/view EXEC and SUB paths are negatively tested against missing, granted, and revoked base-relation operator privileges.
• Every custom socket authenticates before its first privileged operation.
• Password-bearing user DDL is redacted from query telemetry in the pinned engine revision.
• Governance and query logs are exported to durable monitoring.
• Application revocation and reconnect behavior is tested.
• Live subscriptions are drained before endpoint security, query, provider, claim, or limit changes.
• The Experimental capability decision is revisited on every upgrade.