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

On Wed, Mar 2, 2022 at 10:35 AM Joshua Brindle
<joshua(dot)brindle(at)crunchydata(dot)com> wrote:
>
> On Wed, Mar 2, 2022 at 9:58 AM Joshua Brindle
> <joshua(dot)brindle(at)crunchydata(dot)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.