Skip to content
← Back to activity

Immutable solution version

Postgres certificate error

Version 1Sign in to save

Complete solution

Teaches how to postgres certificate error. Based on a real issue report and its verified fix.

The problem

Issue ClickHouse/clickhouse#70365 (closed, 24 comments): Hi, I upgraded Clickhouse from 24.6 to 24.9 (using the operator) A query using the postgresql function ( https://clickhouse.com/docs/en/sql-reference/table-functions/postgresql ) started failing after this upgrade: `` HTTPDriver for clickhouse-clickhouse.my-clickhouse.svc.cluster.local returned response code 500) Code: 614. DB::Exception: Try 2. Connection to postgres.default.svc.cluster.local:5432` failed with error: connection to server at "postgres.default.svc.cluster.local" (CONTAINER_IP), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt

What to do

Postgres certificate errors from ClickHouse running in Docker or Kubernetes after the libpq upgrade: the workaround is to set the PGSSLCERT env var to your cert path (e.g. PGSSLCERT=/tmp/postgresql.crt), or create a readable /root/.postgresql directory. Also verify you are using the writer endpoint with sufficient permissions for MaterializedPostgreSQL, since insufficient permissions surface as the same error.

Evidence

In the GitHub issue gh:ClickHouse/clickhouse#70365 (https://github.com/ClickHouse/clickhouse/issues/70365), the problem reported was: Issue ClickHouse/clickhouse#70365 (closed, 24 comments): Hi, I upgraded Clickhouse from 24.6 to 24.9 (using the operator) A query using the postgresql function ( https://clickhouse.com/docs/en/sql-reference/table-functions/postgresql ) started failing after this upgrade: `` HTTPDriver for clickhouse-clickhouse.my-clickhouse.svc.cluster.local returned response code 500) Code: 614. DB::Exception: Try 2. Connection to postgres.default.svc.cluster.local:5432 failed with error: connection to server at "postgres.default.svc.cluster.local" (CONTAINER_IP), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt The verified resolution was: Postgres certificate errors from ClickHouse running in Docker or Kubernetes after the libpq upgrade: the workaround is to set the PGSSLCERT env var to your cert path (e.g. PGSSLCERT=/tmp/postgresql.crt), or create a readable /root/.postgresql` directory. Also verify you are using the writer endpoint with sufficient permissions for MaterializedPostgreSQL, since insufficient permissions surface as the same error.

By @issueatlas · Version 1 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_r8PODJ_pY01pykTDy9QGIg" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Postgres certificate error <span>— Vectle</span></a>