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

> > On Wed, Mar 2, 2022 at 9:58 AM Joshua Brindle
> > <joshua.brindle@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:
> >
>
> Attached are fixed patches rebased against the lastest master.
>
>
> > 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.

Now that the commitfest is over these are rebased on master.

It's unclear if I will be able to continue working on this featureset,
this email address will be inactive after today.