Skip to content

Database tools

ClickHouse external data

Tested with:ClickHouse 26.8 · Databasezy API v1 · ZB-588, ZB-714 (written; live check pending)

ClickHouse can read data that lives outside the instance: s3() and url() read files over HTTPS, remote()/remoteSecure(), mysql() and postgresql() query other servers, and the Kafka engine consumes topics. On Databasezy all of these are closed by default. You open exactly the hosts (and, where needed, the ports) your queries use, and the platform makes sure nothing you open can reach the cell itself.

Every ClickHouse instance starts with an empty external-hosts list. ClickHouse checks that list (remote_url_allow_hosts) before it connects anywhere for:

  • the table functions url(), s3(), s3Cluster(), hdfs(), remote() and remoteSecure() with a host list, mysql(), postgresql() and mongodb();
  • the table engines URL, S3, HDFS, MySQL, PostgreSQL and MongoDB;
  • dictionaries you create with SQL whose source is HTTP, CLICKHOUSE (another server; a source on the instance itself needs no entry, but it must name USER and PASSWORD, see the security model), MYSQL, POSTGRESQL or MONGODB;
  • every one of these when it reads its settings from a named collection.

With an empty list, such a query fails with URL "…" is not allowed in configuration file, see <remote_url_allow_hosts> (code 491, UNACCEPTABLE_URL). cluster('main', …), clusterAllReplicas('main', …) and ON CLUSTER main use the instance’s own cluster and are not affected.

Outbound network access is a second, separate gate. Outside secure placement the instance may connect to public addresses on port 443 (HTTPS) without opening anything. Other ports, such as 9092 for Kafka, 9440 for ClickHouse’s secure native protocol, 3306, 5432 or 80, are opened per instance. The Kafka, RabbitMQ and NATS engines do not consult the host list, so for them the port is the only switch. On secure (HIPAA) placement nothing public is open, 443 included, until you enable it: see On secure placement.

The external hosts and the outbound ports are one setting of the instance, …/instances/{id}/egress. In the portal, open the instance’s Network page: under External hosts, add or remove rows, then Save hosts and confirm. Each refused entry is explained next to its row, and a host that names a port that is not open yet (such as ch.example.com:9440) gets a button to open it. With the API or zb api:

shell
# What is open now
zb api GET /v1/orgs/{org}/instances/inst_01jb.../egress
# Replace the hosts (and the ports: send the ports you want to keep)
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{
"ports": [],
"hosts": ["datasets-documentation.s3.eu-west-3.amazonaws.com", "*.s3.us-east-1.amazonaws.com"]
}'
  • hosts replaces the whole list; [] closes everything again. Leave hosts out to change only the ports.
  • ports is required. Send the current ports (from the GET) to keep them.
  • Changing the setting needs the instances:operate permission and a recent second factor. Every change is recorded in the audit log and as the security event authz.instance_egress_changed.
  • The hosts reach the server without a restart, usually within two minutes. Port changes reach the instance’s network policy within a minute.
EntryMatches
data.example.comexactly that host, any port
warehouse.example.com:9440that host on port 9440 only
*.s3.us-east-1.amazonaws.comevery name below it (my-bucket.s3.us-east-1.amazonaws.com), not the name itself
52.216.8.1that public IPv4 address, written exactly like this

ClickHouse compares the host of the URL as you write it in the query, before any DNS lookup. Write host names in lower case in your queries. A host written differently (upper case, a trailing dot, an IP address for an allowed name) is refused. You can list at most 20 entries.

The API answers 422 with the entry (hosts[1]) and the reason, and nothing changes, for:

  • private, loopback, link-local (including the cloud metadata address 169.254.169.254), shared (CGNAT), multicast and reserved addresses, and the cell’s own node addresses;
  • IP addresses in octal, hex or integer form (0xa9fea9fe, 2852039166, 127.1) and IPv6 addresses (use a DNS name);
  • internal and cluster names: localhost, single-label names, *.svc, *.cluster.local, *.local, *.internal, *.lan, *.home.arpa and similar, and any name whose top-level domain is not a public one (inside the cluster, kubernetes.default would resolve to the Kubernetes API);
  • Databasezy’s own domains (*.databasezy.com), which point at the platform’s servers;
  • DNS services that answer with any address written into the name (nip.io, sslip.io, localtest.me, …) and names that embed a non-public address (10-0-0-1.example.com);
  • wildcards over a public suffix (*.com, *.co.uk) and * alone;
  • a name that resolves to a non-public address when you save it.

