Content-Type: multipart/alternative;
 boundary="------------X1mOAzZFTh0wttRyiVBOOF08"
Message-ID: <77196b85-f79e-4c9a-afef-8b5ad6f0ee44@hot.ee>
Date: Sat, 7 Jun 2025 09:40:02 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: et
To: pgsql-general <pgsql-general@postgresql.org>
From: Andrus <kobruleht2@hot.ee>
Subject: How to use cert authentication with pg_dump in windows
Archived-At: <https://www.postgresql.org/message-id/77196b85-f79e-4c9a-afef-8b5ad6f0ee44%40hot.ee>
Precedence: bulk

This is a multi-part message in MIME format.
--------------X1mOAzZFTh0wttRyiVBOOF08
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi!

Postgres 17 server certificate is issued by RapidSsl for 
server.mydomain.com

PostgreSQL 17 client certificate is created using this tutorial[1].

pg_hba.conf first line contains:

hostssl all varukoopia ::/0 cert


Trying to use it on Windows Server:

set PGSSLCERT=client.crt
set PGSSLKEY=client.key
set PGSSLROOTCERT=system
"c:\Program Files\PostgreSQL\17\bin\pg_dump" --verbose -b -f 
"test.backup" -F c -h localhost -p 5432 -U varukoopia mydatabase

Throws this error:

 > pg_dump: error: connection to server at "localhost" (::1), port 5432 
failed: SSL error: unregistered scheme

How to use the client certificate with `pg_dump`?

According to https://github.com/EnterpriseDB/edb-installers/issues/264

windows system certificate store is not supported. How to specify root 
certificates or disable certificate verification? Certtificate can used 
only to log on to postgres without password.

   [1]: 
https://docs.devart.com/studio-for-postgresql/connecting-to-db/generating-ssl-certificate.html


Andrus

--------------X1mOAzZFTh0wttRyiVBOOF08
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi!<br>
    </p>
    <p>Postgres 17 server certificate is issued by RapidSsl for
      server.mydomain.com <br>
    </p>
    <p>PostgreSQL 17 client certificate is created using this
      tutorial[1].<br>
      <br>
      pg_hba.conf first line contains:<br>
      <br>
      hostssl all varukoopia ::/0 cert<br>
      <br>
      <br>
      Trying to use it on Windows Server: <br>
      <br>
      set PGSSLCERT=client.crt<br>
      set PGSSLKEY=client.key<br>
      set PGSSLROOTCERT=system<br>
      "c:\Program Files\PostgreSQL\17\bin\pg_dump" --verbose -b -f
      "test.backup" -F c -h localhost -p 5432 -U varukoopia mydatabase<br>
      <br>
      Throws this error:<br>
      <br>
      &gt; pg_dump: error: connection to server at "localhost" (::1),
      port 5432 failed: SSL error: unregistered scheme<br>
      <br>
      How to use the client certificate with `pg_dump`?<br>
      <br>
      According to
      <a class="moz-txt-link-freetext" href="https://github.com/EnterpriseDB/edb-installers/issues/264">https://github.com/EnterpriseDB/edb-installers/issues/264</a><br>
      <br>
      windows system certificate store is not supported. How to specify
      root certificates or disable certificate verification?
      Certtificate can used only to log on to postgres without password.<br>
      <br>
        [1]:
<a class="moz-txt-link-freetext" href="https://docs.devart.com/studio-for-postgresql/connecting-to-db/generating-ssl-certificate.html">https://docs.devart.com/studio-for-postgresql/connecting-to-db/generating-ssl-certificate.html</a></p>
    <p><br>
    </p>
    <p>Andrus<br>
    </p>
  </body>
</html>

--------------X1mOAzZFTh0wttRyiVBOOF08--