On Wed, Mar 2, 2022 at 9:58 AM Joshua Brindle <joshua.brin...@crunchydata.com> wrote: > > This is not intended for PG15. > > Attached are a proof of concept patchset to implement multiple valid > passwords, which have independent expirations, set by a GUC or SQL > using an interval. >
<snip> > postgres=# select * from pg_auth_password ; > roleid | name | > password > | expiration > --------+---------+------------------------------------------------------------------------------------------------------------------- > --------------------+------------------------------- > 10 | __def__ | > SCRAM-SHA-256$4096:yGiHIYPwc2az7xj/7TIyTA==$OQL/AEcEY1yOCNbrZEj4zDvNnOLpIqltOW1uQvosLvc=:9VRRppuIkSrwhiBN5ePy8wB1y > zDa/2uX0WUx6gXi93E= | > 16384 | __def__ | > SCRAM-SHA-256$4096:AAAAAAAAAAAAAAAAAAAAAA==$1Ivp4d+vAWxowpuGEn05KR9lxyGOms3yy85k3D7XpBg=:k8xUjU6xrJG17PMGa/Zya6pAE > /M7pEDaoIFmWvNIEUg= | 2022-03-02 06:52:31.217193-08 > 16384 | newone | > SCRAM-SHA-256$4096:AAAAAAAAAAAAAAAAAAAAAA==$WK3+41CCGDognSnZrtpHhv00z9LuVUjHR1hWq8T1+iE=:w2C5GuhgiEB7wXqPxYfxBKB+e > hm4h6Oeif1uzpPIFVk= | 2022-03-03 06:47:53.728159-08 > (3 rows) There's obviously a salt problem here that I'll need to fix that apparently snuck in at the last rebase, but this brings up one aspect of the patchset I didn't mention in the original email: For the SCRAM protocol to work as is with existing clients the salt for each password must be the same. Right now ALTER USER will find and reuse the salt, but a user passing in a pre-computed SCRAM secret currently has no way to get the salt. for \password (we'll need a new one that takes a password name) I was thinking libpq could hold onto the salt that was used to log in, but for outside computation we'll need some way for the client to request it. None of that is done yet.