Re: initdb recommendations

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
33 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Peter Eisentraut-6
On 2019-04-06 20:08, Noah Misch wrote:
>>> I think we should just change the defaults.  There is a risk of warning
>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>> gotten the information.
>>>
>>
>> I've been suggesting that for years, so definite strong +1 for doing that.
>
> +1

To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".

I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.

Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX.  I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing.  But what should the defaults be on
Windows?  It doesn't have local sockets, so the lack of peer wouldn't
matter.  But is it OK to default to a password method, or would that
upset people particularly?

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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Magnus Hagander-2


On Thu, May 23, 2019, 18:54 Peter Eisentraut <[hidden email]> wrote:
On 2019-04-06 20:08, Noah Misch wrote:
>>> I think we should just change the defaults.  There is a risk of warning
>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>> gotten the information.
>>>
>>
>> I've been suggesting that for years, so definite strong +1 for doing that.
>
> +1

To recap, the idea here was to change the default authentication methods
that initdb sets up, in place of "trust".

I think the ideal scenario would be to use "peer" for local and some
appropriate password method (being discussed elsewhere) for host.

Looking through the buildfarm, I gather that the only platforms that
don't support peer are Windows, AIX, and HP-UX.  I think we can probably
figure out some fallback or alternative default for the latter two
platforms without anyone noticing.  But what should the defaults be on
Windows?  It doesn't have local sockets, so the lack of peer wouldn't
matter.  But is it OK to default to a password method, or would that
upset people particularly?


I'm sure password would be fine there. It's what "everybody else" does (well sqlserver also cord integrated security, but people are used to it). 

/Magnus 

Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
In reply to this post by Peter Eisentraut-6
On 5/23/19 12:54 PM, Peter Eisentraut wrote:

> On 2019-04-06 20:08, Noah Misch wrote:
>>>> I think we should just change the defaults.  There is a risk of warning
>>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>>> gotten the information.
>>>>
>>>
>>> I've been suggesting that for years, so definite strong +1 for doing that.
>>
>> +1
>
> To recap, the idea here was to change the default authentication methods
> that initdb sets up, in place of "trust".
>
> I think the ideal scenario would be to use "peer" for local and some
> appropriate password method (being discussed elsewhere) for host.
+1.

> Looking through the buildfarm, I gather that the only platforms that
> don't support peer are Windows, AIX, and HP-UX.  I think we can probably
> figure out some fallback or alternative default for the latter two
> platforms without anyone noticing.  But what should the defaults be on
> Windows?  It doesn't have local sockets, so the lack of peer wouldn't
> matter.  But is it OK to default to a password method, or would that
> upset people particularly?

+1 for password method. Definitely better than trust :)

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
On 5/23/19 6:47 PM, Jonathan S. Katz wrote:

> On 5/23/19 12:54 PM, Peter Eisentraut wrote:
>> On 2019-04-06 20:08, Noah Misch wrote:
>>>>> I think we should just change the defaults.  There is a risk of warning
>>>>> fatigue.  initdb does warn about this, so anyone who cared could have
>>>>> gotten the information.
>>>>>
>>>>
>>>> I've been suggesting that for years, so definite strong +1 for doing that.
>>>
>>> +1
>>
>> To recap, the idea here was to change the default authentication methods
>> that initdb sets up, in place of "trust".
>>
>> I think the ideal scenario would be to use "peer" for local and some
>> appropriate password method (being discussed elsewhere) for host.
>
> +1.
>
>> Looking through the buildfarm, I gather that the only platforms that
>> don't support peer are Windows, AIX, and HP-UX.  I think we can probably
>> figure out some fallback or alternative default for the latter two
>> platforms without anyone noticing.  But what should the defaults be on
>> Windows?  It doesn't have local sockets, so the lack of peer wouldn't
>> matter.  But is it OK to default to a password method, or would that
>> upset people particularly?
>
> +1 for password method. Definitely better than trust :)
Attached is v2 of the patch.

For now I have left in the password based method to be scram-sha-256 as
I am optimistic about the support across client drivers[1] (and FWIW I
have an implementation for crystal-pg ~60% done).

However, this probably means we would need to set the default password
encryption guc to "scram-sha-256" which we're not ready to do yet, so it
may be moot to leave it in.

So, thinking out loud about that, we should probably use "md5" and once
we decide to make the encryption method "scram-sha-256" by default, then
we update the recommendation?

