I can not connect via postgres interface

[l-we]

ClickHouse server version 21.8.4.51 (official build).
psql (PostgreSQL) 13.4 (Debian 13.4-1.pgdg110+1)
Encryption :password_sha256_hex

psql -h 192.168.10.10 -U default -p 9005 "sslmode=disable" -W  
ERROR:  Authentication method is not supported

{} <Error> PostgreSQLHandler: DB::Exception: Authentication type [ERRFMT] is not supported.

[peter279k]

I think it's the issue. I also have the same issue about using PostgreSQL interface to cause password authentication method errors.

After investigating detailed above methods, I notice that PostgreSQL client is not compatible with ClickHouse PostgreSQL interface.

We can look at the PostgreSQL password authentication and ClickHouse password authentication and we can organize following method lists:

PostgreSQL client password authentication

• scram-sha-256
• md5
• plain text password

PostgreSQL interface provided by ClickHouse

• plain text password
• sha256 password
• double sha1 password
• ldap
• kerberos

When I create the user with sha256 password or double sha1 password in ClickHouse, it will cause authentication method issue with PostgreSQL client.

If creating user with the plain text password method in ClickHouse, it will connect ClickHouse with PostgreSQL client successfully.

Here are my experiments about above descriptions:

localadmin@clickhouse01:~$ clickhouse-client --host=IP_Address --port=9000 --password=password --database
="database"
ClickHouse client version 21.12.3.32 (official build).
Connecting to database database at IP_Address:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.

clickhouse01 :) create user peter HOST ANY IDENTIFIED WITH sha256_password by 'password';
clickhouse01 :) create user peter2 HOST ANY IDENTIFIED WITH plaintext_password by 'password';
clickhouse01 :) create user peter3 HOST ANY IDENTIFIED WITH double_sha1_password by 'password';

As we can see, I create three users with different password authentication methods.
And the experiment results about using PostgreSQL client are as follows:

ubuntu@iotdevice01:~/hardware-monitor$ psql --version
psql (PostgreSQL) 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
ubuntu@iotdevice01:~/hardware-monitor$
ubuntu@iotdevice01:~$ psql -h IP_ADDRESS -p 9005 -U peter --password
Password:
psql: error: connection to server at "IP_ADDRESS", port 9005 failed: SSL SYSCALL error: Success
connection to server at "IP_ADDRESS", port 9005 failed: ERROR:  Authentication method is not supported
ubuntu@iotdevice01:~$
ubuntu@iotdevice01:~$ psql -h IP_ADDRESS -p 9005 -U peter2 -d default --password
Password:
psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1), server 21.12.3.32)
WARNING: psql major version 13, server major version 21.
         Some psql features might not work.
Type "help" for help.

default=> show tables;
 name
------
(0 rows)

default=>\q
ubuntu@iotdevice01:~$ psql -h IP_ADDRESS -p 9005 -U peter3 --password
Password:
psql: error: connection to server at "IP_ADDRESS", port 9005 failed: SSL SYSCALL error: Success
connection to server at "IP_ADDRESS", port 9005 failed: ERROR:  Authentication method is not supported

We can see that it's worked for the peter2 user and others are failed. I think the possible issues are as follows:

• The ClickHouse PostgreSQL interface is not implemented fully.
• The ClickHouse PostgreSQL interface about SHA-256 is not supported for the PostgreSQL client about scram-sha-256.

@l-we, it only uses the plain text password to connect ClickHouse via PostgreSQL client. However, this method is not recommended because this approach may be under the sniffing attack.

I hope the password authentication will be fixed in the future released version.

[filimonov]

Encryption :password_sha256_hex

Section was added to the documentation: "The PostgreSQL protocol currently only supports plain-text passwords."

https://clickhouse.com/docs/en/interfaces/postgresql/#connect-psql-to-clickhouse

The error message is also quite clear:

ERROR: Authentication method is not supported