> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for the PostgreSQL wire protocol interface in ClickHouse

# PostgreSQL interface

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

<Tip>
  Check out our [Managed Postgres](/products/managed-postgres/overview) service. Backed by NVMe storage that is physically collocated with compute, it delivers up to 10x faster performance for workloads that are disk-bound compared to alternatives using network-attached storage like EBS and allows you to replicate your Postgres data to ClickHouse using the Postgres CDC connector in ClickPipes.
</Tip>

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 isn't already directly supported by ClickHouse (for example, Amazon Redshift).

To enable the PostgreSQL wire protocol, add the [postgresql\_port](/reference/settings/server-settings/settings#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:

```xml theme={null}
<clickhouse>
    <postgresql_port>9005</postgresql_port>
</clickhouse>
```

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

```response theme={null}
{} <Information> Application: Listening for PostgreSQL compatibility protocol: 127.0.0.1:9005
```

<h2 id="connect-psql-to-clickhouse">
  Connect psql to ClickHouse
</h2>

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

```bash theme={null}
psql -p [port] -h [hostname] -U [username] [database_name]
```

For example:

```bash theme={null}
psql -p 9005 -h 127.0.0.1 -U alice default
```

<Note>
  The `psql` client requires a login with a password, so you won't be able connect using the `default` user with no password. Either assign a password to the `default` user, or login as a different user.
</Note>

The `psql` client prompts for the password:

```response theme={null}
Password for user alice:
psql (14.2, server 22.3.1.1)
WARNING: psql major version 14, server major version 22.
         Some psql features might not work.
Type "help" for help.

default=>
```

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.
</Note>

<h2 id="using-ssl">
  Using SSL
</h2>

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:

```bash theme={null}
psql "port=9005 host=127.0.0.1 user=alice dbname=default sslcert=/path/to/certificate.pem sslkey=/path/to/key.pem sslrootcert=/path/to/rootcert.pem sslmode=verify-ca"
```

<h2 id="using-scram-sha256">
  Configuring ClickHouse user authentication with SCRAM-SHA-256
</h2>

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`:

```xml theme={null}
<user_with_sha256>
    <password_scram_sha256_hex>04e7a70338d7af7bb6142fe7e19fef46d9b605f3e78b932a60e8200ef9154976</password_scram_sha256_hex>
</user_with_sha256>
```

View the [PostgreSQL docs](https://jdbc.postgresql.org/documentation/head/ssl-client.html) for more details on their SSL settings.