Message-ID: <05afcadd-0e31-751c-c915-cb27e6bf5a5b@jakobs.com>
Date: Thu, 22 Dec 2022 10:40:04 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.0
To: pgsql-admin@lists.postgresql.org
References: 
 <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: Facing issue with cert authentication
In-Reply-To: 
 <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------cKPxezC5wTkw0L6sdmyf8Drq"
Archived-At: 
 <https://www.postgresql.org/message-id/05afcadd-0e31-751c-c915-cb27e6bf5a5b%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------cKPxezC5wTkw0L6sdmyf8Drq
Content-Type: multipart/mixed; boundary="------------cO5du4LAouU009n08DJbfyN2";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <05afcadd-0e31-751c-c915-cb27e6bf5a5b@jakobs.com>
Subject: Re: Facing issue with cert authentication
References: <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>
In-Reply-To: <CAEyh4sbEsn0BAb5KXf_wUgoJVSd=9Nj4aNZP626zVPKdy4HmXw@mail.gmail.com>

--------------cO5du4LAouU009n08DJbfyN2
Content-Type: multipart/alternative;
 boundary="------------XxOsZviYtCXspDmVujW1fq17"

--------------XxOsZviYtCXspDmVujW1fq17
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMjIuMTIuMjIgdW0gMDU6MjUgc2NocmllYiBEaGlyZW5kcmEgU2luZ2g6DQo+IEhpIEFs
bCwNCj4gSSBhbSB1c2luZyBjZXJ0IGF1dGhlbnRpY2F0aW9uIHRvIGF1dGhlbnRpY2F0ZS4N
Cj4gSSBoYXZlIGNyZWF0ZWQgYSB1c2VyIHdpdGggbmFtZSBTMTE0NTQ2ICh3aXRoIHVwcGVy
Y2FzZSAnUycpLiB1c2VyIA0KPiBjcmVhdGVkIGlzIHMxMTQ1NDYgKHdpdGggbG93ZXJjYXNl
ICdzJykuDQo+IENOIGluIHRoZSBjbGllbnQgY2VydGlmaWNhdGUgaXMgInBnLXJlYWQgKFMx
MTQ1NDYpIi4gJ1MnIGluIFMxMTQ1NDYgaXMgDQo+IHVwcGVyY2FzZS4gSSBoYXZlIG5vIGNv
bnRyb2wgdG8gaGF2ZSB0aGUgJ1MnIGluIHRoZSBDTiBpbiBsb3dlcmNhc2UuIA0KPiBNeSBv
cmdhbml6YXRpb24gUEtJIGFsd2F5cyBjcmVhdGUgdGhlIGNlcnRpZmljYXRlIHdpdGggdXBw
ZXJjYXNlICdTJy4NCj4gSSBleHRyYWN0ZWQgdGhlIHN0cmluZyBTMTE0NTQ2IGZyb20gdGhl
IENOIHVzaW5nIHJlZ2V4IGluIHRoZSANCj4gcGdfaWRlbnQuY29uZiBmaWxlLg0KPiBjZXJ0
LWNuLW1hcCDCoCDCoCAvXi4qWyhdKFtTc11bMC05Ll0qKVspXSQgwqAgwqAgwqAgXDENCj4N
Cj4gTm93IHdoZW4gaSB0cnkgdG8gY29ubmVjdCB1c2luZyBwc3FsLCBhdXRoZW50aWNhdGlv
biBmYWlscy4gSSB0cnkgdG8gDQo+IGNvbm5lY3Qgd2l0aCBib3RoIGFzIHVzZXIgUzExNDU0
Nih1cHBlcmNhc2UgUykgYXMgd2VsbCBhcyANCj4gczExNDU0Nihsb3dlcmNhc2UgcykuIElu
IGJvdGggY2FzZSBpdCBmYWlscy4NCj4NCj4gV2hlbiBpIHRyeSB0byBjb25uZWN0IHdpdGgg
UzExNDU0NiwgaXQgZmFpbCB3aXRoIG1lc3NhZ2UgdGhhdCBubyByb2xlIA0KPiAiUzExNDU0
NiIgZXhpc3QuDQo+IHBzcWwgImhvc3Q9cG9zdGdyZXMuYXBwLm5ldCA8aHR0cDovL3Bvc3Rn
cmVzLmFwcC5uZXQ+IHVzZXI9UzExNDU0NiANCj4gZGJuYW1lPWFwcGRiIHNzbG1vZGU9dmVy
aWZ5LWZ1bGwgc3NsY2VydD1jZXJ0LnBlbSBzc2xrZXk9Y2VydC1rZXkucGVtIA0KPiBzc2xy
b290Y2VydD10bHMtY2EtYnVuZGxlLnBlbSINCj4gcHNxbDogZXJyb3I6IGNvbm5lY3Rpb24g
dG8gc2VydmVyIGF0ICJwb3N0Z3Jlcy5hcHAubmV0IA0KPiA8aHR0cDovL3Bvc3RncmVzLmFw
cC5uZXQ+IiAoMTAuMTI5LjE4Ny4yNyksIHBvcnQgNTQzMiBmYWlsZWQ6IEZBVEFMOiANCj4g
wqByb2xlICJTMTE0NTQ2IiBkb2VzIG5vdCBleGlzdA0KPg0KPiBXaGVuIGkgdHJ5IHRvIGNv
bm5lY3Qgd2l0aCBzMTE0NTQ2LCBjZXJ0aWZpY2F0ZSBhdXRoZW50aWNhdGlvbiBmYWlsLiAN
Cj4gZXh0cmFjdGVkIHVzZXJuYW1lIGZyb20gQ04gaXMgUzExNDU0Nih1cHBlcmNhc2UgUykg
YW5kIHN1cHBsaWVkIA0KPiB1c2VybmFtZSBpbiBjb25uZWN0aW9uIGlzIHMxMTQ1NDYobG93
ZXJjYXNlIHMpLg0KPiBwc3FsICJob3N0PXBvc3RncmVzLmFwcC5uZXQgPGh0dHA6Ly9wb3N0
Z3Jlcy5hcHAubmV0PiB1c2VyPXMxMTQ1NDYgDQo+IGRibmFtZT1hcHBkYiBzc2xtb2RlPXZl
cmlmeS1mdWxsIHNzbGNlcnQ9Y2VydC5wZW0gc3Nsa2V5PWNlcnQta2V5LnBlbSANCj4gc3Ns
cm9vdGNlcnQ9dGxzLWNhLWJ1bmRsZS5wZW0iDQo+IHBzcWw6IGVycm9yOiBjb25uZWN0aW9u
IHRvIHNlcnZlciBhdCAicG9zdGdyZXMuYXBwLm5ldCANCj4gPGh0dHA6Ly9wb3N0Z3Jlcy5h
cHAubmV0PiIgKDEwLjEyOS4xODcuMjcpLCBwb3J0IDU0MzIgZmFpbGVkOiBGQVRBTDogDQo+
IMKgY2VydGlmaWNhdGUgYXV0aGVudGljYXRpb24gZmFpbGVkIGZvciB1c2VyICJzMTE0NTQ2
Ig0KPg0KPiBpc24ndCBpdCBzdHJhbmdlIGJlaGF2aW9yPyB3aGlsZSBjcmVhdGluZyB0aGUg
dXNlciBpdCBpZ25vcmVzIHRoZSBjYXNlIA0KPiBidXQgY2hlY2tzIHRoZSBjYXNlIGR1cmlu
ZyBhdXRoZW50aWNhdGlvbi4NCj4gQW55b25lIGNhbiBwbGVhc2Ugc3VnZ2VzdCBob3cgdG8g
cmVzb2x2ZSB0aGlzIGlzc3VlID8NCj4gSSBjYW4gY3JlYXRlIHRoZSB1c2VyIHdpdGggdXBw
ZXJjYXNlICdTJyBieSBkb3VibGUgcXVvdGluZyB0aGUgDQo+IHVzZXJuYW1lLiBidXQgdGhl
IHNjcmlwdCB3aGljaCBjcmVhdGVzIHRoZSB1c2VyIHdpbGwgZG8gdGhlIHNhbWUgZm9yIA0K
PiBhbGwgdXNlcnMgd2hpY2ggaSBkbyBub3Qgd2FudC4NCj4NCj4gVGhhbmtzLA0KPiBEaGly
ZW5kcmEuDQoNCg0KQ2FuIHlvdSB0cnk6DQoNCnBzcWwgImhvc3Q9cG9zdGdyZXMuYXBwLm5l
dCA8aHR0cDovL3Bvc3RncmVzLmFwcC5uZXQ+IHVzZXI9XCJTMTE0NTQ2XCIgDQpkYm5hbWU9
YXBwZGIgc3NsbW9kZT12ZXJpZnktZnVsbCBzc2xjZXJ0PWNlcnQucGVtIHNzbGtleT1jZXJ0
LWtleS5wZW0gDQpzc2xyb290Y2VydD10bHMtY2EtYnVuZGxlLnBlbSINCg0KVGhpcyBzaG91
bGQga2VlcCB0aGUgdXNlciBuYW1lIGluIHVwcGVyIGNhc2UuIFdpdGhvdXQgcXVvdGVzICh3
aGljaCBoYXZlIA0KdG8gYmUgcHJvdGVjdGVkIGJ5IGEgcHJlY2VkaW5nIGJhY2tzbGFzaCwg
YmVjYXVzZSB0aGUgd2hvbGUgY29ubmluZm8gaXMgDQphbHJlYWR5IGluIHF1b3RlcykgdGhl
IG5hbWUgd2lsbCBiZSBmb2xkZWQgdG8gbG93ZXIgY2FzZS4NCg0KQmVzdCBSZWdhcmRzLA0K
DQpIb2xnZXINCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVs
LiArNDktMTc4LTk3NTkwMTINCg0K
--------------XxOsZviYtCXspDmVujW1fq17
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 22.12.22 um 05:25 schrieb Dhirendra=

      Singh:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CAEyh4sbEsn0BAb5KXf_wUgoJVSd=3D9Nj4aNZP626zVPKdy4HmXw@mail.gm=
