User Lifecycle

Gnok exposes a full SQL surface for managing the principals that authenticate against the engine and catalog — end users, service accounts, and their credentials. Everything described here ships as native SQL DDL and routes through the Gnok Catalog REST surface. State machines, session-epoch invalidation, GDPR-grade soft delete → expunge, audit partitioning, and compliance webhooks are all built in.

Lifecycle states

Every principal carries a status column with one of five values:

Status	How you get there	What it means
ACTIVE	Default for password-grant users; UNDROP	Can log in, everything works
INVITED	CREATE USER without PASSWORD	User exists but cannot log in until invitation is redeemed
SUSPENDED	ALTER USER … SUSPEND	Logins refused; rows preserved; session_epoch bumped
DEACTIVATED	DROP USER	Soft-delete; archive sweeper will tombstone after retention window
PROVISIONED	SCIM push	Reserved for IdP-synced identities

LOCKED is a derived state — computed from login_failures — not stored on the row.

Every state transition that should invalidate live sessions (DROP, SUSPEND, RENAME, password change, MFA change, PAT revoke, MANAGE privilege grant/revoke) bumps session_epoch. The engine's JWT validator rejects tokens whose sess_epoch claim lags the current value, so revocation takes effect before the JWT naturally expires.

CREATE USER

CREATE USER [IF NOT EXISTS] <username>
    EMAIL = '<email>'                 -- required, unique per tenant
    [ PASSWORD = '<initial-password>' ]
    [ DISPLAY_NAME = '<text>' ]
    [ DEFAULT_ROLE = <role> ]
    [ DEFAULT_WAREHOUSE = <warehouse> ]
    [ MUST_CHANGE_PASSWORD = { TRUE | FALSE } ]
    [ COMMENT = '<text>' ]
    [ IDENTITY_SOURCE = 'NATIVE' ]

• EMAIL is mandatory. Plaintext passwords are redacted from query_history before persistence.
• Omitting PASSWORD mints a one-shot invitation token the admin forwards out-of-band; see Invitations.
• The engine accepts either KEY VALUE, KEY = VALUE, or KEY=VALUE for every clause — editors that emit any of these forms work unchanged.
• Passwords are hashed with Argon2id (t=3, m=64MiB, p=1). The tenant password policy (length, char classes, no-username, history reuse) is enforced before the hash is written.

ALTER USER

-- Metadata mutations. Pass multiple clauses in one statement.
ALTER USER <username> SET
    [ PASSWORD = '<new>' ]
    [ EMAIL = '<new>' ]
    [ DISPLAY_NAME = '<text>' ]
    [ DEFAULT_ROLE = <role> ]
    [ DEFAULT_WAREHOUSE = <wh> ]
    [ MUST_CHANGE_PASSWORD = { TRUE | FALSE } ]
    [ COMMENT = '<text>' ];

ALTER USER <username> UNSET <col1>, <col2>, ...;
ALTER USER <username> SUSPEND;
ALTER USER <username> UNSUSPEND;
ALTER USER <username> UNLOCK;
ALTER USER <username> RENAME TO <new-username>;

