Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYWeB-00GQ6B-OJ for pgsql-admin@arkaria.postgresql.org; Fri, 09 Feb 2024 19:34:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rYWeA-00BZHP-IW for pgsql-admin@arkaria.postgresql.org; Fri, 09 Feb 2024 19:34:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYWeA-00BZHG-5v for pgsql-admin@lists.postgresql.org; Fri, 09 Feb 2024 19:34:50 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYWe5-006M7d-3z for pgsql-admin@lists.postgresql.org; Fri, 09 Feb 2024 19:34:49 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id B49DF380C96; Fri, 9 Feb 2024 20:34:43 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id iacYnNkKb0iR; Fri, 9 Feb 2024 20:34:43 +0100 (CET) Received: from [192.168.178.86] (xdsl-89-1-60-134.nc.de [89.1.60.134]) by rs.plausibolo.de (Postfix) with ESMTPSA id 5040E38031A; Fri, 9 Feb 2024 20:34:43 +0100 (CET) Message-ID: <954de75f-5e5a-e5a9-0c64-3e0b030f3957@jakobs.com> Date: Fri, 9 Feb 2024 20:34:42 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: Use AD-account as login into Postgres. Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: <09c4b7dc-901d-135e-087b-808c489e0d81@jakobs.com> From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------QT7QcU3QSw00cRVedSIae0y0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------QT7QcU3QSw00cRVedSIae0y0 Content-Type: multipart/mixed; boundary="------------APOEcibPAjbsY0WafT0LriGJ"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <954de75f-5e5a-e5a9-0c64-3e0b030f3957@jakobs.com> Subject: Re: Use AD-account as login into Postgres. References: <09c4b7dc-901d-135e-087b-808c489e0d81@jakobs.com> In-Reply-To: --------------APOEcibPAjbsY0WafT0LriGJ Content-Type: multipart/alternative; boundary="------------3sGpVqn5GwiDxaagJqztZsZI" --------------3sGpVqn5GwiDxaagJqztZsZI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 DQpBbSAwOS4wMi4yNCB1bSAyMDozMSBzY2hyaWViIFDDpHIgTWF0dHNzb246DQo+IFllcyB0 aGlzIGlzIGEgY29tcGxldGUgd2luZG93cyBpbnN0YWxsYXRpb24gb2YgUG9zdGdyZXMgYW5k IHRoZXkgd2lsbCANCj4gdXNlIGFkLWxvZ2luIGFjY291bnQgaW50byB0aGUgZGF0YWJhc2UN Cj4NCj4gTXZoIFDDpHINCj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQo+ICpGcsOlbjoqIEhvbGdl ciBKYWtvYnMgPGhvbGdlckBqYWtvYnMuY29tPg0KPiAqU2tpY2thdDoqIGZyZWRhZywgZmVi cnVhcmkgOSwgMjAyNCAyMDowNQ0KPiAqVGlsbDoqIHBnc3FsLWFkbWluQGxpc3RzLnBvc3Rn cmVzcWwub3JnIA0KPiA8cGdzcWwtYWRtaW5AbGlzdHMucG9zdGdyZXNxbC5vcmc+DQo+ICrD hG1uZToqIFJlOiBVc2UgQUQtYWNjb3VudCBhcyBsb2dpbiBpbnRvIFBvc3RncmVzLg0KPiBB bSAwOS4wMi4yNCB1bSAxOTozMSBzY2hyaWViIFDDpHIgTWF0dHNzb246DQo+PiBIaSENCj4+ IElzIGl0IG9ubHkgdG8gY29uZmlnIGluIGhiYS5jb25mIHRoZSBjb25uZWN0aW9uIGluZm8s IMKgdG8gdXNlIA0KPj4gQUQtYWNjb3VudHMgdG8gbG9naW4gaW4gcG9zdGdyZXMuDQo+PiBU aGlzIGlzIGEgd2luZG93cy9wb3N0cmVzIGludGFsbGF0aW9uIPCfpKbigI3imYLvuI/inIzv uI8NCj4+DQo+PiBNdmggUMOkcg0KPj4gKzQ2NzA2MDY5NjQ1DQo+DQo+IEhpLA0KPg0KPiBT aG9ydCBhbnN3ZXI6IE5vIQ0KPg0KPiBTU1BJIHVzaW5nIEFEIGFjY291bnRzIGZvciBhdXRo ZW50aWNhdGlvbiB3b3JrcyBvbmx5IGluIGEgY29tcGxldGUgDQo+IFdpbmRvd3MgZW52aXJv bm1lbnQuIFRoZSBjbGllbnQgYW5kIHRoZSBzZXJ2ZXIgbWFjaGluZSBoYXZlIHRvIGJlIA0K PiBtZW1iZXIgb2YgdGhlIHNhbWUgQUQgZW52aXJvbm1lbnQsIHdoaWNoIGlzbid0IHBvc3Np YmxlIGZvciANCj4gbm9uLVdpbmRvd3MgbWFjaGluZXMuIE90aGVyd2lzZSwgdGhlcmUgaXMg bm8gdHJ1c3QgYmV0d2VlbiB0aGUgbWFjaGluZXMuDQo+DQo+IEFuIGF1dG9tYXRpYyBjcmVh dGlvbiBvZiBQb3N0Z3JlU1FMIHJvbGVzIGZyb20gQUQgYWNjb3VudHMgaGFzIHRvIGJlIA0K PiBkb25lIG91dHNpZGUgUG9zdGdyZVNRTCwgaS4gZS4gYnkgYSBzY3JpcHQgcnVubmluZyBy ZWd1bGFybHkuDQo+DQo+IEEgY291cGxlIG9mIHllYXJzIGFnbywgSSB3cm90ZSBzdWNoIGEg c2NyaXB0IGZvciBhIGN1c3RvbWVyLg0KPg0KPiBSZWdhcmRzLA0KPg0KPiBIb2xnZXINCj4N CklmIHRoYXQncyB0aGUgY2FzZSwgY3JlYXRlIGFsbCB0aGUgbmVjZXNzYXJ5IHJvbGVzIChn cm91cHMsIHVzZXJzKSBpbiANClBvc3RncmVTUUwgbWF0Y2hpbmcgZW50cmllcyBpbiBwZ19o YmEuY29uZiBhbmQgbWFwcGluZyBlbnRyaWVzIGluIA0KcGdfaWRlbnQsIHNvIHRoYXQgV2lu ZG93cyB1c2VycyBjYW4gY29ubmVjdCB0byB0aGUgZGF0YWJhc2Ugd2l0aG91dCANCm5lZWRp bmcgdG8gYXV0aGVudGljYXRlIGFnYWluLg0KDQpJdCdzIGEgbmljZSB3YXkgb2YgcHJvdmlk aW5nIHNpbmdsZSBzaWduLW9uLg0KDQpSZWdhcmRzLA0KDQpIb2xnZXINCg0KDQo+IC0tIA0K PiBIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkw MTINCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDkt MTc4LTk3NTkwMTINCg0K --------------3sGpVqn5GwiDxaagJqztZsZI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


