Message-ID: <6d800f7b-47e3-08e1-2a9e-0cd561af64cd@jakobs.com>
Date: Tue, 14 Nov 2023 15:51:53 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.12.0
Content-Language: de-DE
To: pgsql-admin@lists.postgresql.org
References: 
 <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
Cc: bernd.lentes@helmholtz-muenchen.de
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: Update "usename" in pg_user
In-Reply-To: 
 <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------rBitfMMYDVOku66jEN1BmxxX"
Archived-At: 
 <https://www.postgresql.org/message-id/6d800f7b-47e3-08e1-2a9e-0cd561af64cd%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------rBitfMMYDVOku66jEN1BmxxX
Content-Type: multipart/mixed; boundary="------------MavruxBtEy9vSI06I0JwCltF";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Cc: bernd.lentes@helmholtz-muenchen.de
Message-ID: <6d800f7b-47e3-08e1-2a9e-0cd561af64cd@jakobs.com>
Subject: Re: Update "usename" in pg_user
References: <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
In-Reply-To: <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>

--------------MavruxBtEy9vSI06I0JwCltF
Content-Type: multipart/alternative;
 boundary="------------d4Xoeu9T6VcaRRIxTQVVq7ic"

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

QW0gMTQuMTEuMjMgdW0gMTM6NTYgc2NocmllYiBCZXJuZCBMZW50ZXM6DQo+IEhpLA0KPg0K
PiB0aGUgbGFzdCB0aW1lIGkgd3JvdGUgYSBTUUwgY29tbWFuZCBpcyBhYm91dCAyMCB5ZWFy
cyBhZ28uDQo+IFNvIEknbSBhc2tpbmcgZm9yIHlvdXIgYWR2aWNlLg0KPiBJIGhhdmUgYSBE
QiB3aGljaCBpcyBhY2Nlc3NlZCBieSB+IDQwIHVzZXJzLg0KPiBUaGUgdXNlcnMgYXJlIGlu
IHBnX3VzZXIgYW5kIGF1dGhlbnRpY2F0ZWQgd2l0aCBMREFQLg0KDQpUaGVyZSBoYXZlIGJl
ZW4gc2V2ZXJhbCB3YXJuaW5ncyBvbiB0aGlzIGxpc3QgYWJvdXQgdXNpbmcgTERBUCBmb3Ig
DQphdXRoZW50aWNhdGlvbi4NCg0KSSBxdW90ZSBvbmUgb2YgdGhlbToNCg0KICAgIFBvc3Rn
cmVTUUwgc3VwcG9ydHMgR1NTQVBJIC8gS2VyYmVyb3MsIHdoaWNoIGlzIGEgbXVjaCBiZXR0
ZXINCiAgICBhcHByb2FjaCBpbiBBY3RpdmUgRGlyZWN0b3J5IGFuZCBvdGhlciBlbnZpcm9u
bWVudHMgd2hlcmUgS2VyYmVyb3MNCiAgICBpcyBkZXBsb3llZC4NCg0KICAgIFVzaW5nIExE
QVAgZXhwb3NlcyB0aGUgdXNlcidzIHBhc3N3b3JkIHRvIHRoZSBkYXRhYmFzZSBzZXJ2ZXIg
YW5kDQogICAgdGhlcmVmb3JlIGlzbid0IHNlY3VyZSBhbmQgc2hvdWxkIGJlIHN0cm9uZ2x5
IGRpc2NvdXJhZ2VkLg0KDQogICAgWW91IGNhbiBjZXJ0YWlubHkgaGF2ZSBhcHBsaWNhdGlv
bnMgYXV0aGVudGljYXRlIHRvIFBvc3RncmVTUUwgdXNpbmcNCiAgICBLZXJiZXJvcyB0b28g
d2l0aCB2ZXJ5IGxpdHRsZSBvdmVyaGVhZCAodW5saWtlIFBHJ3MgTERBUA0KICAgIGF1dGhl
bnRpY2F0aW9uIG9wdGlvbiwgd2hpY2ggbWFrZXMgYSBzeW5jaHJvbm91cyBjYWxsIG91dCB0
byB0aGUNCiAgICBMREFQIHNlcnZlciBvbiBlYWNoIGFuZCBldmVyeSBsb2dpbikuDQoNCklm
IHlvdSBpbnNpc3Qgb24gdXNpbmcgTERBUCwgeW91IG1pZ2h0IHVzZSB0aGUgcmVnZXhwIGZl
YXR1cmUgdG8gZXh0cmFjdCANCnRoZSB1c2VybmFtZSBmcm9tIHRoZSBjb21wbGV0ZSBtYWls
IGFkZHJlc3MuIE9ubHkgaWYgdGhlIHNhbWUgdXNlciBuYW1lIA0Kd2l0aCBkaWZmZXJlbnQg
c2VydmVyIHBhcnRzIChhZnRlciB0aGUgQCkgZXhpc3QsIHlvdSdsbCBoYXZlIHRvIGNoYW5n
ZSANCnlvdXIgdXNlciBuYW1lcy4NCg0KUXVvdGVkIHVzZXIgbmFtZXMgYXJlIHZlcnkgbXVj
aCB1bmxvdmVkLg0KDQpSZWdhcmRzLA0KDQpIb2xncg0KDQo+IC0tIA0KPiBIb2xnZXIgSmFr
b2JzLCBQb3N0Z3JlU1FMIEV4cGVydCBhbmQgVHJhaW5lciwgQmVyZ2lzY2ggR2xhZGJhY2gs
IFRlbC4gKzQ5LTE3OC05NzU5MDEyDQo=
--------------d4Xoeu9T6VcaRRIxTQVVq7ic
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 14.11.23 um 13:56 schrieb Bernd
      Lentes:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04=
