PostgreSQL Interface

ClickHouse supports the PostgreSQL wire protocol, which allows you to use Postgres clients to connect to ClickHouse. In a sense, ClickHouse can pretend to be a PostgreSQL instance - allowing you to connect a PostgreSQL client application to ClickHouse that is not already directly supported by ClickHouse (for example, Amazon Redshift).

To enable the PostgreSQL wire protocol, add the postgresql_port setting to your server's configuration file. For example, you could define the port in a new XML file in your config.d folder:

Startup your ClickHouse server and look for a log message similar to the following that mentions Listening for PostgreSQL compatibility protocol:

Connect psql to ClickHouse

The following command demonstrates how to connect the PostgreSQL client psql to ClickHouse:

For example:

note

The psql client requires a login with a password, so you will not be able connect using the default user with no password. Either assign a password to the default user, or login as a different user.

The psql client prompts for the password:

And that's it! You now have a PostgreSQL client connected to ClickHouse, and all commands and queries are executed on ClickHouse.

note

The PostgreSQL protocol currently only supports plain-text passwords.

Using SSL

If you have SSL/TLS configured on your ClickHouse instance, then postgresql_port will use the same settings (the port is shared for both secure and insecure clients).

Each client has their own method of how to connect using SSL. The following command demonstrates how to pass in the certificates and key to securely connect psql to ClickHouse:

Configuring ClickHouse User Authentication with SCRAM-SHA-256

To ensure secure user authentication in ClickHouse, it is recommended to use the SCRAM-SHA-256 protocol. Configure the user by specifying the password_scram_sha256_hex element in the users.xml file. The password hash must be generated with num_iterations=4096.

Ensure that the psql client supports and negotiates SCRAM-SHA-256 during connection.

Example configuration for user user_with_sha256 with the password abacaba:

View the PostgreSQL docs for more details on their SSL settings.