Immutable solution version
Postgres certificate error
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>