ail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Hi All,<br>
        <div>I am using cert authentication to authenticate.<br>
          I have created a user with name S114546 (with uppercase 'S').
          user created is s114546 (with lowercase 's').<br>
          CN in the client certificate is "pg-read (S114546)". 'S' in
          S114546 is uppercase. I have no control to have the 'S' in the
          CN in lowercase. My organization PKI always create the
          certificate with uppercase 'S'.<br>
          I extracted the string S114546 from the CN using regex in the
          pg_ident.conf file.<br>
          cert-cn-map =C2=A0 =C2=A0 /^.*[(]([Ss][0-9.]*)[)]$ =C2=A0 =C2=A0=
 =C2=A0 \1<br>
          <br>
          Now when i try to connect using psql, authentication fails. I
          try to connect with both as user S114546(uppercase S) as well
          as s114546(lowercase s). In both case it fails.<br>
          <br>
          When i try to connect with S114546, it fail with message that
          no role "S114546" exist. <br>
          psql "host=3D<a href=3D"http://postgres.app.net"
            moz-do-not-send=3D"true">postgres.app.net</a> user=3DS114546
          dbname=3Dappdb sslmode=3Dverify-full sslcert=3Dcert.pem
          sslkey=3Dcert-key.pem sslrootcert=3Dtls-ca-bundle.pem"<br>
          psql: error: connection to server at "<a
            href=3D"http://postgres.app.net" moz-do-not-send=3D"true">pos=