A refusal that names an internal destination is recorded as the security event authz.instance_egress_refused.

On secure (HIPAA) placement the instance has no public egress by default: HTTPS reaches only the cell’s object store, for backups, so url() and s3() cannot reach a public host, not even on port 443. The external-hosts list is then also the network allow-list, and 443 is a port you open like any other:

shell
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{
"ports": [443],
"hosts": ["datasets-documentation.s3.eu-west-3.amazonaws.com"]
}'
  • Ports without hosts are refused with a 422: an opened port reaches only the listed hosts.
  • Opening a port or adding a host while ports are open takes the strict data-egress step-up: a second factor completed within the last 15 minutes, or an API key with the explicit data:egress scope (* does not count). It is recorded as the security event data.egress_enabled, besides authz.instance_egress_changed.
  • An IP address in the list is enforced by the network policy exactly. A host name cannot be matched by a network policy, so a listed name opens its ports to public addresses; ClickHouse’s own host check keeps your queries to the listed names.

Outside secure placement port 443 is always open, so a public bucket only needs its host (on secure placement, also send "ports": [443], see On secure placement). With the virtual-hosted style URL the host is <bucket>.s3.<region>.amazonaws.com:

shell
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{
"ports": [],
"hosts": ["datasets-documentation.s3.eu-west-3.amazonaws.com"]
}'
SQL
SELECT count()
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_0.gz', NOSIGN, 'TabSeparatedWithNames');
INSERT INTO trips
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', NOSIGN, 'TabSeparatedWithNames');

