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.
What is closed by default
Section titled “What is closed by default”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()andremoteSecure()with a host list,mysql(),postgresql()andmongodb(); - the table engines
URL,S3,HDFS,MySQL,PostgreSQLandMongoDB; - dictionaries you create with SQL whose source is
HTTP,CLICKHOUSE(another server; a source on the instance itself needs no entry, but it must nameUSERandPASSWORD, see the security model),MYSQL,POSTGRESQLorMONGODB; - 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.
Allow a host
Section titled “Allow a host”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:
# What is open nowzb 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"]}'hostsreplaces the whole list;[]closes everything again. Leavehostsout to change only the ports.portsis required. Send the current ports (from theGET) to keep them.- Changing the setting needs the
instances:operatepermission and a recent second factor. Every change is recorded in the audit log and as the security eventauthz.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.
What an entry may be
Section titled “What an entry may be”| Entry | Matches |
|---|---|
data.example.com | exactly that host, any port |
warehouse.example.com:9440 | that host on port 9440 only |
*.s3.us-east-1.amazonaws.com | every name below it (my-bucket.s3.us-east-1.amazonaws.com), not the name itself |
52.216.8.1 | that 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.
What is always refused
Section titled “What is always refused”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.arpaand similar, and any name whose top-level domain is not a public one (inside the cluster,kubernetes.defaultwould 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 placement
Section titled “On secure placement”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:
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:egressscope (*does not count). It is recorded as the security eventdata.egress_enabled, besidesauthz.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.
Read a public S3 bucket
Section titled “Read a public S3 bucket”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:
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{ "ports": [], "hosts": ["datasets-documentation.s3.eu-west-3.amazonaws.com"]}'SELECT count()FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_0.gz', NOSIGN, 'TabSeparatedWithNames');
INSERT INTO tripsSELECT * 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:
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 secretGRANT 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 COLLECTIONhides secret values.ALTER NAMED COLLECTION raw_events SET secret_access_key = '…'rotates a key;DROP NAMED COLLECTION raw_eventsremoves 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
CREATEstatements (without the secrets) in version control.
Query another server
Section titled “Query another server”| Function | Host list | Port to open |
|---|---|---|
remoteSecure('ch.example.com:9440', db, table, 'user', 'password') | ch.example.com or ch.example.com:9440 | 9440 |
mysql('db.example.com:3306', 'shop', 'orders', 'user', 'password') | db.example.com | 3306 |
postgresql('pg.example.com:5432', 'app', 'users', 'user', 'password') | pg.example.com | 5432 |
url('https://api.example.com/export.csv', CSVWithNames) | api.example.com | none (443) |
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.
Consume a Kafka topic
Section titled “Consume a Kafka topic”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:
zb api PUT /v1/orgs/{org}/instances/inst_01jb.../egress -d '{"ports": [9092]}'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 ASSELECT ts, user_id, event FROM events_queue;- Use TLS (
sasl_sslorssl) 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_consumersshows each consumer’s assignments, offsets and last error.- To pause consumption,
DETACH TABLE events_queue;ATTACH TABLE events_queueresumes from the committed offsets. - With high availability, create the queue table, a
ReplicatedMergeTreetarget table and the viewON CLUSTER main: the replicas join the same consumer group and share the topic’s partitions.
Security model
Section titled “Security model”| Layer | What it does |
|---|---|
| External-hosts list | ClickHouse refuses any URL or server not on the instance’s list; empty by default. You cannot change it from SQL. |
| Validation at the API | Internal, 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 cell | The cell checks every entry again before writing the server configuration and drops any that fails. |
| Network policy | Port 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 credentials | The server never asks the cloud metadata service for credentials, so s3() cannot sign with the node’s role. |
| No password-less users | ClickHouse’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. |
| Audit | Every 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:
CREATE DICTIONARY rates (currency String, rate Float64)PRIMARY KEY currencySOURCE(CLICKHOUSE(TABLE 'rates_src' DB 'default' USER 'app' PASSWORD '<password>'))LAYOUT(COMPLEX_KEY_HASHED())LIFETIME(MIN 300 MAX 600);Limits
Section titled “Limits”- 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.