=2Eprod.outlook.com">
      <pre class=3D"moz-signature" cols=3D"72">Hi,

the last time i wrote a SQL command is about 20 years ago.
So I'm asking for your advice.
I have a DB which is accessed by ~ 40 users.
The users are in pg_user and authenticated with LDAP.
</pre>
    </blockquote>
    <p>There have been several warnings on this list about using LDAP
      for authentication.</p>
    <p>I quote one of them:</p>
    <blockquote>
      <p>PostgreSQL supports GSSAPI / Kerberos, which is a much better
        approach in Active Directory and other environments where
        Kerberos is deployed.<br>
      </p>
      <p>Using LDAP exposes the user's password to the database server
        and therefore isn't secure and should be strongly discouraged.<br=
>
      </p>
      <p>You can certainly have applications authenticate to PostgreSQL
        using Kerberos too with very little overhead (unlike PG's LDAP
        authentication option, which makes a synchronous call out to the
        LDAP server on each and every login).<br>
      </p>
    </blockquote>
    <p>If you insist on using LDAP, you might use the regexp feature to
      extract the username from the complete mail address. Only if the
      same user name with different server parts (after the @) exist,
      you'll have to change your user names.</p>
    <p>Quoted user names are very much unloved.</p>
    <p>Regards,</p>
    <p>Holgr<br>
    </p>
    <blockquote type=3D"cite"
cite=3D"mid:PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04=
=2Eprod.outlook.com">
      <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, PostgreSQL Expert and Trainer, Bergisch Gladbach, Tel. +49=
-178-9759012</pre>
    </blockquote>
  </body>
</html>

--------------d4Xoeu9T6VcaRRIxTQVVq7ic--

--------------MavruxBtEy9vSI06I0JwCltF--

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

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

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZVOJiQUDAAAAAAAKCRCKV5yTsxlhuq+I
AJ49rUcVOhiAXQhV6Cs3gV/eXErSvACgni77DJgISLykEQfm54TxVrx1O34=
=XMh9
-----END PGP SIGNATURE-----

--------------rBitfMMYDVOku66jEN1BmxxX--