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 1qZWZW-005j0r-44 for pgsql-general@arkaria.postgresql.org; Fri, 25 Aug 2023 13:09:54 +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 1qZWZU-0035fY-II for pgsql-general@arkaria.postgresql.org; Fri, 25 Aug 2023 13:09:52 +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 1qZWZT-0035fK-QR for pgsql-general@lists.postgresql.org; Fri, 25 Aug 2023 13:09:51 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qZWZL-000ybV-7o for pgsql-general@lists.postgresql.org; Fri, 25 Aug 2023 13:09:50 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id D16FE5F7B7; Fri, 25 Aug 2023 09:09:35 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1692968975; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=izdKegxdtcRakvDRl5mynLAiaU4HR575cRpbNnQo1FM=; b=ZZqYQm98LPiTZkkJRTEISNOausTsjW7HnOfJ03oLGDxWalp5/YkuiIXS/h7JzMml5fOM0/ nL9K4uM9vfdoULFPr/fbLoxfQpMWo7gZXZgroJcTTXF5OB3RTJAXkhfwsuNGNRmqECOmkC zGtEcmrzry2Vg8bp3hZwVMw1NZhNero= Date: Fri, 25 Aug 2023 09:09:35 -0400 From: Stephen Frost To: Dominique Devienne Cc: Emile Amewoto , Roger Tannous , pgsql-general@lists.postgresql.org Subject: Re: LDAP Authentication Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="IH93Wj/aF3ft+E6F" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --IH93Wj/aF3ft+E6F Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Dominique Devienne (ddevienne@gmail.com) wrote: > On Thu, Aug 24, 2023 at 10:07=E2=80=AFPM Stephen Frost wrote: > > * Emile Amewoto (emileam@yahoo.com) wrote: > > > Here is the high level process: > > > 1- Create the user x without password in Postgres. > > > 2- Assign role or roles to the user x > > > 3- Update pg_hba.conf with the ldap connection link. > > > > > > You might need cert for the ldap to connect to AD, assuming you are > > using AD. > > > > If you're using AD, you should *really* be using Kerberos/gssapi for > > your authentication and *not* LDAP. LDAP is insecure as it involves > > passing around the user's credentials which is extremely bad practice > > and is strongly discouraged. LDAP auth also involves in-line round > > trips to the LDAP server which can delay or even fail database > > connections in the event that the LDAP server is even temporarily > > unavailable. > But as part of a team that wants to hook PostgreSQL to the company's > Windows AD, could you please > provide more info on how to configure the alternative you suggest should = be > used instead? https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi= -kerberos-authentication > Are you saying AD already "speaks" Kerberos/GSSAPI, and instead of > configuring LDAP in hba.conf, > one should configure GSSAPI instead? As "simple" as that? (just fyi- while they're not exactly the same, where I say 'Kerberos' below, you can think of it as GSSAPI, Kerberos, or Kerberos/GSSAPI) More-or-less. Windows AD uses Kerberos/GSSAPI already for tons of authentication- when you log into a AD connected system, that's Kerberos, when you connect to an IIS site in an AD environment and automatically get authenticated, that's Kerberos (and Negotiate to wrap it, but still), when you connect to SQL Server, again, Kerberos. The "pass-the-password" LDAP-based auth method is just about exclusively used for 3rd-party non-Kerberos-speaking services. When you have the option to use Kerberos, you absolutely should. > What about SSO? Can the local creds / token from the already-AD-connected > local OS user be extracted, > so the user doesn't need to supply any password, not even the AD-one? Kerberos *is* the true SSO that you're looking for- when you sign into any AD connected system (eg: your laptop), you get Kerberos credentials (run 'klist' at a cmd prompt sometime..) and you don't have to provide your password again for any Kerberos authentication until/unless your Kerberos tickets (acquired when you logged into the laptop) expire. With the LDAP auth method, the user has to provide their AD password on every connection to authenticate, or worse, save their AD password for connection in some local file so they don't have to type it in every time. That password is then also sent to the PG server on every connection, meaning that if the PG server is compromised, the account of every user who logs into that PG server is then also compromised- their full AD account. > Regarding your second point about availability of the LDAP server, isn't > that normal to fail connecting > when Auth cannot be ascertained / verified? Kerberos/GSSAPI somehow main > some cache to avoid that? Kerberos tickets are acquired from the AD when you log into your laptop. Those tickets will have some lifetime (which you can configure, but tends to be 8-10 hours or so). When you connect to a Kerberos service, the system will automatically reach out to the AD to obtain a ticket for that service (eg, a PostgreSQL ticket, for your account) which will also have a specific lifetime. When you connect to PostgreSQL, the PG server is able to verify your identity based on the ticket you present- the PG server never has to reach out to the AD system for user authentication. For as long as the PostgreSQL ticket that you acquired when you first connected to the PG server is valid (again, typically 8-10 hours, but you can configure it), the AD server is no longer involved and doesn't need to be contacted, no matter how many subsequent connections you make to the PG server. Note that Kerberos is all about authentication- not about authorization. There's a few different tools out there for sync'ing users from AD into PostgreSQL and those can be used to manage who is authorized for a given PG server, including removing their authorization in a relatively short period of time if needed (more-or-less how often you feel like running the sync'ing cronjob or such). I do think it'd be pretty cool to improve on that with a way for PG to reach out to an AD system and run a query and then track the changes to that query (LDAP has support for this kind of thing) which would make changes to, eg, group membership in AD propagate to the PG server in pretty close to real-time. Sadly, I'm not aware of anyone actively working on that though. > Given that caching is often more trouble than it's worth, how is that > better? Na=C3=AFve question, really. --DD This one I find pretty curious given that cacheing is a very large part of what PostgreSQL does and is certainly quite worth it. ;) Thanks, Stephen --IH93Wj/aF3ft+E6F Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmToqAsACgkQ7WyKOINH ZFWTaxAAmRguwD3+xqkT/hDyEK3L4GwROsNJ/9fs/45ffEaTdrzK7Xss8l/EHDuh MiLr2SuXMfvvSOahaVABTlNXs5ff/Qrx7evbnpzAFIlkc40xBhK7V+AyUpBe+5FD 77M9QkbqbyabDoVQiBpvlQNh075zWjivMCi2x1k6rbZ1FljSk+5iY/L6M0gWogED T95AWAfdznvXGlJ1LDVkKSNIjN6fdXr+Owo921j9eqdhwQIsA2bsczYFwPRChpnw 6DFaEqH8k4MAkHtm4K/f4Hzgwwmyn/JTj+047BiebwEUhiMPjtRmJN4/eUw2p65o Rn5sWQ03FGttXEvsr/aSjUOJjP28vAHxdnKoxOm3JLcNpMv6SwjNynclkfvSizsb 8j9W4WWZ/yQWnr/Aveln9dSfFM0Y4YQIn8lZopQbKtx7xg2Jc5D031z4C42REjHw ZdPZTHm9mGk7qknzwwfYtAaCC1G4+1Ab1NKce8wh4TCE6F48/giH7ZfC+QLgam1e xVDYBn0jvyF3rVifAKt/TRdPlT8ixcqxGpOXGuVcCWOGKgj0oruz7Mbo/86z8Aei WX2Vu2AYimXpd2QywgB+1z5Sx9iLwAVeqreURB/dzVHoq6mB3FtzhJtOEeyzg5qs FiO54vZQSuSCObC4fAhHqu8X1zsM6y4LfiLxzpYFgYKEYsqZmv4= =LrHq -----END PGP SIGNATURE----- --IH93Wj/aF3ft+E6F--