Database connectors

How to connect PostgreSQL, MySQL and MariaDB to Console so agents can read a database with read-only queries.

Enterprise module

Connectors are an enterprise module. The PostgreSQL, MySQL and MariaDB packages are installed from Build → Connectors, like every other connector package (see Install packages).

The three packages behave the same way. Console opens the connections to your database itself, with one database account you give it. The connector package never sees the password and never opens a connection. Every statement is read-only. Agents get tools to list schemas and tables, describe a table, run a query, explain a plan and sample rows. A table can also be synced into a Knowledge Engine as a knowledge source.

Connect a database

1. Open Build → Connectors, install the package you need, then create a connector from it (see Create and connect).
2. Fill in the settings below.
3. Connect the organisation account: choose Database user and password, enter the user name and password, and connect. Console checks the connection by asking the server who the account is and what it may do.
4. Open the connector's tools and bind them to an agent.

Create a database user that can only read before step 3 (below).

Settings

Setting	Meaning
Server	Name or IP address of the database server, for example db.example.com. No scheme, user name, port or path. This is the only server the connector can reach.
Port	Leave empty for the default: 5432 for PostgreSQL, 3306 for MySQL and MariaDB.
Database	The database to connect to.
Encryption (TLS)	How the connection is encrypted and what Console checks. See Encryption.
CA certificate (PEM)	Optional. Only when the server certificate is signed by your own authority.
Schemas to show	Optional. The catalog tools show only these schemas.
Rows per query	Default 100, 1 to 1000. The most rows one query returns to an agent.
Time limit per statement (seconds)	Default 15, 1 to 60. A statement that runs longer is stopped.

Schemas to show is a convenience, not a permission. A query can still name any schema the account can read. Limit what is readable with grants or views.

Encryption (TLS)

Choice	What Console checks	Where
Encrypted, check certificate and server name (recommended)	The certificate chains to a trusted authority and its name matches Server. The default, and the only choice that protects against talking to the wrong server.	Everywhere
Encrypted, check the certificate chain only	The chain, not the name. Use it when the certificate was issued for a different name than the one you connect with.	Everywhere
Encrypted, check nothing (on-prem only)	Nothing. For a closed test network.	On-prem only
Not encrypted (on-prem only)	Nothing. For a closed test network.	On-prem only

For CA certificate (PEM), paste the certificate of your own authority (public data, not a secret). Several certificates are allowed. Console checks the text when it connects; text that holds no valid certificate fails the connection with a clear message. The server must have TLS switched on and a certificate installed: ssl = on in postgresql.conf for PostgreSQL, a certificate and key in the server's TLS settings for MySQL and MariaDB.

Sign-in

One method: Database user and password. You enter a User name and a Password. Console keeps the password and uses it only to open connections. It is never sent in an HTTP request, and neither the connector code nor the agents see it. Leave the password empty only for an account that has none, such as a public database.

When the account connects, Console shows who it is (<user>@<host>/<database>) and the server version. If the account can write, the connection name ends with "(write access: use a read-only account)". The connection still works; the label is a warning.

Tools

All tools are read-only (read access), so they run without asking for approval. See Tools and approvals.

Tool	What it does
List schemas (list_schemas)	Lists the schemas the connector shows.
List tables (list_tables)	Lists tables and views of a schema with a row estimate, at most 200. An admin can pin the schema.
Describe table (describe_table)	Columns with type, nullability, default and comment, primary key, indexes, foreign keys and a row estimate.
Run query (run_query)	Runs one read statement. Placeholders are $1, $2, ... on PostgreSQL and ? on MySQL and MariaDB. Values go in params.
Explain query (explain_query)	Shows the plan without running the statement. It never uses ANALYZE.
Sample rows (sample_rows)	The first rows of a table, up to 50, without writing SQL.

What a query may contain

Console accepts one read statement and refuses anything else before it takes a connection.

Rule	Detail
One statement	A ; is allowed only as the last character.
First keyword	SELECT, WITH, VALUES, TABLE, SHOW or EXPLAIN. MySQL and MariaDB also accept DESCRIBE and DESC.
No writing	INSERT, UPDATE, DELETE, DDL, COPY or LOAD, locking and session statements are refused anywhere in the text, including inside a WITH clause. INTO and FOR UPDATE / FOR SHARE are refused too.
No ANALYZE	EXPLAIN ANALYZE runs the statement and is refused.
No side-effect functions	Functions that read files, wait, take locks, send signals or run a statement from text are refused.
MySQL and MariaDB extras	Comments the server executes (/*! ... */), optimizer hints (/*+ ... */) and variable assignment with := are refused.
Size	At most 20,000 characters, at most 100 parameters, at most 64 KiB of parameter data.

A refused statement returns a readable reason so the agent can correct it. Beyond the check, Console starts every statement in a read-only session or transaction where the engine supports it.

Limits

