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

On Thu, Oct 5, 2023 at 1:09 PM Jeff Davis <pgsql@j-davis.com> wrote:
>
> >
> > I think this would be mighty confusing to users since it's not clear
> > that
> > adding a password will potentially invalidate a current password
> > (which
> > might be actively in use), but only if there are already 2 in the
> > stack.  I
> > worry that such a desіgn might be too closely tailored to the
> > implementation details.  If we proceed with this design, perhaps we
> > should
> > consider ERROR-ing if a user tries to add a third password.
>
> I agree that the proposed language is confusing, especially because ADD
> causes a password to be added and another one to be removed. But
> perhaps there are some non-confusing ways to expose a similar idea.

How about a language like the following (I haven't tried if this will
work in the grammar we have):

CREATE ROLE u1 PASSWORD 'p1';

ALTER ROLE u1ADD NEW PASSWORD 'p2';

ALTER ROLE u1 ADD NEW PASSOWRD 'p3';
ERROR: Cannot have more than 2 passwords at the same time.

ALTER ROLE u1 DROP OLD PASSWORD;

ALTER ROLE u1 ADD NEW PASSOWRD 'p3';
-- succeeds; forgets password 'p1'; p2 and p3 can be used to login

ALTER ROLE u1 DROP NEW PASSWORD;
-- forgets password 'p3'. Only 'p2' can be used to login

ALTER ROLE u1 ADD NEW PASSOWRD 'p4';
-- succeeds; 'p2' and 'p4' can be used to login

-- Set the valid-until of 'new' (p4) password
ALTER ROLE u1 VALID UNTIL '2024/01/01';

-- If we need the ability to change valid-until of both old and new,
we may allow something like the following.
ALTER ROLE u1 [_NEW_ | OLD] VALID UNTIL '2024/01/01';

This way there's a notion of a 'new' and 'old' passwords. User cannot
add a third password without explicitly dropping one of existing
passwords (either old or new). At any time the user can choose to drop
the old or the new password. Adding a new password will mark the
current password as 'old'; if there's only old password (because 'new'
was dropped) the 'old' password will remain intact and the new one
will be placed in 'current'/new spot.

So in normal course of operation, even for automated jobs, the
expected flow to roll over the passwords would be:

ALTER USER u1 DROP OLD PASSWORD;
-- success if there is an old password; otherwise NOTICE: no old password
ALTER USER u1 ADD NEW PASSWORD 'new-secret';

> The thing I like about Gurjeet's proposal is that it's well-targeted at
> a specific use case rather than trying to be too general. That makes it
> a little easier to avoid certain problems like having a process that
> adds passwords and never removes the old ones (leading to weird
> problems like 47000 passwords for one user).
>
> But it also feels strange to be limited to two -- perhaps the password
> rotation schedule or policy just doesn't work with a limit of two, or
> perhaps that introduces new kinds of mistakes.
>
> Another idea: what if we introduce the notion of deprecating a
> password?

I'll have to think more about it, but perhaps my above proposal
addresses the use case you describe.

> if we allow multiple deprecated passwords, we'd still have to come up
> with some way to address them (names, numbers, validity period,
> something). But by isolating the problem to deprecated passwords only,
> it feels like the system is still being restored to a clean state with
> at most one single current password. The awkwardness is contained to
> old passwords which will hopefully go away soon anyway and not
> represent permanent clutter.

+1

Best regards,
Gurjeet
http://Gurje.et