Date: Fri, 24 May 2019 10:00:02 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: "Jonathan S. Katz" <jkatz@postgresql.org>,
	Magnus Hagander <magnus@hagander.net>,
	Joe Conway <mail@joeconway.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Noah Misch <noah@leadboat.com>
Subject: Re: initdb recommendations
Message-ID: <20190524140002.GB2480@tamriel.snowman.net>
References: <20190524023009.GL2480@tamriel.snowman.net>
 <df011ad3-f1a3-149e-3489-2d68e6ea21a7@joeconway.com>
 <20190524121306.GS2480@tamriel.snowman.net>
 <17041a05-0e28-f2b1-bf0c-fe375af66b45@joeconway.com>
 <20190524121904.GU2480@tamriel.snowman.net>
 <CABUevExV2YKa8cFRmFzf=cGriqhoHqwd9ev=2hWdq1kieqaV6Q@mail.gmail.com>
 <20190524123317.GW2480@tamriel.snowman.net>
 <ded009db-b651-1d35-0ec6-282af16068a4@postgresql.org>
 <20190524130123.GY2480@tamriel.snowman.net>
 <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="leQ0ainWKcxhm+Q3"
Content-Disposition: inline
In-Reply-To: <1f55ea0f-a0fc-73cf-a7da-c6b8d8c752c2@iki.fi>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--leQ0ainWKcxhm+Q3
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Heikki Linnakangas (hlinnaka@iki.fi) wrote:
> On 24/05/2019 16:01, Stephen Frost wrote:
> >What I was really getting at though was the ability to have multiple
> >authenticator tokens active concurrently (eg: md5 AND SCRAM), with an
> >ability to use either one (idk, md5_or_scram auth method?), and then
> >automatically set both on password change until everything is using
> >SCRAM and then remove all MD5 stuff.
>=20
> Umm, that's what "md5" already does. Per documentation
> (https://www.postgresql.org/docs/current/auth-password.html):

I remembered that we did something here but hadn't gone and looked at
it recently, so sorry for misremembering.  Perhaps all the more reason
for detailed migration documentation.

> > To ease transition from the md5 method to the newer SCRAM method, if
> > md5 is specified as a method in pg_hba.conf but the user's password on
> > the server is encrypted for SCRAM (see below), then SCRAM-based
> > authentication will automatically be chosen instead.
>=20
> The migration path is:
>=20
> 1. Use "md5" in pg_hba.conf, and put password_encryption=3D'scram-sha-256=
' in
> postgresql.conf.
>=20
> 2. Wait until all users have reset their passwords, so that all users hav=
e a
> SCRAM-SHA-256 verifier.

Wait though- once a password is changed then they *have* to use SCRAM
for auth from that point on, right?  That's great if you can be sure
that everything you're connecting from supports it, but that isn't going
to necessairly be the case.  I think this is what I recall being unhappy
about and what I was trying to remember about what we did.

We also haven't got a way to tell very easily when a given md5 (or
scram, for that matter...) authenticator was last used, making it hard
to see if it's still actually being used or not.  Nor is there a very
nice way to see when all users have reset their passwords to scram
without inspecting the password hash itself...

> 3. Replace "md5" with "scram-sha-256" in pg_hba.conf.
>=20
> Step 3 is kind of optional; once all users have a SCRAM verifier instead =
of
> an MD5 hash, they will all use SCRAM even without changing pg_hba.conf. It
> just prevents MD5 authentication in case a user forces a new MD5 hash into
> the system e.g. by changing password_encryption, or by setting an MD5
> password explicitly with ALTER USER.

Yes, which you'd certainly want to do, so I don't consider it to be
optional.  Further, we should really have a way for an admin to say
"never allow storing an md5 password again" which I don't think we do.

Thanks,

Stephen

--leQ0ainWKcxhm+Q3
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJc5/jiAAoJEO1sijiDR2RVHKQP/121i+UsIVGVxFDbKzgJFlzY
1viiNzTfFaYgjaXgm7yqnQz+jlhxJfNwCEl2mxTpcYrB8Xj+F/KoUzSnqdCzIwW7
mqYkS8NO0KQ2annTAma7YbM0eNibCevDihBLEjBVk9quKg55tfh8eIHzhdUHqBDS
ynf2pk7t/rAaFCJqovR9xniroXuxAD5c7EtDivyCmJANar79Yva5dbpqo71DpbpN
U+nfjq09CG39WL7xOYVgSqOH6TfJlHGNUoanwEY3Jvs+QLQw1LzNB433gluER/h+
+ZxXoiWN0A5EIrCUUA9Cx84r/vcleMO/veesXwPN8WguCf/zU9uzBKjm9Zqlup5F
gbHdeXdGdxIKhgXkOB7cuuZ8iVkJv3D8ggIM4sBQTBBFzSYxByl/MJsZwlhfzpCz
HIzu6ZfYPPBQWXgE8dGOOLUqgQGrEh7m0eEUvkAnInDKWdJeZCaE7H1isYj+2iGX
+Xj287HtFpD0L0VHAhv+NIYu4ig9/m7aZ3DhEzntspapbai1YJv1RDcUSjKucg5n
ZtKI5mDREJxY3Vnlcm6gqCE4pIqcLFGIjT6wCBCIVyUkTnEbUkR4njP3n2LJ/0GY
umv58VYJ5cCpvSyZ2FG0bl1Oy7jhKGuBln7dxikihVs23wrqx+JGsiHI7DozVnzi
JHC+EcaqTBGyrmvqTKYz
=CXuD
-----END PGP SIGNATURE-----

--leQ0ainWKcxhm+Q3--