Thanks,

Jonathan

0001-Add-a-warning-about-the-client-authentication-v2.patch (4K) Download Attachment
signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Tom Lane-2
"Jonathan S. Katz" <[hidden email]> writes:
> For now I have left in the password based method to be scram-sha-256 as
> I am optimistic about the support across client drivers[1] (and FWIW I
> have an implementation for crystal-pg ~60% done).

> However, this probably means we would need to set the default password
> encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> may be moot to leave it in.

> So, thinking out loud about that, we should probably use "md5" and once
> we decide to make the encryption method "scram-sha-256" by default, then
> we update the recommendation?

Meh.  If we're going to break things, let's break them.  Set it to
scram by default and let people who need to cope with old clients
change the default.  I'm tired of explaining that MD5 isn't actually
insecure in our usage ...

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Tom Lane ([hidden email]) wrote:

> "Jonathan S. Katz" <[hidden email]> writes:
> > For now I have left in the password based method to be scram-sha-256 as
> > I am optimistic about the support across client drivers[1] (and FWIW I
> > have an implementation for crystal-pg ~60% done).
>
> > However, this probably means we would need to set the default password
> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> > may be moot to leave it in.
>
> > So, thinking out loud about that, we should probably use "md5" and once
> > we decide to make the encryption method "scram-sha-256" by default, then
> > we update the recommendation?
>
> Meh.  If we're going to break things, let's break them.  Set it to
> scram by default and let people who need to cope with old clients
> change the default.  I'm tired of explaining that MD5 isn't actually
> insecure in our usage ...
+many.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Joe Conway
On 5/23/19 10:30 PM, Stephen Frost wrote:

> Greetings,
>
> * Tom Lane ([hidden email]) wrote:
>> "Jonathan S. Katz" <[hidden email]> writes:
>> > For now I have left in the password based method to be scram-sha-256 as
>> > I am optimistic about the support across client drivers[1] (and FWIW I
>> > have an implementation for crystal-pg ~60% done).
>>
>> > However, this probably means we would need to set the default password
>> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
>> > may be moot to leave it in.
>>
>> > So, thinking out loud about that, we should probably use "md5" and once
>> > we decide to make the encryption method "scram-sha-256" by default, then
>> > we update the recommendation?
>>
>> Meh.  If we're going to break things, let's break them.  Set it to
>> scram by default and let people who need to cope with old clients
>> change the default.  I'm tired of explaining that MD5 isn't actually
>> insecure in our usage ...
>
> +many.

many++

Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Dave Cramer-8


On Fri, 24 May 2019 at 07:48, Joe Conway <[hidden email]> wrote:
On 5/23/19 10:30 PM, Stephen Frost wrote:
> Greetings,
>
> * Tom Lane ([hidden email]) wrote:
>> "Jonathan S. Katz" <[hidden email]> writes:
>> > For now I have left in the password based method to be scram-sha-256 as
>> > I am optimistic about the support across client drivers[1] (and FWIW I
>> > have an implementation for crystal-pg ~60% done).
>>
>> > However, this probably means we would need to set the default password
>> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
>> > may be moot to leave it in.
>>
>> > So, thinking out loud about that, we should probably use "md5" and once
>> > we decide to make the encryption method "scram-sha-256" by default, then
>> > we update the recommendation?
>>
>> Meh.  If we're going to break things, let's break them.  Set it to
>> scram by default and let people who need to cope with old clients
>> change the default.  I'm tired of explaining that MD5 isn't actually
>> insecure in our usage ...
>
> +many.

many++

Are we doing this for pg12? In any case, I would think we better loudly
point out this change somewhere.


+many as well given the presumption that we are going to break existing behaviour 

Dave
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Peter Eisentraut-6
In reply to this post by Joe Conway
On 2019-05-24 13:48, Joe Conway wrote:
> Are we doing this for pg12?

no

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


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
In reply to this post by Joe Conway
Greetings,

* Joe Conway ([hidden email]) wrote:

