Validate statements, TLS and credentials

Building a connector, step 7 (SQL track, 3 of 3). Previous: Statements and Grant kinds.

Before you run a single statement against the application's data, ask the database whether it accepts them. Every SQL connector has a handler for that, connection.validateStatements, which checks every statement without executing any. This page also covers how the gateway connects: TLS, and the username and password.

Validate statements

connection.validateStatements asks the database to plan or compile each statement the definition runs. It binds every placeholder to null, because it checks the statement, not values. Each dialect checks without executing:

Dialect	How the database checks a statement
Postgres	`EXPLAIN (ANALYZE false, COSTS false)` on the statement, inside a read-only transaction that is always rolled back.
MySQL	`PREPARE` and then `DEALLOCATE PREPARE`: the statement is parsed and its names resolved, and nothing is evaluated.
SQL Server	`sp_describe_first_result_set` with the statement passed as a parameter: it is compiled and analyzed, and not executed.
Oracle	`EXPLAIN PLAN FOR` the statement, with the plan it writes rolled back.

The result names each statement by where the definition defines it:

Key	Statement
`validationQuery`	The connection test query
`entities.<key>.sync`	The entity's source: its `selectStatement`, or the `SELECT` built from its `table` and `where`
`entities.<key>.statements.<operation>`	A write statement: `create`, `update`, `delete`, `enable` or `disable`
`entities.<key>.createdId`	The entity's `createdId` query
`grants.<kind>.select`, `grants.<kind>.add`, `grants.<kind>.remove`	A Grant kind's statements

{
  "statements": {
    "validationQuery": { "ok": true },
    "entities.user.sync": { "ok": true },
    "entities.user.statements.create": { "ok": true },
    "grants.has_role.remove": { "ok": false, "message": "SQL postgres error 42703: column \"role\" does not exist" }
  }
}

The input { "keys": ["grants.has_role.add", "grants.has_role.remove"] } limits the check to those statements. With no input, every statement is checked.

Validation proves that each statement in the table above parses and names real tables and columns. It does not prove the statement does the right thing: an UPDATE with the wrong WHERE validates. That is what testing is for.

It also checks only those statements, never the queries the engine builds around them: the paged, incremental and single-record reads of each entity, and each Grant kind's reads by target. Column names that appear only in those queries are checked for their spelling as identifiers, not against the database: orderKey, updatedAt, sourceColumn, targetColumn and the entity's identifier. So a misspelled orderKey, or an ORDER BY inside a selectStatement on SQL Server, validates cleanly and then fails on the first Sync. Run fetchAll, fetchOne and each Grant kind's relationship read once in a draft test before you publish.

In the Connector Builder

On the Statements tab, the Validate section has a button for the selected entity, such as Validate user's statements, and Validate every statement. The Grant kinds tab has Validate every statement too. Validation runs on your tenant's gateway with the Test panel's run config, and each statement's result appears below it.

In a draft test

A draft test of a SQL connector, validation included, runs your unpublished statements on your tenant's gateway with the credentials you type. Owlie treats that like publishing, so it needs the custom_connector.publish permission (the owner and admin roles have it by default), not only the permission to edit connectors. Point draft tests at a test database.

Validation runs without your consent to writes. Every other handler of a SQL draft, the connection test and every read included, counts as a write: the gateway cannot prove that authored statement text only reads, so the builder asks Run followed by the handler key and against the real system? before it runs one, and runs it when you select Run it. Through the API, a draft test of any handler but connection.validateStatements and connection.validateConfig needs "allow_writes": true.

Through the API, send every install field the test needs; Owlie fills in no defaults. Include dialect, or the gateway connects with its Postgres driver whatever your definition says, and type the password itself. A gateway secret name in its place is refused. gateway_id chooses the gateway; without it the test runs on your tenant's one active gateway.

{
  "procedure": "connectors.testArtifact",
  "input": {
    "connector_id": "<connector id>",
    "version_id": "<draft version id>",
    "artifact_key": "connection.validateStatements",
    "test_input": {},
    "gateway_id": "<gateway id>",
    "integration_config": {
      "config": {
        "dialect": "postgres",
        "host": "db-test.acmeledger.example",
        "port": 5432,
        "database": "ledger_test"
      },
      "auth": { "key": "password", "config": { "user": "owlie_test", "password": "<test password>" } }
    }
  }
}

The same body with "artifact_key": "user.fetchAll" and "allow_writes": true reads the first page of users, as many rows as the definition's pageSize. Install-editable values you leave out of config take their seeds. Build with the API and agents describes the endpoint and the response.

TLS

The install's ssl field decides how the gateway encrypts the connection:

`ssl`	Connection	Needs the operator's `allowTlsOverrides`
Absent, or `true`	TLS, with the server's certificate verified	No
`{ "caCert": "<PEM>" }`	TLS, verified against the certificate authority you give	Yes
`{ "rejectUnauthorized": false }`	TLS, without verifying the server's certificate	Yes
`false`	No TLS, for a database on your network that does not offer it	Yes

Without allowTlsOverrides for the connector, the gateway refuses a connection that asks for any of the last three, and says which setting the operator needs. It never falls back silently. The ssl object accepts only caCert and rejectUnauthorized. The operator sets allowTlsOverrides in the gateway's configuration (Private-network gateways).

The username and password

A SQL connector has one way to sign in, Username and password, with the fields user and password.

Installs do not give the password to Owlie. For an install on a gateway, the password field takes the name of a gateway secret: the install form asks for the gateway environment variable name, and the gateway reads the value from OWLIE_SECRET_<NAME> or its secrets file. The operator must also grant that name to the connector.
Draft tests take typed credentials in the Test panel's run config. The gateway resolves none of its own secrets for a draft, and scrubs the typed values from its logs and error messages.

Give the database user only what the connector's statements need: read on the tables and views Sync reads, and write on the tables the write statements change.

Next: Test your connector