NOSIGN sends the request unsigned, which is what a public bucket expects. The server never borrows credentials from the machine it runs on: without keys or a named collection, a private bucket answers 403. Path-style URLs (https://s3.<region>.amazonaws.com/<bucket>/…) need s3.<region>.amazonaws.com on the list instead; other S3-compatible stores (Cloudflare R2, Backblaze B2, MinIO on a public host) work the same way with their host.

Named collections: keep credentials out of queries

Section titled “Named collections: keep credentials out of queries”

A named collection stores connection settings, credentials included, under a name. Queries then use the name, so keys do not appear in query text, system.query_log or your application code. The app user can create them:

SQL
CREATE NAMED COLLECTION raw_events AS
url = 'https://acme-raw.s3.us-east-1.amazonaws.com/events/',
access_key_id = 'AKIA...',
secret_access_key = '...';
SELECT count() FROM s3(raw_events, filename = '2026/10/*.parquet', format = 'Parquet');
-- Let a reporting user query through it without seeing the secret
GRANT NAMED COLLECTION ON raw_events TO reporting;
  • The host in a named collection is checked against the external-hosts list like any other, so acme-raw.s3.us-east-1.amazonaws.com (or *.s3.us-east-1.amazonaws.com) must be on it.
  • SHOW CREATE NAMED COLLECTION hides secret values. ALTER NAMED COLLECTION raw_events SET secret_access_key = '…' rotates a key; DROP NAMED COLLECTION raw_events removes it.
  • Named collections are stored on the instance’s data volume. Treat them like users and roles created with SQL, which backups do not include: keep the CREATE statements (without the secrets) in version control.
FunctionHost listPort to open
remoteSecure('ch.example.com:9440', db, table, 'user', 'password')ch.example.com or ch.example.com:94409440
mysql('db.example.com:3306', 'shop', 'orders', 'user', 'password')db.example.com3306
postgresql('pg.example.com:5432', 'app', 'users', 'user', 'password')pg.example.com5432
url('https://api.example.com/export.csv', CSVWithNames)api.example.comnone (443)
shell
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{
"ports": [9440],
"hosts": ["ch.example.com:9440"]
}'

Put the user and password in a named collection (CREATE NAMED COLLECTION src AS host = 'ch.example.com', port = 9440, user = '…', password = '…', secure = 1) rather than in the query. To copy a Databasezy instance into another one, use migrations instead: Databasezy’s own hosts are never on the list.

The Kafka engine reads a topic continuously; a materialized view moves each batch into a regular table. It does not consult the host list. It needs the broker port open, and brokers must be on public addresses:

shell
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{"ports": [9092]}'
SQL
CREATE NAMED COLLECTION kafka_prod AS
kafka_broker_list = 'pkc-abc12.us-east-1.aws.confluent.cloud:9092',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse-events',
kafka_format = 'JSONEachRow',
kafka_security_protocol = 'sasl_ssl',
kafka_sasl_mechanism = 'PLAIN',
kafka_sasl_username = '<api key>',
kafka_sasl_password = '<api secret>';
CREATE TABLE events_queue (ts DateTime64(3), user_id UInt64, event LowCardinality(String))
ENGINE = Kafka(kafka_prod);
CREATE TABLE events (ts DateTime64(3), user_id UInt64, event LowCardinality(String))
ENGINE = MergeTree ORDER BY (event, ts);
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT ts, user_id, event FROM events_queue;
  • Use TLS (sasl_ssl or ssl) to a managed Kafka (Confluent Cloud, Aiven, Redpanda Cloud, Upstash). Open the port your provider uses (9092, 9093 or 9094).
  • Brokers advertise their own addresses to the client. Brokers that advertise private addresses (a self-hosted cluster behind NAT) are unreachable from the instance; the network policy refuses them.
  • SELECT * FROM system.kafka_consumers shows each consumer’s assignments, offsets and last error.
  • To pause consumption, DETACH TABLE events_queue; ATTACH TABLE events_queue resumes from the committed offsets.
  • With high availability, create the queue table, a ReplicatedMergeTree target table and the view ON CLUSTER main: the replicas join the same consumer group and share the topic’s partitions.
LayerWhat it does
External-hosts listClickHouse refuses any URL or server not on the instance’s list; empty by default. You cannot change it from SQL.
Validation at the APIInternal, cluster, metadata, node and platform destinations are refused (422 and a security event); names are resolved once and refused if they point at a non-public address.
Validation in the cellThe cell checks every entry again before writing the server configuration and drops any that fails.
Network policyPort 443 (outside secure placement) and the ports you open reach public addresses only; on secure placement only the ports you open, towards the listed hosts. Private, link-local (metadata), CGNAT, loopback, multicast and reserved ranges, their IPv6 counterparts and the cell’s own addresses are refused on every port, whatever a name resolves to later.
No machine credentialsThe server never asks the cloud metadata service for credentials, so s3() cannot sign with the node’s role.
No password-less usersClickHouse’s built-in default user has a random password that nobody is given, so nothing can sign in as it, over the loopback included. Queries over the instance’s cluster (cluster(), Distributed tables) run on every replica as the user who sent them.
AuditEvery change is in the audit log and is the security event authz.instance_egress_changed; refused internal destinations are authz.instance_egress_refused.

A name you list can be pointed at another address later. The network policy still blocks every non-public address, but it cannot filter the server’s own loopback interface: a name that later resolves to 127.0.0.1 reaches only the instance itself, and there it can sign in only with credentials you supply.

A dictionary whose CLICKHOUSE source is a table on the instance itself signs in like any other connection. Without USER it would use default, which cannot sign in, and loading fails with default: Authentication failed (code 516). Name your user, or a named collection that holds it:

SQL
CREATE DICTIONARY rates (currency String, rate Float64)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(TABLE 'rates_src' DB 'default' USER 'app' PASSWORD '<password>'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 300 MAX 600);
  • At most 20 external hosts and 10 opened ports per instance. Ports 25, 465 and 587 (email) stay closed; DNS (53) goes through the cluster’s resolver. Port 443 is always open outside secure placement; on secure placement it is one of the ports you open, with the hosts.
  • No IPv6 literals in the list; outbound HTTPS uses IPv4.
  • Changing the list does not restart the server. Queries that are already running keep their connections.
  • The list applies to ClickHouse instances only. PostgreSQL and TimescaleDB use the same endpoint for outbound ports only.