> On 5/23/19 10:30 PM, Stephen Frost wrote:
> > * Tom Lane ([hidden email]) wrote:
> >> "Jonathan S. Katz" <[hidden email]> writes:
> >> > For now I have left in the password based method to be scram-sha-256 as
> >> > I am optimistic about the support across client drivers[1] (and FWIW I
> >> > have an implementation for crystal-pg ~60% done).
> >>
> >> > However, this probably means we would need to set the default password
> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> >> > may be moot to leave it in.
> >>
> >> > So, thinking out loud about that, we should probably use "md5" and once
> >> > we decide to make the encryption method "scram-sha-256" by default, then
> >> > we update the recommendation?
> >>
> >> Meh.  If we're going to break things, let's break them.  Set it to
> >> scram by default and let people who need to cope with old clients
> >> change the default.  I'm tired of explaining that MD5 isn't actually
> >> insecure in our usage ...
> >
> > +many.
>
> many++
>
> Are we doing this for pg12? In any case, I would think we better loudly
> point out this change somewhere.
Sure, we should point it out, but I don't know that it needs to be
screamed from the rooftops considering the packagers have already been
largely ignoring our defaults here anyway...

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Joe Conway
On 5/24/19 8:13 AM, Stephen Frost wrote:

> Greetings,
>
> * Joe Conway ([hidden email]) wrote:
>> On 5/23/19 10:30 PM, Stephen Frost wrote:
>> > * Tom Lane ([hidden email]) wrote:
>> >> "Jonathan S. Katz" <[hidden email]> writes:
>> >> > For now I have left in the password based method to be scram-sha-256 as
>> >> > I am optimistic about the support across client drivers[1] (and FWIW I
>> >> > have an implementation for crystal-pg ~60% done).
>> >>
>> >> > However, this probably means we would need to set the default password
>> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
>> >> > may be moot to leave it in.
>> >>
>> >> > So, thinking out loud about that, we should probably use "md5" and once
>> >> > we decide to make the encryption method "scram-sha-256" by default, then
>> >> > we update the recommendation?
>> >>
>> >> Meh.  If we're going to break things, let's break them.  Set it to
>> >> scram by default and let people who need to cope with old clients
>> >> change the default.  I'm tired of explaining that MD5 isn't actually
>> >> insecure in our usage ...
>> >
>> > +many.
>>
>> many++
>>
>> Are we doing this for pg12? In any case, I would think we better loudly
>> point out this change somewhere.
>
> Sure, we should point it out, but I don't know that it needs to be
> screamed from the rooftops considering the packagers have already been
> largely ignoring our defaults here anyway...

Yeah, I thought about that, but anyone not using those packages will be
in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
change, but I predict many related questions on the lists, and anything
we can do to mitigate that should be done.

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Joe Conway ([hidden email]) wrote:

> On 5/24/19 8:13 AM, Stephen Frost wrote:
> > * Joe Conway ([hidden email]) wrote:
> >> On 5/23/19 10:30 PM, Stephen Frost wrote:
> >> > * Tom Lane ([hidden email]) wrote:
> >> >> "Jonathan S. Katz" <[hidden email]> writes:
> >> >> > For now I have left in the password based method to be scram-sha-256 as
> >> >> > I am optimistic about the support across client drivers[1] (and FWIW I
> >> >> > have an implementation for crystal-pg ~60% done).
> >> >>
> >> >> > However, this probably means we would need to set the default password
> >> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> >> >> > may be moot to leave it in.
> >> >>
> >> >> > So, thinking out loud about that, we should probably use "md5" and once
> >> >> > we decide to make the encryption method "scram-sha-256" by default, then
> >> >> > we update the recommendation?
> >> >>
> >> >> Meh.  If we're going to break things, let's break them.  Set it to
> >> >> scram by default and let people who need to cope with old clients
> >> >> change the default.  I'm tired of explaining that MD5 isn't actually
> >> >> insecure in our usage ...
> >> >
> >> > +many.
> >>
> >> many++
> >>
> >> Are we doing this for pg12? In any case, I would think we better loudly
> >> point out this change somewhere.
> >
> > Sure, we should point it out, but I don't know that it needs to be
> > screamed from the rooftops considering the packagers have already been
> > largely ignoring our defaults here anyway...
>
> Yeah, I thought about that, but anyone not using those packages will be
> in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
> change, but I predict many related questions on the lists, and anything
> we can do to mitigate that should be done.
You think there's someone who builds from the source and just trusts
what we have put in for the defaults in pg_hba.conf..?

I've got a really hard time with that idea...

