Agent Connections

An agent connection reaches a database that Kvery cannot connect to directly, for example a database on a private network or behind a firewall that accepts no connections from outside. You run the Kvery Agent, a small HTTP service, on a machine that can reach the database. Kvery sends every SQL statement of a query to the agent in a signed request; the agent runs it on the database and answers with the result.

If you have an SSH server that can reach the database, an SSH tunnel is the other way to reach it.

How it works

• Kvery calls the agent at the Agent URL of the connection followed by /execute.
• Every request is signed with a secret key that Kvery and the agent share, and names the connection of the agent's configuration the statement is for.
• A request expires after five minutes and carries a one-time ID, so the agent runs it once only. The agent keeps these IDs in memory only: after a restart of the agent, or with several agent processes that share the secret key, a request that was read on the network could run once more within its five minutes. An https:// agent URL keeps the requests from being read (see Encryption and clock).
• Every statement runs in a transaction of its own on the agent: it is committed when it succeeds and rolled back when it fails.
• The agent's configuration holds the address and the credentials of the database: the host, port, username, password, SSL and SSH settings of a direct connection are not used by an agent connection.

Setting up the agent

The agent is open source: github.com/KoltSystems/kvery-agent. The Download Agent link of the connection form leads to the same repository, and its README describes every option. In short:

1. Install Python 3.8 or newer with its venv module. On Debian and Ubuntu: sudo apt install git python3-venv.

2. Get the agent, create a virtual environment in its folder, and install the packages of the agent and the driver of your database into it:

   git clone https://github.com/KoltSystems/kvery-agent.git
   cd kvery-agent
   python3 -m venv .venv
   .venv/bin/pip install -r requirements.txt
   .venv/bin/pip install PyMySQL            # MySQL / MariaDB
   .venv/bin/pip install psycopg2-binary    # PostgreSQL
   .venv/bin/pip install pyodbc             # SQL Server (also needs the Microsoft ODBC driver)

   Debian 12, Ubuntu 23.04 and newer refuse pip install into the Python of the system (PEP 668); the virtual environment .venv holds the packages of the agent.

3. Copy config.json to a folder outside the repository, for example /etc/kvery-agent/config.json, make it readable only by the account that runs the agent (chmod 600), and fill it in (see below). Outside the repository folder, git pull updates the agent without stopping on your settings.

4. Start the agent from its folder with the Python of the virtual environment and the path of the configuration file as its argument: .venv/bin/python kvery-agent.py /etc/kvery-agent/config.json. The README shows how to run it as a systemd service with the same command and how to update it. Restart the agent after every change of the configuration file.

The configuration file

The agent does not start while a required key is missing or a value cannot be used; it prints the reason. The keys you need for Kvery:

Key	Meaning
connections	The databases the agent may use, by name. Each entry has a type (mysql, pgsql, or dblib for SQL Server), host, port, username, password and database. Kvery refers to an entry by its name.
port	TCP port the agent listens on.
ip_whitelist	IP addresses the agent serves; every other address gets HTTP 403. List the address of the Kvery server here: 164.92.167.149. The connection form shows it as Server IP.
secret_key	Shared secret that signs the requests: at least 16 characters, 32 or more random characters are recommended. Enter the same value at the connection in Kvery.
algorithms	Signing algorithms a request may use: one or more of HS256, HS384 and HS512 (default ["HS256"]). Kvery signs every request with HS256, so the list has to include HS256.
require_expiry	true refuses a request without an expiry time or a one-time ID. Kvery sends both, so true is recommended (default false).

The logging keys log_enabled, log_filename and logrotate_days are required as well. An example with one MySQL connection:

{
    "connections": {
        "reports": {
            "type": "mysql",
            "host": "127.0.0.1",
            "port": 3306,
            "username": "report_user",
            "password": "...",
            "database": "reports"
        }
    },
    "port": 1337,
    "ip_whitelist": ["164.92.167.149"],
    "log_enabled": true,
    "logrotate_days": 7,
    "log_filename": "agent.log",
    "secret_key": "<32 or more random characters>",
    "date_format": "iso",
    "require_expiry": true
}

