Support for authenticated Clickhouse connections via Basic Auth / -X headers

[ctoprimer]

What an exciting project! We're looking to migrate from Clickhouse to DuckDB and it seems like a great tool to help. However, it doesn't seem like ch_scan allows anything more than a "user" named parameter to be specified. Given Clickhouse's documentation on the topic it would be necessary to supply a "password" as a named parameter, too. In fact, it seems like Clickhouse Cloud (version 24.8.1.10545) is simply not accepting user/password combinations anymore.

% curl -X GET 'https://console-api.clickhouse.cloud/.api/services/<SERVICE>/query/?default_format=jsoneachrow&user=<USER>&password=<PASSWORD>&query=select%20version%28%29' 
{"error":"Unauthorized"}%  

Whereas the same request with Basic Auth works.

% curl --user '<USER>:<PASSWORD>' 'https://console-api.clickhouse.cloud/.api/services/<SERVICE>/query/?default_format=jsoneachrow&query=select%20version%28%29'     
{"version()":"24.8.1.10545"}

Is there a way ch_scan could specify a Basic Auth or -X header as described in Clickhouse's documentation?

[lmangani]

Hello @ctoprimer

The current ch_scan function is just a macro:

WITH
	  "json" AS (SELECT * FROM read_json_auto(concat(server, '/?default_format=jsoneachrow&user=', user, '&query=', query))),
	  "parquet" AS (SELECT * FROM read_parquet(concat(server, '/?default_format=parquet&user=', user, '&query=', query))),
	  "csv" AS (SELECT * FROM read_csv_auto(concat(server, '/?default_format=csv&user=', user, '&query=', query))),
	  "blob" AS (SELECT * FROM read_blob(concat(server, '/?default_format=blob&user=', user, '&query=', query))),
	  "text" AS (SELECT * FROM read_text(concat(server, '/?default_format=text&user=', user, '&query=', query)))
	FROM query_table(CASE
	  WHEN LOWER(format) LIKE 'json%' THEN 'json'
	  WHEN LOWER(format) LIKE 'csv%' THEN 'csv'
	  WHEN LOWER(format) = 'parquet' THEN 'parquet'
	  WHEN LOWER(format) = 'blob' THEN 'blob'
	  WHEN LOWER(format) = 'text' THEN 'text'
	  ELSE 'json'
	END

I assume you tried using basic auth as part of the URL? ie: https://username:pass@host and that failed?

I don't have a CH cloud setup to test with but the following might do the trick:

CREATE SECRET extra_http_headers (
      TYPE HTTP,
      EXTRA_HTTP_HEADERS MAP{
          'X-ClickHouse-User': 'user',
          'X-ClickHouse-Key': 'password'
      }
  );

We're slowly working on a native client update to offer binary protocol support and all authentication options.

[lmangani]

@ctoprimer we released a binary client clickhouse_scan in the chsql_native extension

# Local Setup, Insecure
export CLICKHOUSE_URL="tcp://localhost:9000"
# Remote Setup, Secure
export CLICKHOUSE_URL="tcp://user:pass@remote:9440/?secure=true&skip_verify=true"

--- Install
D INSTALL chsql_native FROM community;
D LOAD chsql_native;
D SELECT * FROM clickhouse_scan("SELECT version(), 'hello', 123");
┌────────────┬─────────┬────────┐
│ version()  │ 'hello' │  123   │
│  varchar   │ varchar │ uint32 │
├────────────┼─────────┼────────┤
│ 24.10.2.80 │ hello   │    123 │
└────────────┴─────────┴────────┘