Limit	Value
Rows per query	Rows per query setting: default 100, at most 1000.
Result size	About 256 KiB per result for an agent. The answer says truncated when rows were left out.
Cell size	Text longer than 8 KiB in a cell is cut, and the answer lists the cut columns.
Binary values	A base64 preview of the first 256 bytes, with the full size.
Time per statement	Time limit per statement setting: default 15 seconds, at most 60.
Connecting	A connection attempt gives up after 15 seconds.
Failed connection attempts	6 a minute for each connector, so a wrong address does not hammer the server.
Tables listed	200 by default, at most 1,000 per request.
Statements at the same time	4 per connector, 2 per agent call. Beyond that a call waits up to 5 seconds, then fails with a "try again shortly" message.

Rows come back as arrays in column order with a type for each column. Integers above 2^53 and DECIMAL / NUMERIC values arrive as strings so no digit is lost. Dates and times are text, and a timestamp without a time zone has no Z. JSON is parsed.

Connection pooling

Console keeps a few connections open per connector (3 by default, at most 10) and reuses them. An idle connection closes after one minute, and any connection after ten minutes, so a changed password or address is picked up soon. If every connection is busy, a call waits up to 5 seconds and then fails with a "try again shortly" message.

Several Console instances each keep their own connections. Allow for that in the server's connection limit; the account recipes below set one for the account.

Network reachability

Console	What it can reach
Cognipeer's cloud (SaaS)	Public addresses only. A database on a private network is not reachable. Allow the Console egress address in the database firewall (your Cognipeer contact gives you the address), or put the database behind a public address that only that address can use.
On-prem	Private addresses too. The operator can narrow the reachable private ranges with CONNECTOR_EGRESS_PRIVATE_CIDRS.

Port rules on SaaS: the default port, or any port from 1024 up that is not on a short list of well-known non-database ports (for example 6379, 11211, 27017, 9200, 2375). An on-prem Console accepts any port.

When the server cannot be reached for any reason (nothing listens, the name does not resolve, a firewall drops the packets, a reset, a timeout), Console reports one message: "The database server could not be reached." It gives no detail about your network. If this appears on an on-prem Console while the database is up, ask the operator whether the database's address range is allowed. On an on-prem Console, a server that offers no TLS while an encrypted mode is chosen is named instead: "The database server does not accept encrypted (TLS) connections."

Give Console a read-only database user

Console reads your database with one account, and whatever that account may do is what the connector can do. Console makes every statement read-only as far as the engine allows, but the account is the real boundary. Create an account that can only read. Do not use postgres, root or any account that owns data. Console does not hide or mask columns: anything the account can read, an agent can read. To share only part of a table, expose it as a view and grant the account that view only.

PostgreSQL

Connect to the database the agents should read, as a role that may create roles, and run:

CREATE ROLE cognipeer_reader WITH LOGIN PASSWORD 'replace-with-a-long-random-password' CONNECTION LIMIT 10;
ALTER ROLE cognipeer_reader SET default_transaction_read_only = on;
GRANT CONNECT ON DATABASE mydb TO cognipeer_reader;
GRANT USAGE ON SCHEMA public TO cognipeer_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cognipeer_reader;
-- Tables and views that the role app_owner creates later (once per schema and creating role):
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO cognipeer_reader;

• Repeat the USAGE and SELECT lines for every other schema the agents should read.
• On PostgreSQL 14 and newer you can run GRANT pg_read_all_data TO cognipeer_reader; instead of the per-schema grants. It does not bypass row-level security.
• Before PostgreSQL 15, every user may create objects in the public schema by default. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC; so the account cannot.

MySQL and MariaDB

As an administrator, replace 203.0.113.10 with the address Console connects from (or use '%' to allow any address), and run:

CREATE USER 'cognipeer_reader'@'203.0.113.10' IDENTIFIED BY 'replace-with-a-long-random-password' REQUIRE SSL WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT ON mydb.* TO 'cognipeer_reader'@'203.0.113.10';
-- Optional: let agents read the definition of views.
GRANT SHOW VIEW ON mydb.* TO 'cognipeer_reader'@'203.0.113.10';

• REQUIRE SSL makes the server refuse this account over an unencrypted connection. MAX_USER_CONNECTIONS 10 limits how many connections it can hold.
• A grant ON mydb.* also covers tables and views created later.
• Repeat the GRANT SELECT line for every other database the agents should read.

To check what the account holds, run \du cognipeer_reader and \dp public.* in psql, or SHOW GRANTS FOR 'cognipeer_reader'@'203.0.113.10'; in MySQL and MariaDB. The in-product Setup guide on the connector shows the same recipe.

Sync a table into a knowledge base

Each package offers a table source. Choose the schema and table, an Id column (the primary key, or a unique column that is never empty), the Text columns (1 to 20; each becomes a ## column block), and optionally metadata columns (up to 20), a title column, a Changed-at column and an address template (https only, {column} placeholders, for example https://app.example.com/orders/{id}).

• Rows are read in pages of 500 by key, one short read-only statement each.
• With a Changed-at column, scheduled runs read only rows changed since the last run. Without it every run reads the whole table, and unchanged rows are skipped by their content.
• Rows that disappear are removed from the knowledge base only after a complete full run. Start a full run from time to time when you use a changed-at column.
• Text in a column is cut at 64 KiB, and the source says which columns.
• The Knowledge Engine has no per-row access control for a table: everyone who can query the knowledge base can read what you sync. Point the source at a view that holds only what may be indexed.

See Knowledge sources for schedules and runs.