Gateway
Connect a database
The read-only check, the least-privilege role, attaching tables, the tenant column, several databases behind one gateway, and how main stays fresh.
Self-hostedChronos Gateway runs on your own servers today. Releases and install script. The engine under it: Chronos DB, self-hosted
Three steps
Connecting takes three steps: look at the database, make a role for the gateway, then attach the tables agents may work on. The URL says which database it is (postgres://, mysql://, mariadb://, mongodb:// or mongodb+srv://), and the same calls work for each. The examples use a table leads whose tenant column is org_id, and a developer key in $KEY. Use your own names.
Look first: POST /v1/connect/check
Read-only. Nothing is written, and the URL isn’t kept. It answers which tables can be attached, which column looks like your tenant, and the statements that make the gateway’s role.
Make the gateway’s role
A database admin runs the statements from the check, once, with a long random password.
Attach: POST /v1/connect
With the new role’s URL, the tables and the tenant column. The engine copies the tables, and the gateway starts the engine and sets up the merge policy.
The read-only check
curl -s localhost:7400/v1/connect/check -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"url": "postgres://admin:ADMIN_PASSWORD@db.internal:5432/app"}'The gateway connects with that URL, reads the catalog, and forgets the URL. It takes a developer key.
| Field | Meaning |
|---|---|
| url | A URL that can read the catalog. An admin’s is fine here: it is used once and kept nowhere. |
| schema | Postgres only: the schema to look at. Default public. Beside MySQL, MariaDB and MongoDB the schema is the URL’s database. |
# MySQL or MariaDB-d '{"url": "mysql://admin:ADMIN_PASSWORD@db.internal:3306/shop"}'-d '{"url": "mariadb://admin:ADMIN_PASSWORD@db.internal:3306/shop"}'# MongoDB: the URL must name the database; its collections are the ones attached-d '{"url": "mongodb://admin:ADMIN_PASSWORD@db.internal:27017/shop"}'What the check answers
{role, superuser, server_version, database, schema, source, tables: [{name, rows, key, columns, generated, partitioned, row_security, attachable, refused, sensitive}], attachable, tenant_candidates: [{column, tables, of}], role_sql, warnings, follow: {ready, missing}}| Key | Says |
|---|---|
| source | postgres, mysql or mongodb. Beside MySQL and MariaDB, flavor says mysql or mariadb. |
| role, superuser | Who the URL logs in as, and whether that is a superuser (an administrator, beside MySQL and MongoDB). |
| server_version, database | The server’s version and the database looked at. Too old a server is refused here, saying so. |
| tables | Each table (or collection): name, estimated rows, its key, columns, whether it is attachable, and if not, why (refused). |
| tables[].generated | Generated columns: attached, worked out by the database, never written by an agent. |
| tables[].partitioned | Postgres: a partitioned table, listed whole. Its partitions aren’t listed. |
| tables[].row_security | Postgres: row-level security is on. role_sql then gives the role a policy that reads every row. |
| tables[].sensitive | Columns whose names suggest values agents shouldn’t read (email, phone, ssn, password, token, card, address and the like). A hint, by name, for PUT /v1/masks. |
| tables[].auto_increment, inserts | MySQL and MariaDB: the columns the database numbers, and whether runs can add rows. inserts is false where the column is narrower than INT or is BIGINT UNSIGNED: a run can change and delete that table’s rows, not add any. |
| tables[].fields | MongoDB: of each top-level field in the first 100 documents, {name, type, in, of, column}. column is true when every one of those documents has it as a string, an integer or an ObjectId, so it can be a column of its own. |
| attachable | The names of the tables that can be attached. |
| tenant_candidates | Columns that look like a tenant column, in the order they’re preferred, each with how many attachable tables have it: tenant_id, org_id, organization_id, account_id, workspace_id, team_id, company_id, customer_id, project_id, user_id. |
| role_sql | The statements that make a least-privilege role for the gateway, for the attachable tables. A mongosh script beside MongoDB. |
| warnings | For example: the URL logs in as a superuser (make the role and connect with it instead), or points at a read replica (attach reads from it, but merges must write to the primary). Beside MySQL and MongoDB, what does not work there yet. |
| follow | Whether GATEWAY_FOLLOW can work there (ready), and what is missing. |
What is refused, and why
| Database | Refused |
|---|---|
| Postgres | A view (attach the tables it reads). A table with no primary key, nor a unique index on NOT NULL columns. A table that inherits or is inherited from. A table with row-level security and a restrictive policy for every role (the copy could hold only some of its rows). Postgres 11 or older. |
| MySQL, MariaDB | Views, tables not InnoDB, tables without a primary key, and tables with binary, BIT or spatial columns. MySQL older than 8.0, MariaDB older than 10.5. |
| MongoDB | A standalone server, a sharded cluster (a mongos), views, time-series and capped collections. MongoDB older than 5.0. A URL that names no database. |
TLS options in the URL
| Database | Options |
|---|---|
| Postgres, the check | Reads sslmode as psql and the engine do. prefer (the default) and require encrypt without checking whose certificate it is, so Supabase’s pooler and a Postgres with a self-signed certificate (as Debian and Ubuntu install it) connect as they are. verify-ca and verify-full check it, against the public web certificate authorities only (not sslrootcert). With a private CA and verify-full, use sslmode=require for the check, or skip the check and write the role from the statements below. Neon’s connection string works as its console gives it (channel binding). |
| Postgres, the attach | sslmode disable, prefer, require, verify-ca and verify-full, and sslrootcert=<file.pem> for a private CA (a path the gateway’s user can read). Without sslrootcert, verify-full uses the system’s CA bundle. |
| MySQL, MariaDB | ssl-mode DISABLED, PREFERRED (the default), REQUIRED, VERIFY_CA or VERIFY_IDENTITY, and ssl-ca=<file.pem>. Logins: caching_sha2_password (MySQL 8’s default) and mysql_native_password. A login that needs the password itself is only sent over TLS. |
| MongoDB | MongoDB’s own connection string (mongodb:// or mongodb+srv://), with its own options (replicaSet=rs0&tls=true). |
The role: Postgres
Run role_sql from the check as a database admin (for example in psql). Choose a long random password. For the two tables leads and accounts in public, it reads:
create role chronos_gateway login password '<a long random password>';grant usage on schema "public" to chronos_gateway;grant select, insert, update, delete on "public"."leads", "public"."accounts" to chronos_gateway;grant usage on all sequences in schema "public" to chronos_gateway;create schema if not exists chronos;create table if not exists chronos.merges (id text primary key, at timestamptz not null default now());create table if not exists chronos.merge_rows (merge_id text not null, n int not null, table_name text not null, op text not null, key jsonb, before jsonb, after jsonb, at timestamptz not null default now(), primary key (merge_id, n));create index if not exists merge_rows_at on chronos.merge_rows (at);grant usage on schema chronos to chronos_gateway;grant select, insert on chronos.merges to chronos_gateway;grant select, insert, delete on chronos.merge_rows to chronos_gateway;That is all the role may do: read and write the attached tables, use their sequences, record each merge in its own table chronos.merges, and keep what each merge wrote in chronos.merge_rows, the table undo works from. It is not an owner and not a superuser.
- Row-level security. For each attachable table with row-level security on (Supabase turns it on for every table),
role_sqladds a policy that shows the role every row. Your app’s policies keep holding everyone else. A table with a restrictive policy for every role is refused. - Postgres 12 or 13. Run
set password_encryption = 'scram-sha-256';in the same session beforecreate role. The engine refuses to send a password as MD5 or in the clear unless the connection issslmode=verify-full. - Writing as your end user. For
act_as, so runs write as your end user’s role, once:GRANT authenticated TO chronos_gateway(with your role’s name). See Runs. - Following changes. For
GATEWAY_FOLLOWthe role also needsREPLICATION(ALTER ROLE ... REPLICATION; RDS:GRANT rds_replication), and Postgres needswal_level = logical. The check’sfollowsays what is missing.
create policy chronos_gateway_all on "public"."leads" to chronos_gateway using (true) with check (true);The role: MySQL and MariaDB
role_sql makes a user with rights on the attached tables and on the gateway’s own two tables in that database: chronos_merges, one row per merge, and chronos_merge_rows, what each merge wrote, kept to undo it by. For the table orders in the database shop:
create user 'chronos_gateway'@'%' identified by '<a long random password>';grant select, insert, update, delete on `shop`.`orders` to 'chronos_gateway'@'%';create table if not exists `shop`.`chronos_merges` (id varchar(255) primary key, at datetime(6) not null default current_timestamp(6)) engine = InnoDB;grant select, insert on `shop`.`chronos_merges` to 'chronos_gateway'@'%';create table if not exists `shop`.`chronos_merge_rows` (merge_id varchar(255) not null, n int not null, table_name varchar(255) not null, op varchar(10) not null, row_key json, before_row json, after_row json, at datetime(6) not null default current_timestamp(6), primary key (merge_id, n), key chronos_merge_rows_at (at)) engine = InnoDB;grant select, insert, delete on `shop`.`chronos_merge_rows` to 'chronos_gateway'@'%';There is one grant line per attached table. The check does not list chronos_merges or chronos_merge_rows as tables to attach.
The role: MongoDB
Beside MongoDB, role_sql is a mongosh script: a role with rights on the attached collections and on the engine’s own two (chronos_merges, chronos_merge_rows), and a user with it. For the collection leads in the database shop:
use shopdb.createRole({ role: "chronos_gateway", roles: [], privileges: [ { resource: { db: "shop", collection: "leads" }, actions: ["find", "insert", "update", "remove", "changeStream"] }, { resource: { db: "shop", collection: "chronos_merges" }, actions: ["find", "insert", "createCollection"] }, { resource: { db: "shop", collection: "chronos_merge_rows" }, actions: ["find", "insert", "remove", "createCollection", "createIndex"] }, { resource: { db: "shop", collection: "" }, actions: ["listCollections"] }] })db.createUser({ user: "chronos_gateway", pwd: "<a long random password>", roles: ["chronos_gateway"] })changeStreamon each attached collection is forGATEWAY_FOLLOW. A role made from older statements needs that added (db.grantPrivilegesToRole), or the engine says what is missing and keeps refreshing on its timer.- There is one privilege line per attached collection.
- MongoDB’s built-in
readWriteon the database is also enough for the engine, but it reaches every collection there. The script above reaches only the attached ones.
Attach the tables
curl -s localhost:7400/v1/connect -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"url": "postgres://chronos_gateway:ROLE_PASSWORD@db.internal:5432/app?sslmode=verify-full", "tables": ["leads", "accounts"], "tenant_column": "org_id"}'| Field | Meaning |
|---|---|
| url | The new role’s URL. Not sent when GATEWAY_SOURCE_FILE is set (a url is then refused with 400). |
| tables | The tables (collections, beside MongoDB) agents may work on. Only these are copied. |
| tenant_column | The column that names each row’s tenant. Recorded, so a run can then give just the value ("tenant": 42). |
| fields | MongoDB only: the top-level fields kept as columns of their own. The tenant’s field is added to them for you. |
| name | Connects one more database under this name, beside the first. 1 to 32 of a-z, 0-9, _ and -. Without it, the call connects the first database, named default. |
| yes | true to attach more than 1,000,000 rows. |
The gateway saves the URL in its data folder (source.url, mode 600, readable by its user only), the engine copies the tables, and the gateway starts the engine and makes the merge policy. The answer is {ok, tables, tenant_column, output}, with source (which database it is) and the engine’s report in output.
curl -s localhost:7400/v1/connect -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"url": "mysql://chronos_gateway:ROLE_PASSWORD@db.internal:3306/shop?ssl-mode=VERIFY_IDENTITY", "tables": ["orders"], "tenant_column": "account_id"}'curl -s localhost:7400/v1/connect -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"url": "mongodb://chronos_gateway:ROLE_PASSWORD@db.internal:27017/shop?replicaSet=rs0&tls=true", "tables": ["leads"], "tenant_column": "org_id", "fields": ["status"]}'MongoDB: fields
The engine keeps a collection as a table of _id, the fields you name in fields, and doc (the rest of each document, as jsonb). Name a field in fields:
- if you mask it: a field must be a column to be masked (
PUT /v1/masks); - if agents filter by it: a run’s read narrows to a condition only on a column. A condition on a field inside
doccounts as a read of the whole collection, so any document added or changed there holds the run’s merge; - the tenant’s field is always one: it is added for you.
A field named in fields must hold one type throughout the collection: string, int, long or ObjectId. A field of mixed types is refused at the attach. Documents without it have NULL.
One connection per data folder
A second POST /v1/connect without a name answers 409 attached. To attach other tables of the first database later, start a gateway with a new data folder. To work beside another database as well, connect it under a name (below).
The tenant column
The tenant column says whose each row is: org_id, tenant_id, account_id and the like. A run is started for one tenant, and its world holds only that tenant’s rows.
- A table with the column: every read and write a run makes is filtered to its tenant, whatever SQL the agent sends. A row it writes must hold the tenant’s value before and after.
- A table without it, with a foreign key to one that has it, up to three hops away (
order_items.order_idtoorders.id): held to the tenant through that key. A run reads and changes only the rows whose key names one of its tenant’s rows, and a row it writes must name one. - A table with neither is shared, like a lookup table: every run reads it in full, and a run’s writes to it are refused (
42501), since every other tenant would see them. Give such tables the column or a key to a table that has it, change them outside runs, or don’t attach them.
With tenant_column recorded at connect, POST /v1/runs takes just the value: "tenant": 42. It also takes {"org_id": 42} or "org_id = 42"; that form takes only a plain column name and =. A tenant column that no attached table has is refused: 400 tenant_column, and the message lists the attached tables and their columns.
Each connected database has its own tenant column. Beside MongoDB the tenant’s field must be a column, which naming it as tenant_column does.
Big copies
The attach copies every row of the tables you name, holding one read open on the database while it runs. Over 1,000,000 rows in all (the database’s estimate), it asks first:
{"error": {"code": "confirm", "message": "about N rows in all ... Send yes: true to go on."}}The message gives the sizes. Send the same request with "yes": true; the console asks you. As a guide, 2.5 million rows took about 30 seconds on a test machine.
- Disk: the data folder needs room for a copy of the attached tables.
- Each refresh reads the attached tables in full. On big tables, refresh less often (
GATEWAY_REFRESH_EVERY), or follow changes instead (GATEWAY_FOLLOW, Postgres and MongoDB). - A run’s read of a table is checked against the database at its merge. A read broader than 100,000 rows of its tenant’s can’t be checked, and the merge is held for a person.
Connect at start
To keep the database URL out of the API entirely, put it in a file and let the gateway attach at its first start.
sudo install -m 640 -o root -g chronos-gateway /dev/null /etc/chronos-gateway/postgres-urlprintf '%s' 'postgres://chronos_gateway:ROLE_PASSWORD@db.internal:5432/app?sslmode=verify-full' \ | sudo tee /etc/chronos-gateway/postgres-url >/dev/nullsudoedit /etc/chronos-gateway/gateway.env # uncomment and set the three lines belowsudo systemctl restart chronos-gatewayGATEWAY_SOURCE_FILE=/etc/chronos-gateway/postgres-urlGATEWAY_TABLES=leads,accountsGATEWAY_TENANT_COLUMN=org_id| Variable | Meaning |
|---|---|
| GATEWAY_SOURCE_FILE | A file holding the database URL. Copied into the data folder at each start, so a changed password is picked up at the next restart. |
| GATEWAY_TABLES | With GATEWAY_SOURCE_FILE: the tables to attach at the first start, comma-separated. |
| GATEWAY_TENANT_COLUMN | With GATEWAY_TABLES: the tenant column. |
| GATEWAY_FIELDS | With GATEWAY_SOURCE_FILE, beside MongoDB: the fields kept as columns. |
- At its first start the gateway attaches the tables and logs
attached leads, accounts from GATEWAY_SOURCE_FILE, or the reason it couldn’t. It does not ask before a big copy. - This also works after you connected through the API: set
GATEWAY_SOURCE_FILEto rotate the password. - While it is set,
POST /v1/connecttakestablesonly and refuses aurl. - With
GATEWAY_SOURCE_FILEbut noGATEWAY_TABLES, the gateway logs a warning and waits forPOST /v1/connectwithtables. - Compose: write the URL to
postgres-url.txtnext tocompose.yaml, thenchmod 600 postgres-url.txtandsudo chown 65532 postgres-url.txt(the image’s user). Uncomment theGATEWAY_SOURCE_FILEline and bothsecretsblocks incompose.yaml, addGATEWAY_TABLESandGATEWAY_TENANT_COLUMNto.env, and rundocker compose up -d. - Helm:
postgres.existingSecret(the URL under the keypostgresUrl),postgres.tablesandpostgres.tenantColumn. The Secret is mounted as a file, never an environment value. - These start-up settings are the first database’s only.
Several databases behind one gateway
One gateway can work beside several databases, each with its own engine, tenant column, rules, labels and masks. Connect the first as ever: it is named default. Connect another under a name:
curl -s localhost:7400/v1/connect -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"name": "shop", "url": "mysql://chronos_gateway:ROLE_PASSWORD@db:3306/shop", "tables": ["orders"], "tenant_column": "account_id"}'- A name is 1 to 32 of
a-z,0-9,_and-. A name that is taken answers409 attached. - It is kept in
<data>/sources/<name>/. GET /v1/sourceslists them:{sources: [{name, kind, tables, tenant_column}]}.- A request names its database with the header
X-Chronos-Source: <name>or?source=<name>:GET /v1/status,POST /v1/setup,POST /v1/refresh,/v1/labels,/v1/masks,/v1/policy. Without one it meansdefault, so nothing changes for a gateway with one database. A name that isn’t connected is404 no_source. POST /v1/runstakes"source": "shop"(or the header). A run belongs to that one database, says so (source), and every call on it (/v1/runs/:id/..., approvals) goes to its database by itself. A run across two databases isn’t offered.GET /v1/runslists every database’s runs;?source=<name>one’s. Every event’sdatasayssource.- Keys, webhooks, approvers and single sign-on are the gateway’s, shared by all.
- The console has a picker.
curl -s localhost:7400/v1/sources -H "Authorization: Bearer $KEY"curl -s localhost:7400/v1/status -H "Authorization: Bearer $KEY" -H 'X-Chronos-Source: shop'curl -s -X POST "localhost:7400/v1/refresh?source=shop" -H "Authorization: Bearer $KEY"curl -s localhost:7400/v1/runs -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \ -d '{"source": "shop", "tenant": 42, "intent": "fix the order"}'Disconnect a database
DELETE /v1/sources/:name disconnects a database connected under a name. It takes a developer key.
curl -s -X DELETE localhost:7400/v1/sources/shop -H "Authorization: Bearer $KEY"# {"ok": true, "name": "shop", "discarded": 0}# with runs still in progress there: discard them with itcurl -s -X DELETE "localhost:7400/v1/sources/shop?discard_runs=true" -H "Authorization: Bearer $KEY"- The gateway stops that database’s engine and deletes its own copy and settings:
<data>/sources/<name>/, its rules, labels and masks. - Nothing is touched in the database itself. The gateway’s role and its own tables stay there until you drop them.
- Its runs stay in the list as history. A run that is over still reads after its database is disconnected.
- The name is free again afterwards.
| Answer | Meaning |
|---|---|
| 200 {"ok": true, "name", "discarded"} | Disconnected. discarded is how many runs were discarded with it. |
| 409 runs_live | Runs of it are still in progress (open, held, in conflict or failed). error.runs says how many. Finish or discard them, or send ?discard_runs=true to discard them with it. |
| 409 disconnecting | It is being disconnected already. |
| 400 | The first database (default) isn’t disconnected this way. A gateway for another database starts on a data folder of its own. |
| 404 no_source | No database is connected under that name. |
How main stays fresh
Main is the engine’s copy of the attached tables. Your app keeps writing the database directly, so main must take those changes. There are three ways, and they work together.
| Way | Setting | What happens |
|---|---|---|
| On a timer | GATEWAY_REFRESH_EVERY, default 5m | The engine reads the attached tables again, each whole, in one snapshot, and main takes the database’s inserts, updates and deletes. Works beside every database. Times take 60, 60s, 15m, 1h or 1d. |
| Now | POST /v1/refresh | The same refresh, at once. Answers the engine’s {at, changed, tables, skipped}. |
| As changes commit | GATEWAY_FOLLOW=on, default off | The engine also takes the database’s changes about a second after they commit, reading only the rows that changed. Postgres and MongoDB. Not MySQL or MariaDB yet: they refresh on the timer. |
Follow beside Postgres
- Through a logical replication slot. Every second the engine reads the changes since the last second from it, then the rows they name.
- Postgres needs
wal_level = logical, and the gateway’s role needsREPLICATION. The check’sfollowsays what is missing: self-hosted,ALTER SYSTEM SET wal_level = logicaland a restart; RDS,rds.logical_replication = 1andGRANT rds_replication; Cloud SQL,cloudsql.logical_decoding = on; Neon, turn on logical replication in the project’s settings. - The slot keeps Postgres’s WAL while the engine is down. On Postgres 13 or later set
max_slot_wal_keep_size, so a forgotten slot can’t fill the disk. - If you turn following off, drop the slot, with the gateway stopped:
chronos refresh <data>/db --unfollow.
Follow beside MongoDB
- Through a change stream on each attached collection. Nothing to set up or clean up: MongoDB keeps nothing for a change stream, so there is no slot to drop.
- The gateway’s role needs
changeStreamon the attached collections. The role from the check has it. Without it the engine says what is missing and keeps refreshing on its timer. - If the engine was away longer than MongoDB’s oplog keeps, the next follow takes a new place and refreshes every collection.
- One stream a collection, so many attached collections mean as many small reads a second.
With several databases, only the engines of databases that can be followed are given it (not MySQL or MariaDB yet).
In the console
The console’s Connect page does all of this in the browser, through the same API.
- Paste a URL (Postgres, MySQL, MariaDB or MongoDB) and check it. The page lists the tables, which can be attached and why not, and the tenant candidates.
- Copy the statements for the gateway’s role and have a database admin run them.
- Enter the role’s URL, pick the tables and the tenant column, and attach. Over 1,000,000 rows, it asks you first.
- With several databases, a picker chooses which one the pages show. With a database connected under a name picked, Connect has Disconnect.
On a new gateway, Get started leads here after the setup code. See Install and set up.
Errors
Errors read {"error": {"message": "...", "code": "..."}}.
| What you see | Why, and what to do |
|---|---|
401 with “send a developer key” | The Authorization: Bearer cgk_... header is missing, or the token is a run or approval token. |
/v1/connect/check: a certificate error | Only sslmode=verify-ca or verify-full checks the certificate, against the public web CAs. With a private CA or a self-signed certificate, use sslmode=require for the check, or make the role from the statements above and go straight to /v1/connect. |
/v1/connect/check: the server is too old | Postgres 11 or older, MySQL older than 8.0, MariaDB older than 10.5, MongoDB older than 5.0. The message says which. |
/v1/connect: 400, code confirm | Over 1,000,000 rows. Send the same request with "yes": true. |
/v1/connect: “the server asks for the password as MD5” (or “in the clear”) | Set password_encryption = 'scram-sha-256' and set the role’s password again (alter role chronos_gateway password '...'), or connect with sslmode=verify-full. |
/v1/connect: “no CA certificates found” | verify-full found no system CA bundle. Add sslrootcert=<file.pem> with your CA, readable by the gateway’s user (in Docker, mount it). |
/v1/connect in Docker can’t reach a database on the same machine | Inside the container, localhost is the container. Use host.docker.internal (Docker Desktop; on Linux add --add-host=host.docker.internal:host-gateway to docker run). |
/v1/connect: 409 attached | This data folder already has its first database, or the name is taken. Use a new data folder for different tables, or another name for another database. |
/v1/connect: 400 with a url | GATEWAY_SOURCE_FILE is set: the URL is that file’s. Send tables only. |
404 no_source | The name in X-Chronos-Source or ?source= isn’t connected. |
| “the engine didn’t start within 30 s” | Read engine.log in the data folder. |
POST /v1/runs: 400 tenant_column | No attached table has that column. The message lists the attached tables and their columns. |
Undo: 409 undo_not_ready | The gateway’s role was made before 0.3.1 and has no undo table. Run the statements in error.sql once. |
What each database needs and what is not there yet: Postgres, MySQL, MariaDB, MongoDB.