A random secret can be generated with python3 -c "import secrets; print(secrets.token_urlsafe(48))".

The agent does not start with a secret_key shorter than 16 characters. When you update an agent whose key is shorter, set a key of at least 16 characters both in the agent's configuration file and at the connection in Kvery (Agent Secret Key) before you restart the agent.

Encryption and clock

• The agent itself speaks plain HTTP, which protects neither the statements nor the results on the network. Put a reverse proxy with TLS in front of it and enter its https:// address in Kvery. Kvery verifies the certificate of an https:// agent URL: it has to be issued by a public certificate authority for the host name; otherwise the connection check and the runs fail with "The TLS certificate of the agent could not be verified!". The README has an example proxy configuration; give the proxy a read timeout of at least 100 seconds (see Limits).
• Keep the clock of the agent's machine synchronised: a request is refused as expired when that clock is ahead of Kvery's by more than five minutes (expiry_leeway in the agent's configuration allows for a clock that cannot be kept in step).

Adding the connection in Kvery

In the query form, choose Add new connection in the Database connection field and fill in the common fields (Production, Name, Database type, Database name; see Database connections). Then switch the connection type from Database to Agent and fill in:

• Agent URL — the base address of the agent, for example https://agent.example.com, without /execute and without a / at the end: Kvery appends /execute, so https://agent.example.com/ would be called as https://agent.example.com//execute, which the agent answers with a redirect that Kvery reports as an answer that is not a valid agent response. An address without http:// or https:// is called over plain http. The address may not contain user credentials (user@host), and on app.kvery.io it may not point to an internal address of the server's network; such a URL is refused with "This agent URL is not allowed".
• Agent Database Connection Name — the name of the entry under connections in the agent's config.json.
• Agent Secret Key — the secret_key of the agent.
• Agent Signing Algorithms — the signing algorithm: HS256, the only one Kvery signs with; it has to be in the agent's algorithms list.

Set Database type to the type of the database behind the agent: Kvery escapes form values for that database, and reads the tables and columns for autocomplete with it and the Database name.

Checking the connection

Check connection runs Select 1 through the agent and reports what happened:

• a list of the empty fields ("Fill in the following field(s) to test the connection: …");
• a host name of the Agent URL that cannot be resolved ("The host name could not be resolved. Check the host name.");
• no answer within the time limit, or a refused connection ("Connection failed: the agent at … could not be reached");
• an answer that is not an agent response, for example from another web server at the URL ("… answered with HTTP …, but not with a valid agent response");
• the agent's own error message, for example Unauthorized IP address (the Kvery server is missing from ip_whitelist) or Token expired (the clocks differ);
• the certificate error described above.

When the agent works but its URL is a plain http:// address, the check succeeds with a warning: "The connection works, but the agent is reached over plain http".

On the Databases page, the Edit button of an agent connection opens the same fields. The Agent Secret Key is never shown: enter it again every time you save the connection, because saving checks the connection first.

Limits

• Read-only runs are refused on agent connections: a read-only run is enforced inside a database transaction that the agent does not offer. This concerns the MCP server: kvery_execute_query and read-only tokens cannot run an agent-connection query; use a full-access token with kvery_execute_write_query.
• Long statements: Kvery waits about 100 seconds for the answer of the agent; after that the run fails with "The agent is unreachable!". The agent does not stop the statement: it keeps running and is committed when it succeeds, but its result does not reach Kvery. A longer read timeout of a proxy in front of the agent does not change this, so keep the statements that run through an agent well below 100 seconds.
• Errors during a run that come from the agent connection itself: "The agent is unreachable!" (also when the host name of the Agent URL cannot be resolved at the time of the run), "The agent did not send a valid response!", and "The statement is too long for the agent!". A long statement is sent in the body of the request, which older agents do not accept: update the agent, or shorten the statement. Members who can edit the query see these messages and the agent's own error text; everyone else gets the general error message (or the query's own ON ERROR RETURN message).