Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3cue-00028u-O3 for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Dec 2022 12:55:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p3cud-0008Ox-LE for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Dec 2022 12:55:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3cud-00089l-BN for pgsql-hackers@lists.postgresql.org; Fri, 09 Dec 2022 12:55:35 +0000 Received: from udcm-wwu2.uni-muenster.de ([128.176.118.28]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p3cuW-00033U-Tr for pgsql-hackers@lists.postgresql.org; Fri, 09 Dec 2022 12:55:34 +0000 X-IronPort-AV: E=Sophos;i="5.96,230,1665439200"; d="scan'208,217,223";a="212515709" Received: from secmail.uni-muenster.de ([128.176.118.4]) by UDCM-RELAY2.UNI-MUENSTER.DE with ESMTP; 09 Dec 2022 13:55:29 +0100 Received: from [192.168.178.27] (dynamic-092-229-035-186.92.229.pool.telefonica.de [92.229.35.186]) by SECMAIL.UNI-MUENSTER.DE (Postfix) with ESMTPSA id F2D1D210EEAD; Fri, 9 Dec 2022 13:55:27 +0100 (CET) Content-Type: multipart/mixed; boundary="------------pWb5bxb2gzBDSihFF7JnkDXh" Message-ID: <07eaed48-0da7-bf5b-b299-6f5f49264dae@uni-muenster.de> Date: Fri, 9 Dec 2022 13:55:25 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Content-Language: en-US, de-DE To: PostgreSQL Hackers From: Jim Jones Subject: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------pWb5bxb2gzBDSihFF7JnkDXh Content-Type: multipart/alternative; boundary="------------ZFKo0ow6YhVoqwQK7kRrRy5J" --------------ZFKo0ow6YhVoqwQK7kRrRy5J Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Dear PostgreSQL Hackers, Some time ago we faced a small issue in libpq regarding connections configured in the pg_hba.conf as type *hostssl* and using *md5* as authentication method. One of our users placed the client certificates in ~/.postgresql/ (*postgresql.crt,**postgresql.key*), so that libpq sends them to the server without having to manually set *sslcert* and *sslkey* - which is quite convenient. However, there are other servers where the same user authenticates with password (md5), but libpq still sends the client certificates for authentication by default. This causes the authentication to fail even before the user has the chance to enter his password, since he has no certificate registered in the server. To make it clearer: Although the connection is configured as ... *host  all  dummyuser 192.168.178.42/32  md5 * ... and the client uses the following connection string ... *psql "host=myserver dbname=db user=***dummyuser*" * ... the server tries to authenticate the user using the client certificates in *~/.postgresql/* and, as expected, the authentication fails: *psql: error: connection to server at "myserver" (xx.xx.xx.xx), port 5432 failed: SSL error: tlsv1 alert unknown ca* Server log: ** *2022-12-09 10:50:59.376 UTC [13896] LOG:  could not accept SSL connection: certificate verify failed * Am I missing something?** Obviously it would suffice to just remove or rename *~/.postgresql/**postgresql.{crt,key}*, but the user needs them to authenticate in other servers. So we came up with the workaround to create a new sslmode (no-clientcert) to make libpq explicitly ignore the client certificates, so that we can avoid ssl authentication errors. These small changes can be seen in the patch file attached. *psql "host=myserver dbname=db user=****dummyuser** sslrootcert=server.crt sslmode=no-clientcert"* Any better ideas to make libpq ignore *~/.postgresql/**postgresql.{crt,key}***? Preferably without having to change the source code :) Thanks in advance! Best, Jim --------------ZFKo0ow6YhVoqwQK7kRrRy5J Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Dear PostgreSQL Hackers,

Some time ago we faced a small issue in libpq regarding connections configured in the pg_hba.conf as type hostssl and using md5 as authentication method.

One of our users placed the client certificates in ~/.postgresql/ (postgresql.crt,postgresql.key), so that libpq sends them to the server without having to manually set sslcert and sslkey - which is quite convenient. However, there are other servers where the same user authenticates with password (md5), but libpq still sends the client certificates for authentication by default. This causes the authentication to fail even before the user has the chance to enter his password, since he has no certificate registered in the server.

To make it clearer:

Although the connection is configured as ...

host  all  dummyuser  192.168.178.42/32  md5

... and the client uses the following connection string ...

psql "host=myserver dbname=db user=dummyuser"

... the server tries to authenticate the user using the client certificates in ~/.postgresql/ and, as expected, the authentication fails:

psql: error: connection to server at "myserver" (xx.xx.xx.xx), port 5432 failed: SSL error: tlsv1 alert unknown ca

Server log:

2022-12-09 10:50:59.376 UTC [13896] LOG:  could not accept SSL connection: certificate verify failed

Am I missing something?

Obviously it would suffice to just remove or rename ~/.postgresql/postgresql.{crt,key}, but the user needs them to authenticate in other servers. So we came up with the workaround to create a new sslmode (no-clientcert) to make libpq explicitly ignore the client certificates, so that we can avoid ssl authentication errors. These small changes can be seen in the patch file attached.

psql "host=myserver dbname=db user=dummyuser sslrootcert=server.crt sslmode=no-clientcert"

Any better ideas to make libpq ignore ~/.postgresql/postgresql.{crt,key}? Preferably without having to change the source code :) Thanks in advance!

Best,

Jim

--------------ZFKo0ow6YhVoqwQK7kRrRy5J-- --------------pWb5bxb2gzBDSihFF7JnkDXh Content-Type: text/x-patch; charset=UTF-8; name="v1-0001-add-sslmode-no-clientcert.patch" Content-Disposition: attachment; filename="v1-0001-add-sslmode-no-clientcert.patch" Content-Transfer-Encoding: base64 RnJvbSA4MDEzNjE5YTFlZTIxODdmY2UyODkwNTRhODQ2ZTE3YTU0MTQ4MDFkIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBKaW0gSm9uZXMgPGppbS5qb25lc0B1bmktbXVlbnN0 ZXIuZGU+CkRhdGU6IE1vbiwgMjEgTm92IDIwMjIgMTU6MDU6MzMgKzAxMDAKU3ViamVjdDog W1BBVENIIHYxXSBhZGQgc3NsbW9kZSBuby1jbGllbnRjZXJ0CgotLS0KIGRvYy9zcmMvc2dt bC9saWJwcS5zZ21sICAgICAgICAgICAgICAgICAgfCAxMSArKysrKysrKysKIHNyYy9pbnRl cmZhY2VzL2xpYnBxL2ZlLWNvbm5lY3QuYyAgICAgICAgfCAgMSArCiBzcmMvaW50ZXJmYWNl cy9saWJwcS9mZS1zZWN1cmUtb3BlbnNzbC5jIHwgMjkgKysrKysrKysrKysrKysrKysrKysr KystCiAzIGZpbGVzIGNoYW5nZWQsIDQwIGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkK CmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvbGlicHEuc2dtbCBiL2RvYy9zcmMvc2dtbC9s aWJwcS5zZ21sCmluZGV4IGY5NTU4ZGVjM2IuLmFhMTQyNTNmN2UgMTAwNjQ0Ci0tLSBhL2Rv Yy9zcmMvc2dtbC9saWJwcS5zZ21sCisrKyBiL2RvYy9zcmMvc2dtbC9saWJwcS5zZ21sCkBA IC04NjM4LDYgKzg2MzgsMTcgQEAgbGRhcDovL2xkYXAuYWNtZS5jb20vY249ZGJzZXJ2ZXIs Y249aG9zdHM/cGdjb25uZWN0aW5mbz9iYXNlPyhvYmplY3RjbGFzcz0qKQogICAgICAgPC9l bnRyeT4KICAgICAgPC9yb3c+CiAKKyAgICAgPHJvdz4KKyAgICAgIDxlbnRyeT48bGl0ZXJh bD5uby1jbGllbnRjZXJ0PC9saXRlcmFsPjwvZW50cnk+CisgICAgICA8ZW50cnk+WWVzPC9l bnRyeT4KKyAgICAgIDxlbnRyeT5EZXBlbmRzIG9uIENBIHBvbGljeTwvZW50cnk+CisgICAg ICA8ZW50cnk+SSB3YW50IG15IGRhdGEgZW5jcnlwdGVkLCBhbmQgSSBhY2NlcHQgdGhlIG92 ZXJoZWFkLiBJIHdhbnQgdG8gYmUKKyAgICAgICBzdXJlIHRoYXQgSSBjb25uZWN0IHRvIGEg c2VydmVyIHRoYXQgSSB0cnVzdCwgYnV0IEkgZG8gbm90IHdpc2ggdG8gCisgICAgICAgYXV0 aGVudGljYXRlIHRoZSBjbGllbnQgdXNpbmcgc3NsIGNlcnRpZmljYXRlcy4KKyAgICAgIDwv ZW50cnk+CisgICAgIDwvcm93PgorICAgICAKKyAgICAgCiAgICAgIDxyb3c+CiAgICAgICA8 ZW50cnk+PGxpdGVyYWw+dmVyaWZ5LWNhPC9saXRlcmFsPjwvZW50cnk+CiAgICAgICA8ZW50 cnk+WWVzPC9lbnRyeT4KZGlmZiAtLWdpdCBhL3NyYy9pbnRlcmZhY2VzL2xpYnBxL2ZlLWNv bm5lY3QuYyBiL3NyYy9pbnRlcmZhY2VzL2xpYnBxL2ZlLWNvbm5lY3QuYwppbmRleCBmODhk NjcyYzZjLi4wOGM3ZjIxZWMxIDEwMDY0NAotLS0gYS9zcmMvaW50ZXJmYWNlcy9saWJwcS9m ZS1jb25uZWN0LmMKKysrIGIvc3JjL2ludGVyZmFjZXMvbGlicHEvZmUtY29ubmVjdC5jCkBA IC0xMjY4LDYgKzEyNjgsNyBAQCBjb25uZWN0T3B0aW9uczIoUEdjb25uICpjb25uKQogCQkJ JiYgc3RyY21wKGNvbm4tPnNzbG1vZGUsICJhbGxvdyIpICE9IDAKIAkJCSYmIHN0cmNtcChj b25uLT5zc2xtb2RlLCAicHJlZmVyIikgIT0gMAogCQkJJiYgc3RyY21wKGNvbm4tPnNzbG1v ZGUsICJyZXF1aXJlIikgIT0gMAorCQkJJiYgc3RyY21wKGNvbm4tPnNzbG1vZGUsICJuby1j bGllbnRjZXJ0IikgIT0gMAogCQkJJiYgc3RyY21wKGNvbm4tPnNzbG1vZGUsICJ2ZXJpZnkt Y2EiKSAhPSAwCiAJCQkmJiBzdHJjbXAoY29ubi0+c3NsbW9kZSwgInZlcmlmeS1mdWxsIikg IT0gMCkKIAkJewpkaWZmIC0tZ2l0IGEvc3JjL2ludGVyZmFjZXMvbGlicHEvZmUtc2VjdXJl LW9wZW5zc2wuYyBiL3NyYy9pbnRlcmZhY2VzL2xpYnBxL2ZlLXNlY3VyZS1vcGVuc3NsLmMK aW5kZXggYmFkODUzNTliNi4uYzA5MjAyMGM1ZSAxMDA2NDQKLS0tIGEvc3JjL2ludGVyZmFj ZXMvbGlicHEvZmUtc2VjdXJlLW9wZW5zc2wuYworKysgYi9zcmMvaW50ZXJmYWNlcy9saWJw cS9mZS1zZWN1cmUtb3BlbnNzbC5jCkBAIC0xMDk4LDcgKzEwOTgsMzQgQEAgaW5pdGlhbGl6 ZV9TU0woUEdjb25uICpjb25uKQogCWlmIChjb25uLT5zc2xjZXJ0ICYmIHN0cmxlbihjb25u LT5zc2xjZXJ0KSA+IDApCiAJCXN0cmxjcHkoZm5idWYsIGNvbm4tPnNzbGNlcnQsIHNpemVv ZihmbmJ1ZikpOwogCWVsc2UgaWYgKGhhdmVfaG9tZWRpcikKLQkJc25wcmludGYoZm5idWYs IHNpemVvZihmbmJ1ZiksICIlcy8lcyIsIGhvbWVkaXIsIFVTRVJfQ0VSVF9GSUxFKTsKKwl7 CisKKwkJLyogc3NsbW9kZSBuby1jbGllbnRjZXJ0ICovCisJCWlmIChjb25uLT5zc2xtb2Rl WzBdID09ICduJykKKwkJeworCisJCQkvKgorCQkJICogVGhlIG9wdGlvbiAibm8tY2xpZW50 Y2VydCIgaWdub3JlcyB0aGUgY2xpZW50IGNlcnRpZmljYXRlIGluIGNhc2UgdGhleSBhcmUK KwkJCSAqIHN0b3JlZCBpbiB+Ly5wb3N0Z3Jlc3FsL3Bvc3RncmVzcWwuY3J0IGFuZCB+Ly5w b3N0Z3Jlc3FsL3Bvc3RncmVzcWwua2V5LAorCQkJICogYW5kIHRoZXJlZm9yZSBhdXRvbWF0 aWNhbGx5IHNlbnQgdG8gdGhlIHNlcnZlci4gVGhpcyBpcyB1c2VmdWwgZm9yCisJCQkgKiBw Z19oYmEuY29uZiBlbnRyaWVzIG9mIHR5cGUgImhvc3Rzc2wiIHdpdGhvdXQgImNlcnQiIGFz IGF1dGhlbnRpY2F0aW9uCisJCQkgKiBtZXRob2QgLSBlLmcuIHVzaW5nICJtZDUiIG9yICJz Y3JhbS1zaGEtMjU2IiAtIGFzIHRoZXkgd2lsbCBmYWlsIGlmCisJCQkgKiB0aGUgY2xpZW50 IGNlcnRpZmljYXRlIGlzIHNlbnQgdG8gdGhlIHNlcnZlciBpbiB0aGUgYmFja2dyb3VuZCBh bmQgaXQKKwkJCSAqIGRvZXMgbm90IGV4aXN0IGluIHRoZSBzZXJ2ZXIncyAnc3NsX2NhX2Zp bGUnLgorCQkJICovCisKKwkJCWZuYnVmWzBdID0gJ1wwJzsKKworCQl9CisJCWVsc2UKKwkJ eworCisJCQlzbnByaW50ZihmbmJ1Ziwgc2l6ZW9mKGZuYnVmKSwgIiVzLyVzIiwgaG9tZWRp ciwgVVNFUl9DRVJUX0ZJTEUpOworCisJCX0KKworCisJfQogCWVsc2UKIAkJZm5idWZbMF0g PSAnXDAnOwogCi0tIAoyLjI1LjEKCg== --------------pWb5bxb2gzBDSihFF7JnkDXh--