tgres.app.net</a>"
          (10.129.187.27), port 5432 failed: FATAL: =C2=A0role "S114546" =
does
          not exist<br>
          <br>
          When i try to connect with s114546, certificate authentication
          fail. extracted username from CN is S114546(uppercase S) and
          supplied username in connection is s114546(lowercase s).<br>
          psql "host=3D<a href=3D"http://postgres.app.net"
            moz-do-not-send=3D"true">postgres.app.net</a> user=3Ds114546
          dbname=3Dappdb sslmode=3Dverify-full sslcert=3Dcert.pem
          sslkey=3Dcert-key.pem sslrootcert=3Dtls-ca-bundle.pem"<br>
          psql: error: connection to server at "<a
            href=3D"http://postgres.app.net" moz-do-not-send=3D"true">pos=
tgres.app.net</a>"
          (10.129.187.27), port 5432 failed: FATAL: =C2=A0certificate
          authentication failed for user "s114546"<br>
          <br>
          isn't it strange behavior? while creating the user it ignores
          the case but checks the case during authentication.<br>
          Anyone can please suggest how to resolve this issue ?<br>
          I can create the user with uppercase 'S' by double quoting the
          username. but the script which creates the user will do the
          same for all users which i do not want.<br>
        </div>
        <div><br>
        </div>
        <div>Thanks,</div>
        <div>Dhirendra.</div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>Can you try:</p>
    <p>psql "host=3D<a href=3D"http://postgres.app.net">postgres.app.net<=
/a>
      user=3D\"S114546\" dbname=3Dappdb sslmode=3Dverify-full sslcert=3Dc=
ert.pem
      sslkey=3Dcert-key.pem sslrootcert=3Dtls-ca-bundle.pem"</p>
    <p>This should keep the user name in upper case. Without quotes
      (which have to be protected by a preceding backslash, because the
      whole conninfo is already in quotes) the name will be folded to
      lower case.</p>
    <p>Best Regards,</p>
    <p>Holger<br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------XxOsZviYtCXspDmVujW1fq17--

--------------cO5du4LAouU009n08DJbfyN2--

--------------cKPxezC5wTkw0L6sdmyf8Drq
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY6Ql9AUDAAAAAAAKCRCKV5yTsxlhupR+
AJwOWg2R+FahRKqbcch9bvxDFmLyngCeJrvqrvRnSCad8jptaBJk9ScpKQQ=
=W5BW
-----END PGP SIGNATURE-----

--------------cKPxezC5wTkw0L6sdmyf8Drq--