Re: [PoC/RFC] Multiple passwords, interval expirations - Mailing list pgsql-hackers

On Tue, 2023-10-17 at 16:20 -0400, Stephen Frost wrote:
> Agreed that it's a bad idea to design to support 2 and only 2.

I don't disagree, but it's difficult to come up with syntax that:

 (a) supports N passwords
 (b) makes the ordinary cases simple and documentable
 (c) helps users avoid mistakes (at least in the simple cases)
 (d) makes sense passwords with and without validity period
 (e) handles the challenging cases

One challenging case is that we cannot allow the mixing of password
protocols (e.g. MD5 & SCRAM), because the authentication exchange only
gets one chance at success. If someone ends up with 7 MD5 passwords,
and they'd like to migrate to SCRAM, then we can't allow them to
migrate one password at a time (because then the other passwords would
break). I'd like to see what the SQL for doing this should look like.

>   If
> nothing else, there's the very simple case that the user needs to do
> another password rotation ... and they look and discover that the old
> password is still being used and that if they took it away, things
> would
> break, but they need time to run down which system is still using it
> while still needing to perform the migration for the other systems
> that
> are correctly being updated- boom, need 3 for that case.

That sounds like a reasonable use case. I don't know if we should make
it a requirement, but if we come up with something reasonable that
supports this case I'm fine with it. Ideally, it would still be easy to
see when you are making a mistake (e.g. forgetting to ever remove
passwords).

>   There's other
> use-cases that could be interesting though- presumably we'd log which
> password is used to authenticate and then users could have a fleet of
> web servers which each have their own password but log into the same
> PG
> user and they could happily rotate the passwords independently for
> all
> of those systems.
>
> if they're all
> logging in with the same role and just a different password, that's
> not
> going to happen.

I'm not sure this is a great idea. Can you point to some precedent
here?


> Giving users the option of not having to specify a name and letting
> the
> system come up with one (similar to what we do for indexes and such)
> could work out pretty decently, imv.  I'd have that be optional
> though-
> if the user wants to specify a name, then they should be allowed to
> do
> so.

Can you describe how some basic use cases should work with example SQL?

>
> > * the identifier of the current password always changing (perhaps
> > fine
> > if it'a a "created at" ID?)
>
> I'm not following what the issue is you're getting at here.

I just meant that when rotating passwords, you have to keep coming up
with new names, so the "current" or "primary" one would not have a
consistent name.

Regards,
    Jeff Davis