Date: Thu, 15 Dec 2016 08:40:30 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	Michael Paquier <michael.paquier@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Andres Freund <andres@anarazel.de>,
	David Steele <david@pgmasters.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	David Fetter <david@fetter.org>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Magnus Hagander <magnus@hagander.net>,
	Julian Markwort <julian.markwort@uni-muenster.de>,
	PostgreSQL mailing lists <pgsql-hackers@postgresql.org>,
	Valery Popov <v.popov@postgrespro.ru>
Subject: Re: pg_authid.rolpassword format (was Re: Password
 identifiers, protocol aging and SCRAM protocol)
Message-ID: <20161215134030.GI23417@tamriel.snowman.net>
References: <21eecdb8-365b-8347-1ae2-5278d1fe798c@iki.fi>
 <CAB7nPqT2UGWm0ukdomC+mG-co6nmKer62vuSGRxwtc+jS8uQPA@mail.gmail.com>
 <CAB7nPqQtD7jOpYCsB4Ga=Cz+tgh4Ngac6NAV-WLs9zxaw0Uu7w@mail.gmail.com>
 <762f4875-a87e-cf0b-c06b-b357294d8be1@iki.fi>
 <CAB7nPqTi5jC_AbVMOGidrVy1z6t3v==vWFvdNMxnQEN-WsutKg@mail.gmail.com>
 <2d07165c-1793-e243-a2a9-e45b624c7580@iki.fi>
 <CAB7nPqQ28o6ocOdbidOVmxxcKDMP=woZ=hvAVHKKAjAUtjZNyQ@mail.gmail.com>
 <ce051da1-cc5a-2a06-a9a8-6a4d0d98bdd1@2ndquadrant.com>
 <20161214145723.GJ23417@tamriel.snowman.net>
 <9dc32483-b24a-5832-53e0-fb62838f1315@iki.fi>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="G3Z/XoW3/0g+zQCP"
Content-Disposition: inline
In-Reply-To: <9dc32483-b24a-5832-53e0-fb62838f1315@iki.fi>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--G3Z/XoW3/0g+zQCP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Heikki Linnakangas (hlinnaka@iki.fi) wrote:
> On 12/14/2016 04:57 PM, Stephen Frost wrote:
> >* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> >>On 12/14/16 5:15 AM, Michael Paquier wrote:
> >>>I would be tempted to suggest adding the verifier type as a new column
> >>>of pg_authid
> >>
> >>Yes please.
> >
> >This discussion seems to continue to come up and I don't entirely
> >understand why we keep trying to shove more things into pg_authid, or
> >worse, into rolpassword.
>=20
> I understand the relational beauty of having a separate column for
> the verifier type, but I don't think it would be practical.

I disagree.

> For
> starters, we'd still like to have a self-identifying string format
> like "scram-sha-256:<stuff>", so that you can conveniently pass the
> verifier as a string to CREATE USER.

I don't follow why we can't change the syntax for CREATE USER to allow
specifying the verifier type independently.  Generally speaking, I don't
expect *users* to be providing actual encoded *verifiers* very often, so
it seems like a bit of extra syntax that pg_dump has to use isn't that
big of a deal.

> I think it'll be much better to
> stick to one format, than try to split the verifier into type and
> the string, when it enters the catalog table.

Apparently, multiple people disagree with this approach.  I don't think
history is really on your side here either.

> >We should have an independent table for the verifiers, which has a
> >different column for the verifier type, and either starts off supporting
> >multiple verifiers per role or at least gives us the ability to add that
> >easily later.  We should also move rolvaliduntil to that new table.
>=20
> I agree we'll probably need a new table for verifiers. Or turn
> rolpassword into an array or something. We discussed that before,
> however, and it didn't really go anywhere, so right now I'd like to
> get SCRAM in with minimal changes to the rest of the system. There
> is a lot of room for improvement once it's in.

Using an array strikes me as an absolutely terrible idea- how are you
going to handle having different valid_until times then?

I do agree with trying to get SCRAM in without changing too much of the
rest of the system, but I wanted to make it clear that it's the only
point that I agree with for continuing down this path and that we should
absolutely be looking to change the CREATE USER syntax to specify the
verifier independently, plan to use a different table for the verifiers
with an independent column for the verifier type, support multiple
verifiers per role, etc, in the (hopefully very near...) future.

Thanks!

Stephen

--G3Z/XoW3/0g+zQCP
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJYUp1OAAoJEO1sijiDR2RVzr0P/iZ/IRynygNdIWGRr2r45dFf
ShE7vkjp1tV3rGn+A0liI85x+Sh0LKvGIZCgyyfxU1LTjl0+nPAKkDhYtSDUiDHZ
t0CjouUcohVNo0ZCAuLUEu+XRxghr6fIgAtbYAJA6maB1/MxLuOuNGJ9kuUSGjM5
xeiCI8mTcLrn+BlHuksWOOzZ0pYmt5aQB/w4m9xGZKEih+6EB/DiiY8KlEOK76mH
QaYZi5idGbuaZ9nLaEnKwIXGB62q6jaVcJGN95PH3JYxw7ZvQLDtj6gxbR6tnhWK
jTIhOIWnIgZHdDCqe3O/T+V/jTiyGA+h7GSwLbo8kzpPXX53r1WOyRokzqBoehCq
7KDC1ZBV2JbBBg62t3oeYpAgsMK1FPkuBaPrVfcJFkgqQNbOHILszMEkdosvAFP3
1XX3W1ZA1rNV5BXrF9O3TsDWsNMFJOZ0NSovKn5hrdlV+aTEvkR3mBEDYsoOMMQQ
kdLSWWmjGjjf8+M9rQpHmElWFKpOKq+uv5zBVn9ng4rIHOC+JvFenVtlDyuFjIdr
+JzPVE7a5QH/Dp1uwBY++uEo2MuZrVEX9Bpu8/FdWKS63LMh/7WK5h4C8DWycgp2
4tfFI7tNGUhxuNacbBSXoRJLEDaamg/NvFDci0dNDnIVv59TAZJoqfHPQLU34Ja7
cP00H+IL4bouMQhSLaOq
=U/9y
-----END PGP SIGNATURE-----

--G3Z/XoW3/0g+zQCP--