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

I had an idea to simplify this feature/patch and after some validation
in internal discussions, I am posting the new approach here. I'd
appreciate any feedback and comments.

To begin with, the feature we are chasing is to make it convenient for
the users to rollover their passwords. Currently there is no easy way
to rollover passwords without increasing the risk of an application
outage. After a password change, the users/admins have to rush to
change the password in all locations where it is stored. There is a
window of time where if the application password is not changed to the
new one, and the application tries to connect/reconnect for any
reason, the application will fail authentication and lead to an outage.

I personally haven't seen any attempts by any
application/driver/framework to solve this problem in the wild, so
following is just me theorizing how one may solve this problem on the
application side; there may be other ways in which others may solve
this problem. The application may be written in such a way that upon
password authentication failure, it tries again with a second
password. The application's configuration file (or environment
variables) may allow specifying 2 passwords at the same time, and the
application will keep trying these 2 passwords alternatively until it
succeeds or the user restarts it with a new configuration. With such a
logic in place in their application, the users may first change the
configuration of all the instances of the application to hold the new
password along with the old/current working password, and only then
change the password in the database. This way, in the event of an
application instance start/restart either the old password will
succeed, or the new password will.

There may be other ways to solve this problem, but I can't imagine any
of those ways to be convenient and straightforward. At least not as
convenient as it can be if the database itself allowed for storing
both the passwords, and honored both passwords at the same time, while
allowing to associate a separate validity period with each of the
passwords.

The patches posted in this thread so far attempt to add the ability to
allow the user to have an arbitrary number of passwords. I believe
that allowing arbitrary number of passwords is not only unnecessary,
but the need to name passwords, the need to store them in a shared
catalog, etc. may actually create problems in the field. The
users/admins will have to choose names for passwords, which they
didn't have to previously. The need to name them may also lead to
users storing password-hints in the password names (e.g. 'mom''s
birthday', 'ex''s phone number', 'third password'), rendering the
passwords weak.

Moreover, allowing an arbitrarily many number of passwords will
require us to provide additional infrastructure to solve problems like
observability (which passwords are currently in use, and which ones
have been effectively forgotten by applications), or create a nuisance
for admins that can create more problems than it solves.

So I propose that the feature should allow no more than 2 passwords
for a role, each with their own validity periods. This eliminates the
need to name passwords, because at any given time there are no more
than 2 passwords; current one, and old one. This also eliminates the
need for a shared catalog to hold passwords, because with the limit of
2 imposed, we can store the old password and its validity period in
additional columns in the pg_authid table.

The patches so far also add a notion of max validity period of
passwords, which only a superuser can override. I believe this is a
useful feature, but that feature can be dealt with separately,
independent of password rollover feature. So in the newer patches I
will not include the relevant GUC and code.

With the above being said, following is the user interface I can think
of that can allow for various operations that users may need to
perform to rollover their passwords. The 'ADD PASSWORD' and 'ALL
PASSWORD' are additions to the grammar. rololdpassword and
rololdvaliduntil will be new columns in pg_authid that will hold the
old password and its valid-until value.

In essence, we create a stack that can hold 2 passwords. Pushing an
element when it's full will make it forget the bottom element. Popping
the stack makes it forget the top element, and the only remaining
element, if any, becomes the top.

-- Create a user, as usual
CREATE ROLE u1 PASSWORD 'p1' VALID UNTIL '2020/01/01';

-- Add another password that the user can use for authentication. This moves
-- the 'p1' password hash and its valid-until value to rololdpassword and
-- rololdvaliduntil, respectively.
ALTER ROLE u1 ADD PASSWORD 'p2' VALID UNTIL '2021/01/01';

-- Change the password 'p2's (current password's) validity
ALTER ROLE u1 VALID UNTIL '2022/01/01';
-- Note that currently I don't have a proposal for how to change the old
-- password's validity period, without deleting the latest/main password. See
-- PASSWORD NULL command below on how to delete the current password. It's very
-- likely that in a password rollover use case it's unnecessary, even
-- undesirable, to change the old password's validity period.

-- If, for some reason, the user wants to get rid of the latest password added.
-- Remove 'p2' (current password). The old password (p1), will be restored to
-- rolpassword, along with its valid-until value.
ALTER ROLE u1 PASSWORD NULL;
-- This may come as a surprise to some users, because currently they expect the
-- user to completely lose the ability to use passwords for login after this
-- command. To get the old behavior, the user must now use the ALL PASSWORD
-- NULL incantation; see below.
-- Issuing this command one more time will remove even the restored password,
-- hence leaving the user with no passwords.

-- Change the validity of the restored/current password (p1)
ALTER ROLE u1 VALID UNTIL '2022/01/01';

-- Add a new password (p3) without affecting old password's (p1) validity
ALTER ROLE u1 ADD PASSWORD 'p3' VALID UNTIL '2023/01/01';

-- Add a new password 'p4'. This will move 'p3' to rololdpassword, and hence
-- 'p1' will be forgotten completely.
-- After this command, user can use passwords 'p3' (old) and 'p4' (current) to
-- login.
ALTER ROLE u1 ADD PASSWORD 'p4' VALID UNTIL '2024/01/01';

-- Replace 'p4' (current) password with 'p5'. Note that this command is _not_
-- using the ADD keyword, hence 'p4' is _not_ moved to rololdpassword column.
-- After this command, user can use passwords 'p3' (old) and 'p5'
-- (current) to login.
ALTER ROLE u1 PASSWORD 'p5' VALID UNTIL '2025/01/01';

-- Using the old password to login will produce a warning, hopefully nudging
-- the user to start using the new password.
export PGPASSWORD=p3
psql -U u1
...
WARNING: Used old password to login

export PGPASSWORD=p5
psql -U u1
...
=> (no warning)

-- Remove all passwords from the role. Even old password, if any, is removed.
ALTER ROLE u1 ALL PASSWORD NULL;

In normal use, the users can simply keep ADDing new passwords, and the
database will promptly remember only one old password, and keep
forgetting any passwords older than that. But on the off chance
that someone needs to forget the latest password they added, and
restore the old password to be the "current" password, they can use
the PASSWORD NULL incantation. Note that this will result in
rol*old*password being set to NULL, because our password memory stack
cannot hold more than 2 elements.

Since this feature is targeted towards password rollovers, it's a legitimate
question to ask if we should enforce that the new password being added has a
valid-until greater than the valid-until of the existing/old password. I don't
think we should enforce this, at least not in this patch, because the
user/admin may have a use case where they want a short-lived new password that
they intend/want to change very soon; I'm thinking of cases where passwords are
being rolled over while they are also moving from older clients/libraries that
don't yet support scram-sha-256; keep using md5 and add passwords to honor
password rollover policy, but then as soon as all clients have been updated and
have the ability to use scram-sha-256, rollover the password again to utilize
the better mechanism.

I realize that allowing for a maximum of 2 passwords goes against the
zero-one-infinity rule [1], but I think in the case of password
rollovers it's perfectly acceptable to limit the number of active
passwords to just 2. If there are use cases, either related to password
rollovers, or in its vicinity, that can be better addressed by
allowing an arbitrarily many passwords, I would love to learn about
them and change this design to accommodate for those use cases, or
perhaps revert to pursuing the multiple-passwords feature.

[1]: https://en.wikipedia.org/wiki/Zero_one_infinity_rule

Best regards,
Gurjeet
http://Gurje.et