I'm all for making people aware of it, but I don't think it justifies
being the top item of the release notes or some such.  Frankly, anything
that starts with "If you build from source, then..." is already going to
be pretty low impact and therefore low on the list of things we need to
cover in the release notes, et al.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Magnus Hagander-2


On Fri, May 24, 2019 at 2:19 PM Stephen Frost <[hidden email]> wrote:
Greetings,

* Joe Conway ([hidden email]) wrote:
> On 5/24/19 8:13 AM, Stephen Frost wrote:
> > * Joe Conway ([hidden email]) wrote:
> >> On 5/23/19 10:30 PM, Stephen Frost wrote:
> >> > * Tom Lane ([hidden email]) wrote:
> >> >> "Jonathan S. Katz" <[hidden email]> writes:
> >> >> > For now I have left in the password based method to be scram-sha-256 as
> >> >> > I am optimistic about the support across client drivers[1] (and FWIW I
> >> >> > have an implementation for crystal-pg ~60% done).
> >> >>
> >> >> > However, this probably means we would need to set the default password
> >> >> > encryption guc to "scram-sha-256" which we're not ready to do yet, so it
> >> >> > may be moot to leave it in.
> >> >>
> >> >> > So, thinking out loud about that, we should probably use "md5" and once
> >> >> > we decide to make the encryption method "scram-sha-256" by default, then
> >> >> > we update the recommendation?
> >> >>
> >> >> Meh.  If we're going to break things, let's break them.  Set it to
> >> >> scram by default and let people who need to cope with old clients
> >> >> change the default.  I'm tired of explaining that MD5 isn't actually
> >> >> insecure in our usage ...
> >> >
> >> > +many.
> >>
> >> many++
> >>
> >> Are we doing this for pg12? In any case, I would think we better loudly
> >> point out this change somewhere.
> >
> > Sure, we should point it out, but I don't know that it needs to be
> > screamed from the rooftops considering the packagers have already been
> > largely ignoring our defaults here anyway...
>
> Yeah, I thought about that, but anyone not using those packages will be
> in for a big surprise. Don't get me wrong, I wholeheartedly endorse the
> change, but I predict many related questions on the lists, and anything
> we can do to mitigate that should be done.

You think there's someone who builds from the source and just trusts
what we have put in for the defaults in pg_hba.conf..?

I've got a really hard time with that idea...

I'm all for making people aware of it, but I don't think it justifies
being the top item of the release notes or some such.  Frankly, anything
that starts with "If you build from source, then..." is already going to
be pretty low impact and therefore low on the list of things we need to
cover in the release notes, et al.

I think changing away from "trust" is going to be a much smaller change than people seem to worry about.

It will hit people *in the developer community*.

The thing that will potentially hit *end users* is when the RPMs, DEBs or Windows Installers switch to SCRAM (because of clients with older drivers). But they have *already* stopped using trust many many years ago. 

Making the default change away from trust in the source distro will affect few people.

Making the default change of password_encryption -> scram will affect a *lot* of people. That one needs to be more carefully coordinated.

--
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Magnus Hagander ([hidden email]) wrote:
> The thing that will potentially hit *end users* is when the RPMs, DEBs or
> Windows Installers switch to SCRAM (because of clients with older drivers).

Agreed.  I'm not sure that our change to SCRAM as default would actually
make them change...  It might, but I'm not sure and it's really a bit of
a different discussion in any case because we need to provide info about
how to go about making the migration.

> Making the default change away from trust in the source distro will affect
> few people.

Agreed.

> Making the default change of password_encryption -> scram will affect a
> *lot* of people. That one needs to be more carefully coordinated.

We need to provide better documentation about how to get from md5 to
SCRAM, in my view.  I'm not sure where that should live, exactly.
I really wish we had put more effort into making the migration easy to
do over a period of time, and we might actually have to do that before
the packagers would be willing to make that change.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
On 5/24/19 8:33 AM, Stephen Frost wrote:

> Greetings,
>
> * Magnus Hagander ([hidden email]) wrote:
>> The thing that will potentially hit *end users* is when the RPMs, DEBs or
>> Windows Installers switch to SCRAM (because of clients with older drivers).
>
> Agreed.  I'm not sure that our change to SCRAM as default would actually
> make them change...  It might, but I'm not sure and it's really a bit of
> a different discussion in any case because we need to provide info about
> how to go about making the migration.
Yeah, that's the key piece. Even with (almost) all the drivers now
supporting SCRAM, the re-hashing from md5 => scram-sha-256 does not come
automatically.

