Re: [PoC/RFC] Multiple passwords, interval expirations

Greetings,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> 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

Undoubtably biased ... but I don't agree that this is so difficult.
What points have been raised as a downside of the originally proposed
approach, specifically?

Reading back through the thread, from a user perspective, the primary
one seems to be that passwords are expected to be named. I'm surprised
this is being brought up as such a serious concern. Lots and lots and
lots of things in the system require naming, after all, and the idea
that this is somehow harder or more of an issue is quite odd to me.

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

I've got absolutely no interest in supporting md5- it's high time to rip
that out and be happy that it's gone. We nearly did it last year and
I'm really hoping we accomplish that this year.

I'm open to the idea that we may need to support some new SCRAM version
or an alternative mechanism in the future, but it's long past time to
spend any time worrying about md5. As for how difficult it is to deal
with supporting an alternative in the future- that's going to depend a
great deal on what that alternative is and I don't know that we can
really code to handle that as part of this effort in a sensible way, and
trying to code for "anything" is going to likely make this much more
complicated, and probably rife with security issues too.

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

We have monitoring for many, many parts of the system and this would be
a good thing to monitor also- not just at a per-password level but also
at an overall role/user level, as you have a similar issue there and we
don't properly provide users with any way to check reasonably "hey, when
was the last time this user logged in?". No, trawling through ancient
logs, if you even have them, isn't a proper solution to this problem.

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

It's already the case that lots and lots and lots of systems out there
log into PG using the same username/password. With this, we're at least
offering them the ability to vary the password and keep the user the
same. We've even seen this be asked for in other ways- the ability to
use distinct Kerberos or LDAP identifies to log into the same user in
the database, see pg_ident.conf and various suggestions for how to bring
that to LDAP too. Other systems also support the ability to have a
group of users in LDAP, or such, be allowed to log into a specific
database user. One big reason for this is that then you know everyong
logging into that account has exactly the same access to things- because
that's the lowest level at which access can be granted. Having a way to
support this similar capability but for passwords is certainly useful.

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

As mentioned, we have this general idea already; a simplified CREATE
INDEX syntax can be used as an example:

CREATE INDEX [ [ IF NOT EXISTS ] name ] ON table_name ...

and so we could have:

CREATE PASS [ [ IF NOT EXISTS ] name ] FOR role ...

Giving the user the option of providing a name, but just generating one
if the user declines to include a name. Naturally, users would have to
have some way to look up the names but that doesn't seem difficult to
provide such as through some \du command, along with appropriate views,
just like we have for indexes.

I do generally feel like the original patch was lacking when it came to
syntax and password management functions, but I don't think the answer
to that is to just throw away the whole concept of multiple passwords in
favor of only supporting a very limited number of them. I'd think we'd
work towards improving on the syntax to support what we think users will
need to make all of this work smoothly.

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

Do we need a current or primary? How is that defined, exactly? If we
want it, we could surely create it and make it work, but we'd need to
have a good understand of just what it is and why it's the 'current' or
'primary' and I'm not sure that we've actually got a good definition for
that and I'm not convinced yet that we should.

Thanks,

Stephen