After upgrade of Clickhouse, it's unable to authenticate

[tluchowski]

After upgrade of Clickhouse server to 21.4.3.21 I started getting error:

Invalid authentication: it is not allowed to use Authorization HTTP header and authentication via parameters simultaneously

I am connecting as follows:
const clickhouse = new ClickHouse(
{ basicAuth : {
username: 'xxx',
password: 'yyy'
}}
);

I managed to get it to work again by commenting out the following lines in clickhouse module:

            //if (username) {
            //      url.searchParams.append('user', username);
            //}

[tluchowski]

I am running the most recent version of the module (2.3.0), just upgraded today in hope if would fix my issue, but it didn't

[TimonKK]

Hi. Could you please repeat you code execution with

new ClickHouse({ debug: true, ...});

and post output here
?

[tluchowski]

Dear TimonKK,

Please find below.

QueryCursor {
query: "SELECT xxx from yyy WHERE zzz",
data: undefined,
opts: { format: 'json', raw: false }
}
QueryCursor._getReqParams: params SELECT xxx from yyy WHERE zzz {
headers: { 'Content-Type': 'text/plain' },
url: 'http://myuser:mypass@localhost:8123/?user=myuser&session_timeout=60&output_format_json_quote_64bit_integers=0&enable_http_compression=0&query_id=xxxx&database=default&query=SELECT+zzz+FROM+yyy+WHERE+ZZZ+FORMAT+JSON%3B'
}
Query: SELECT xxx from yyy WHERE zzz

QueryCursor.exec: result SELECT xxx from yyy {
statusCode: 403,
body: 'Code: 516, e.displayText() = DB::Exception: Invalid authentication: it is not allowed to use Authorization HTTP header and authentication via parameters simultaneously (version 21.4.3.21 (official build))\n',
statusMessage: 'Forbidden',
headers: {
date: 'Sat, 17 Apr 2021 14:48:42 GMT',
connection: 'Close',
'content-type': 'text/plain; charset=UTF-8',
'x-clickhouse-server-display-name': 'hostname',
'transfer-encoding': 'chunked',
'x-clickhouse-exception-code': '516'
}
}
403: Code: 516, e.displayText() = DB::Exception: Invalid authentication: it is not allowed to use Authorization HTTP header and authentication via parameters simultaneously (version 21.4.3.21 (official build))

When I comment out the code I mentioned, it looks like below (please note in url there's no user parameter anymore), and Clickhouse is fine with it:
QueryCursor {
query: "select xxx from yyy where zzz ",
data: undefined,
opts: { format: 'json', raw: false }
}

QueryCursor._getReqParams: params SELECT xxx from yyy WHERE zzz {
headers: { 'Content-Type': 'text/plain' },
url: 'http://myuser:mypassword@localhost:8123/?session_timeout=60&output_format_json_quote_64bit_integers=0&enable_http_compression=0&query_id=xxxx&database=default&query=select+zzz+FROM+yyy+WHERE+ZZZ+FORMAT+JSON%3B'
}

[peterpoetzi]

I also encountered the problem just now when I updatet clickhouse
The problem ist in clickhouse/index.js

if (username) {
//url.searchParams.append('user', username);
}

if (password) {
//url.searchParams.append('password', password);
}

A hot fix is, to comment out the 2 lines that append to the URL. Clickhouse does not like it when you send the HTTP basic auth credentials in the url a second time
https://clickhouse.tech/codebrowser/html_report/ClickHouse/src/Server/HTTPHandler.cpp.html#303

[TimonKK]

Please to upgrade lib to 2.4.0

[tluchowski]

Please to upgrade lib to 2.4.0

I confirm this fixed my issue, thank you!

[hlopetz]

excuse me, where v 2.4.0 is? the code in this repository itself shows 2.3.0 and tests of this lib show the same error msg (this line esp). can i have the actual code in this repo?

[anish-g-nair]

The error message states: Invalid authentication: it is not allowed to use Authorization HTTP header and authentication via parameters simultaneously

Consider the config below:

const chConnConfig = {
    url: 'http://1.1.1.1 
    port: 0000 
    debug: false,
    basicAuth: {
        username: 'USER', 
        password: 'PASS', 
    },
    isUseGzip: false,
    format: "json", // "json" || "csv" || "tsv"
    config: {
        session_id                              : 'session_id if neeed',
        session_timeout                         : 60,
        output_format_json_quote_64bit_integers : 0,
        enable_http_compression                 : 0,
        database                                : 'MY_DB', 
    },
}

---

Solution 1

Upgrade the ClickHouse node module to latest version and that should fix this error.
ClickHouse node module: https://www.npmjs.com/package/clickhouse

Solution 2

Keep the same ClickHouse node module and make changes to the CH config chConnConfig (Changes below)

• Move username, password to outer level and comment basicAuth
  • This is what the error message states, that you cannot pass the authentication params simultaneously as both basicAuth (Authorization HTTP header) and as params (from outer level of the config object).
  • It seems that with the new CH DB and old CH node-module combination, CH is expecting that the old CH node-module will pass the authorization (username and password) directly as params and doesn't seem to entertain basicAuth for older CH node-module versions.
  • So as a check just commenting the basicAuth and turning on debug: true will show you that the username, password and database are passed as default and you will also not see the Invalid authentication...... error anymore.
• Move database also to outer level and comment from config: {....}

Modified config:

const chConnConfig = {
    url: 'http://1.1.1.1 
    port: 0000 
    debug: false,
    username: 'USER', 
    password: 'PASS', 
    database: 'MY_DB', 
    // basicAuth: {
    //     username: 'USER', 
    //     password: 'PASS', 
    // },
    isUseGzip: false,
    format: "json", // "json" || "csv" || "tsv"
    config: {
        session_id                              : 'session_id if neeed',
        session_timeout                         : 60,
        output_format_json_quote_64bit_integers : 0,
        enable_http_compression                 : 0,
        // database                                : 'MY_DB', 
    },
}

Hope this helps!. :-)