>> Making the default change away from trust in the source distro will affect
>> few people.
>
> Agreed.

+1

>> Making the default change of password_encryption -> scram will affect a
>> *lot* of people. That one needs to be more carefully coordinated.

Per some of the upthread comments though, if we go down this path we
should at least make the packagers abundantly aware if we do change the
default. I think some of the work they do could help ease the upgrade pain.

> We need to provide better documentation about how to get from md5 to
> SCRAM, in my view.  I'm not sure where that should live, exactly.
> I really wish we had put more effort into making the migration easy to
> do over a period of time, and we might actually have to do that before
> the packagers would be willing to make that change.

+100...I think we should do this regardless, and I was already thinking
of writing something up around it. I would even suggest that we have
said password upgrade documentation backpatched to 10.

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Jonathan S. Katz ([hidden email]) wrote:

> On 5/24/19 8:33 AM, Stephen Frost wrote:
> > We need to provide better documentation about how to get from md5 to
> > SCRAM, in my view.  I'm not sure where that should live, exactly.
> > I really wish we had put more effort into making the migration easy to
> > do over a period of time, and we might actually have to do that before
> > the packagers would be willing to make that change.
>
> +100...I think we should do this regardless, and I was already thinking
> of writing something up around it. I would even suggest that we have
> said password upgrade documentation backpatched to 10.
Not sure that backpatching is necessary, but I'm not actively against
it.

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.

Or something along those lines.  In other words, I'm talking about new
development work to ease the migration (while also providing some oft
asked about features, like the ability to do rolling passwords...).

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Joe Conway
In reply to this post by Jonathan S. Katz-3
On 5/24/19 8:56 AM, Jonathan S. Katz wrote:
> On 5/24/19 8:33 AM, Stephen Frost wrote:
>> * Magnus Hagander ([hidden email]) wrote:
>>> Making the default change away from trust in the source distro will affect
>>> few people.
>>
>> Agreed.
>
> +1

Fewer people, but likely disproportionately high representation on pgsql
lists. Anyway, nuff said -- I guess the future will tell one way or the
other.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Jonathan S. Katz-3
In reply to this post by Stephen Frost
On 5/24/19 9:01 AM, Stephen Frost wrote:

> Greetings,
>
> * Jonathan S. Katz ([hidden email]) wrote:
>> On 5/24/19 8:33 AM, Stephen Frost wrote:
>>> We need to provide better documentation about how to get from md5 to
>>> SCRAM, in my view.  I'm not sure where that should live, exactly.
>>> I really wish we had put more effort into making the migration easy to
>>> do over a period of time, and we might actually have to do that before
>>> the packagers would be willing to make that change.
>>
>> +100...I think we should do this regardless, and I was already thinking
>> of writing something up around it. I would even suggest that we have
>> said password upgrade documentation backpatched to 10.
>
> Not sure that backpatching is necessary, but I'm not actively against
> it.
Well, for someone who wants to cut over and has to manually guide the
process, a guide will help in absence of new development.

>
> 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.
>
> Or something along those lines.  In other words, I'm talking about new
> development work to ease the migration (while also providing some oft
> asked about features, like the ability to do rolling passwords...).
Cool, I have been thinking about a similar feature as well to help ease
the transition (and fwiw was going to suggest it in my previous email).

I think an interim step at least is to document how we can at least help
ease the transition.

Thanks,

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Heikki Linnakangas
In reply to this post by Stephen Frost
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.

Umm, that's what "md5" already does. Per documentation
(https://www.postgresql.org/docs/current/auth-password.html):

 > 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.

The migration path is:

1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256'
in postgresql.conf.

2. Wait until all users have reset their passwords, so that all users
have a SCRAM-SHA-256 verifier.

3. Replace "md5" with "scram-sha-256" in pg_hba.conf.

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.

- Heikki


Reply | Threaded
Open this post in threaded view
|

Re: initdb recommendations

Stephen Frost
Greetings,

* Heikki Linnakangas ([hidden email]) 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.
>
> 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.
>
> The migration path is:
>
> 1. Use "md5" in pg_hba.conf, and put password_encryption='scram-sha-256' in
> postgresql.conf.
>
> 2. Wait until all users have reset their passwords, so that all users have 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.
>
> 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

signature.asc (836 bytes) Download Attachment
12