Am 09.02.24 um 20:31 schrieb P=C3=A4r Mattsson:
Yes this is a complete windows installation of= Postgres and they will use ad-login account into the database=C2=A0

Mvh P=C3=A4r
=C2=A0
Fr=C3=A5n: Holger Jakobs <holger@jakobs.com>
Skickat: fredag, februari 9, 2024 20:05
Till: pgsql-admin@lists.postgresql.org= <pgsql-admin@lists.postgresql.org>
= =C3=84mne: Re: Use AD-account as login into Postgres= =2E
=C2=A0
Am 09.02.24 um 19:31 schrieb P=C3= =A4r Mattsson:
Hi!
Is it only to config in hba.conf the connection info, =C2=A0to use AD-accounts to login in postgres.
This is a windows/postres intallation =F0=9F=A4=A6=E2=80=8D=E2=99=82=EF=B8=8F=E2=9C=8C=EF=B8=8F=

Mvh P=C3=A4r
+46706069645

Hi,

Short answer: No!

SSPI using AD accounts for authentication works only in a complete Windows environment. The client and the server machine have to be member of the same AD environment, which isn't possible for non-Windows machines. Otherwise, there is no trust between the machines.

An automatic creation of PostgreSQL roles from AD accounts has to be done outside PostgreSQL, i. e. by a script running regularly.

A couple of years ago, I wrote such a script for a customer.

Regards,

Holger

If that's the case, create all the necessary roles (groups, users) in PostgreSQL matching entries in pg_hba.conf and mapping entries in pg_ident, so that Windows users can connect to the database without needing to authenticate again.

It's a nice way of providing single sign-on.

Regards,

Holger


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------3sGpVqn5GwiDxaagJqztZsZI-- --------------APOEcibPAjbsY0WafT0LriGJ-- --------------QT7QcU3QSw00cRVedSIae0y0 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZcZ+UgUDAAAAAAAKCRCKV5yTsxlhunCV AKCOWXM2VI5E68G7wJkdgRuem3gcTACdEPO2nrR0czIks1PDPFI3cYUBlL4= =6btR -----END PGP SIGNATURE----- --------------QT7QcU3QSw00cRVedSIae0y0--