Date: Fri, 23 Nov 2018 16:44:23 -0500
From: Stephen Frost <sfrost@snowman.net>
To: "Lentes, Bernd" <bernd.lentes@helmholtz-muenchen.de>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: User Authentication: LDAP and "local" accounts concurrently ?
Message-ID: <20181123214423.GK3415@tamriel.snowman.net>
References: 
 <1819852155.20922807.1542983802608.JavaMail.zimbra@helmholtz-muenchen.de>
 <20181123151725.GH3415@tamriel.snowman.net>
 <448603474.21010176.1542999433914.JavaMail.zimbra@helmholtz-muenchen.de>
 <20181123191413.GJ3415@tamriel.snowman.net>
 <33999F41-3B4A-46D3-BB18-531CF8662C0E@helmholtz-muenchen.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="cEOOY/qhMBY6Rcxu"
Content-Disposition: inline
In-Reply-To: <33999F41-3B4A-46D3-BB18-531CF8662C0E@helmholtz-muenchen.de>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--cEOOY/qhMBY6Rcxu
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote:
> > Am 23.11.2018 um 20:14 schrieb Stephen Frost <sfrost@snowman.net>:
> > With LDAP, the user's password will be seen by the PostgreSQL server,
> > and sent over the wire in cleartext unless you're making sure to use TLS
> > on the connection to PG (and if you're doing that you really want to
> > make sure you have verify-full enabled on your clients....).
> >=20
> > With Kerberos/GSSAPI, the authentication tokens are encrypted by the KDC
> > (in your case, the AD domain controllers) and the user's password is
> > never exposed.
>=20
> I=E2=80=98m Not sure wether my Clients speak TLS. I=E2=80=98m afraid they=
 don=E2=80=98t.=20

That's certainly another reason to avoid using LDAP or any of the
password-based authentication methods except maybe SCRAM.

> But isn=E2=80=98t then also the password transmitted in cleartext ? It mu=
st be transmitted from the client to the Pg Server, independent of using LD=
AP or Kerberos/GSSAPU.

No, Kerberos/GSSAPI *never* transmits the user's password to the server.
The user's password is actually used as an encryption key and is known
only to the KDC (your domain controllers) and the user.  The KDC and the
PG server then share a different encryption key (the service principal).
When the user wants to connect to PG they ask the KDC for a ticket which
the KDC returns to the user as a blob which contains some information
for the PG server encrypted with the PG server's key and then encrypts
that and sends it to the user, who then decrypts it and uses it to
connect to the PG server.

How all of that works is a bit complicated but thankfully you don't
really need to worry about that- Windows and Active Directory handle
almost all of it.  All you need to do is create a service principal in
active directory for the PG server and then export it and copy it over
to the PG server and then enable gssapi in PG.

Thanks!

Stephen

--cEOOY/qhMBY6Rcxu
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJb+HS3AAoJEO1sijiDR2RV28IP/2EmRE/UsCGfTSUEaZRApkEx
Sic7MHmSXni7zhaQMATa16dBjVSm+UUpX8wJaAHkYLO2yGcJr1KiVsx3xMkoNG+i
/OENISTC6eONu6rdcdFH6dxI6M0KQuoDtdKwtnW9VMxZ28rR4SJF55gi0wo8f6CE
rJ6ZK2OVN5F9TVdT1W0ok39eVSC0ysA0wRUsUms+1BsPvfR0+WQMTCQI8IxKxmO0
zDwVvh/KbccLbSje/79SadMQexmWk8Rd2FweEFuRQ3MJT5wqMJBOKk+QZgwKXtf4
uxYYl+/dsZPYj75ZF+wq+cuZIRIXD3aznEICN3NVUUd0/6NIVGz3z5kXUYK5U/Ln
oyIwWiZI2YWWnZdKySv7p/rkj1JAdtepNPywh8h5BwzSrRqV4DpD4g2S4M4LA9T8
jIDsfiLRNeEqZo7GTuByskTmmDLYbQVI8HRWEp5if0e6KuFq3P9RKIaLTEKqnPHo
agSj5P9Penxxn2hoPR5ZPqa/cFZxmafa8/kkq9PBj2NUGey0Yo3jVpGn6sZfCX9J
48YHEbz4z4LVdXuo50SY8LaDskEWq71ts3odTraVf2VkpdD8dehA1wNXN7ZS9xVv
eyWejAiYHZxX+V0AwWM2mZCx8gqjw7GQOVFSfM7+m93TCIjz6crWaw4zzwCkgAn8
pgyxXpin6ZyD5ClAH2el
=2fHH
-----END PGP SIGNATURE-----

--cEOOY/qhMBY6Rcxu--