Self-service password rotation (caller's own principal, verifies the old password before writing the new hash):

ALTER USER SELF SET PASSWORD '<new>' OLD PASSWORD '<old>';

DROP / UNDROP / EXPUNGE

-- Soft-delete: status → DEACTIVATED, session_epoch bumped.
DROP USER [IF EXISTS] <username> [CASCADE | RESTRICT];

-- Restore a DEACTIVATED user before the archive sweeper runs.
UNDROP USER <username>;

-- Hard-clear PII. Status must be DEACTIVATED, legal_hold must be false.
EXPUNGE USER <username> REASON '<text>';

• DROP … RESTRICT (default) refuses if the user holds any tenant-role grants.
• DROP … CASCADE drops the grants alongside the user.
• An archive sweeper tombstones DEACTIVATED rows after the configurable window (default 30 days). is_bootstrap_principal and legal_hold rows are skipped.
• EXPUNGE rewrites the name, clears credentials, tokens, and invitations. The row stays for audit FK integrity; the username is released for reuse.

SHOW / DESCRIBE

SHOW USERS [LIKE '<pattern>'] [WHERE STATUS = '<status>'];
DESCRIBE USER <username>;

DESCRIBE surfaces status, email, display_name, default_role, last_login_at, must_change_password, session_epoch, and audit timestamps. It never returns hashes, secrets, or tokens.

Service accounts + keypairs

Machine principals authenticate via registered keypairs (RS256 / RS512 / ES256 / ES384) or Personal Access Tokens. They never hold a password and get no email.

CREATE SERVICE ACCOUNT [IF NOT EXISTS] <name>
    [ DEFAULT_ROLE = <role> ]
    [ COMMENT = '<text>' ];

DROP SERVICE ACCOUNT [IF EXISTS] <name>;

ALTER SERVICE ACCOUNT <name> ADD KEYPAIR
    KEY_ID '<kid>'
    PUBLIC_KEY '<PEM>'
    [ ALGORITHM <RS256|RS512|ES256|ES384> ];

ALTER SERVICE ACCOUNT <name> DROP KEYPAIR [IF EXISTS] KEY_ID '<kid>';

SHOW SERVICE ACCOUNTS;

DROP KEYPAIR is a soft revoke (revoked_at = NOW()) so the audit trail can resolve kids that were briefly valid. The unique constraint on (principal_id, key_id) is scoped to active rows only — you can re-register the same kid after revoking.

OAuth client credentials (for query engines)

Keypairs (jwt-bearer) suit custom services, but most query engines — Trino, Spark, PyIceberg — authenticate to the Iceberg REST catalog with the OAuth2 client-credentials grant. A service account can be given one or more client-id:client-secret pairs; the resulting token assumes the service account's identity (tenant + roles), so it can read and write the catalog exactly as that account is authorized to. This is what lets an engine use the standard auto-refreshing OAuth2 flow instead of a hand-pasted static token.

Connecting external engines isn't self-service for new organizations yet. Create the service account and its grants in SQL, then email support@gnok.io to have a client issued for it, rotated, or revoked. The client secret is shown once.

Secrets are stored SHA-256 and shown only at creation/rotation. See Connecting external engines for end-to-end engine configuration.

Personal access tokens (PATs)

PATs are the JDBC-friendly alternative to passwords. They're minted once (plaintext returned exactly one time; the catalog only stores the SHA-256 hash) and exchanged for short-lived JWTs via the OAuth grant_type=pat flow.

CREATE PERSONAL ACCESS TOKEN <name>
    [ FOR <username> ]          -- admin-for-other; self-issue when omitted
    [ TTL '<n> days' ]
    [ SCOPE '<read write ...>' ];

DROP PERSONAL ACCESS TOKEN [IF EXISTS] <name> [ FOR <username> ];
SHOW PERSONAL ACCESS TOKENS [ FOR <username> ];

Exchange a PAT for a JWT:

POST /v1/oauth/tokens
grant_type=pat&pat=<token-plaintext>&client_id=gnok-sql

The returned JWT carries auth_method=pat so downstream audit sinks can distinguish password grants from PAT grants.

Second factor (MFA)

Second factors are set up and used on the Studio sign-in page:

• Administrators must use a second factor. An administrator without one sets it up the first time they sign in: a passkey or an authenticator app.
• Passkeys. Any user can add or remove passkeys in Studio from the user menu → Passkeys. On an account that has an authenticator but no passkey, Use passkey on the sign-in page sets one up after you enter your authenticator or recovery code. A passkey is a second factor; the password is still required.
• Recovery codes. When you turn on an authenticator app on the sign-in page, Gnok shows recovery codes once. Each code works once in the Authenticator or recovery code field.

See account access for the step-by-step flow.

Authenticator app from SQL

These statements manage the same authenticator for your own user from a SQL session:

ALTER USER SELF ENROLL MFA TOTP;               -- returns (secret, otpauth_uri)
ALTER USER SELF VERIFY MFA TOTP '<6-digit-code>';
ALTER USER SELF DISABLE MFA WITH TOTP CODE '<6-digit-code>';

ENROLL returns a setup key and an otpauth:// URI for your authenticator app; VERIFY with a current code turns the authenticator on. DISABLE requires a current code — there's no unauthenticated path.

• Gnok accepts these statements only within five minutes of signing in with your password and your current second factor. Sign in again if they're refused.
• Enrolling from SQL doesn't display recovery codes. Prefer the sign-in page, which shows them.
• DISABLE is refused while your organization requires a second factor for your account, as it does for administrators.

Invitations

CREATE USER without PASSWORD puts the user in INVITED and returns an invitation URL containing a one-shot token. The invited party redeems it:

REDEEM INVITATION '<token>' WITH PASSWORD '<new-password>';

Redemption applies the tenant password policy, writes the Argon2id hash, flips INVITED → ACTIVE, clears must_change_password, and bumps session_epoch. The token is single-use — re-redemption returns InvalidInvitation.

Legal hold + compliance

ALTER USER <username> SET LEGAL HOLD REASON '<text>';
ALTER USER <username> CLEAR LEGAL HOLD;

While legal hold is engaged:

• The archive sweeper skips the row (soft-deletes never tombstone).
• EXPUNGE USER refuses with LegalHoldActive.
• All other operations (including login suspension) remain available.

Per-tenant PII quarantine keeps downstream hashes of erased data so detection pipelines can audit exposure without holding the plaintext.

Webhooks

Every audit event — create_user, drop_user, create_pat, drop_service_account, etc. — fans out to every matching webhook subscription inside the same transaction, so delivery is at-least-once and transactional with the audit write.

CREATE WEBHOOK [IF NOT EXISTS] <name>
    URL = '<https://…>'
    SECRET = '<hmac-key>'        -- ≥16 chars; never surfaced through SHOW
    [ EVENTS ('prefix.', 'create_pat', …) ];

DROP WEBHOOK [IF EXISTS] <name>;
SHOW WEBHOOKS;

The delivery worker POSTs to each subscription every 10s with:

• Content-Type: application/json
• X-Gnok-Event-Type: <event>
• X-Gnok-Event-Id: <uuid> (idempotency key)
• X-Gnok-Signature: <hex> (HMAC-SHA256 of the body with the subscription's secret)
• X-Gnok-Delivery-Attempt: <n>

On 2xx the row flips to delivered. On 4xx/5xx/network errors it moves to retrying with exponential backoff (30s → 2m → 8m). After three attempts it lands in failed — replay by flipping the row back to pending. Secrets never appear in SHOW WEBHOOKS or in any API response.

Delegable privileges (MANAGE)

Organization administrators can hand off user-management duties without handing over full administrator rights:

Privilege	Grants
MANAGE USERS	CREATE / ALTER / DROP / SUSPEND / UNLOCK users
MANAGE PATS	Create and revoke PATs for other principals
MANAGE SERVICE ACCOUNTS	Full CRUD on service accounts and their keypairs

GRANT  MANAGE USERS              TO   USER <name>;
GRANT  MANAGE PATS               TO   USER <name>;
GRANT  MANAGE SERVICE ACCOUNTS   TO   USER <name>;

REVOKE MANAGE USERS              FROM USER <name>;
REVOKE MANAGE PATS               FROM USER <name>;
REVOKE MANAGE SERVICE ACCOUNTS   FROM USER <name>;

Only organization administrators (the tenant_admin role) can GRANT / REVOKE these privileges — holders of MANAGE USERS cannot redelegate. Every grant/revoke bumps the target's session_epoch so outstanding tokens lose the stale privilege set on the next request. The privileges apply to both SQL statements and catalog requests.

Audit log

Every mutation above writes a row to user_audit_log (tenant_id, actor_id, target_id, target_name, event_type, details JSONB, created_at). The table is range-partitioned by created_at:

• A historic catch-all covers MINVALUE → start-of-current-month.
• Monthly partitions are pre-created 24 months ahead.
• user_audit_log_ensure_future_partitions(N) (runs hourly) keeps partitions ahead of writes.
• user_audit_log_drop_old_partitions(days) (runs daily) drops partitions whose upper bound exceeds the retention window (default 7 years / SOC 2). The historic catch-all is never dropped.

Service-managed settings

Service-side archival, retention, webhook delivery, and maintenance settings are managed by Gnok. Your account terms and policies determine applicable retention commitments. Use the authorized lifecycle controls above for individual users.

Idempotent patterns

All of these are safe to re-run:

-- Cleanup then recreate
DROP USER IF EXISTS demo_alice CASCADE;
CREATE USER IF NOT EXISTS demo_alice EMAIL = 'demo@ex.com' PASSWORD = 'Initial_Pass_1!';

-- Rotate a service-account keypair
ALTER SERVICE ACCOUNT demo_svc DROP KEYPAIR IF EXISTS KEY_ID 'kp-1';
ALTER SERVICE ACCOUNT demo_svc ADD KEYPAIR
    KEY_ID 'kp-1' PUBLIC_KEY '<PEM>' ALGORITHM RS256;

-- Clean up PAT even if the owning user was already dropped
DROP PERSONAL ACCESS TOKEN IF EXISTS alice_laptop FOR demo_alice;

-- Disable-then-enable a webhook
DROP WEBHOOK IF EXISTS audit_sink;
CREATE WEBHOOK IF NOT EXISTS audit_sink
    URL = 'https://hooks.example.com/gnok'
    SECRET = 'gnok_webhook_secret_16ch!'
    EVENTS ('user.', 'create_pat');

Two statements are intrinsically non-idempotent and must be handled interactively:

• ALTER USER SELF SET PASSWORD '<new>' OLD PASSWORD '<old>' — needs the prior password.
• ALTER USER SELF ENROLL MFA TOTP — fails once already enrolled; pair with DISABLE MFA or a fresh principal.
• REDEEM INVITATION '<token>' WITH PASSWORD '<new>' — tokens are single-use by design.