Re: Password identifiers, protocol aging and SCRAM protocol

On 19 January 2017 at 06:32, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Wed, Jan 18, 2017 at 02:30:38PM +0900, Michael Paquier wrote:
>> On Wed, Jan 18, 2017 at 2:23 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>> > The latest versions document this precisely, but I agree with Peter's concern
>> > about plain "scram". Suppose it's 2025 and PostgreSQL support SASL mechanisms
>> > OAUTHBEARER, SCRAM-SHA-256, SCRAM-SHA-256-PLUS, and SCRAM-SHA3-512. What
>> > should the pg_hba.conf options look like at that time? I don't think having a
>> > single "scram" option fits in such a world.
>>
>> Sure.
>>
>> > I see two strategies that fit:
>> >
>> > 1. Single "sasl" option, with a GUC, similar to ssl_ciphers, controlling the
>> > mechanisms to offer.
>> > 2. Separate options "scram_sha_256", "scram_sha3_512", "oauthbearer", etc.
>>
>> Or we could have a sasl option, with a mandatory array of mechanisms
>> to define one or more items, so method entries in pg_hba.conf would
>> look llke that:
>> sasl mechanism=scram_sha_256,scram_sha3_512
>
> I like that.

Michael, I support your good work on this patch and its certainly shaping up.

Noah's general point is that we need to have a general, futureproof
design for the UI and I agree.

We seem to be caught between adding lots of new things as parameters
and adding new detail into pg_hba.conf.

Parameters like password_encryption are difficult here because they
essentially repeat what has already been said in the pg_hba.conf. If
we have two entries in pg_hba.conf, one saying md5 and the other
saying "scram" (or whatever), what would we set password_encryption
to? It seems clear to me that if the pg_hba.conf says md5 then
password_encryption should be md5 and if pg_hba.conf says scram then
it should be scram.

I'd like to float another idea, as a way of finding a way forwards
that will last over time

* pg_hba.conf entry would say sasl='methodX' (no spaces)
* we have a new catalog called pg_sasl that allows us to add new
methods, with appropriate function calls
* remove password_encryption parameter and always use default
encryption as specified for that session in pg_hba.conf

Which sounds nice, but many users will wish to upgrade their current
mechanisms from using md5 to scram. How will we update passwords
slowly, so that different users change from md5 to scram at different
times? Having to specify the mechanism in the pg_hba.conf makes that
almost impossible, forcing a big bang approach which subsequently may
never happen.

As a way of solving that problem, another idea would be to make the
mechanism session specific depending upon what is stored for a
particular user. That allows us to have a single pg_hba.conf entry of
"sasl", and then use md5, scram-256 or future-mechanism on a per user
basis.

I'm not sure I see a clear way forwards yet, these are just ideas and
questions to help the discussion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services