Greetings, * Jeff Davis (pg...@j-davis.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
signature.asc
Description: PGP signature
