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

> On Mon, Oct 9, 2023 at 2:31 AM Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> >
> > Next steps:
> > - Break the patch into a series of smaller patches.
> > - Add TAP tests (test the ability to actually login with these passwords)
> > - Add/update documentation
> > - Add more regression tests

Please see attached the v4 of the patchset that introduces the notion
of named passwords slots, namely 'first' and 'second' passwords, and
allows users to address each of these passwords separately for the
purposes of adding, dropping, or assigning expiration times.

Apart from the changes described by each patch's commit title, one
significant change since v3 is that now (included in v4-0002...patch)
it is not allowed for a role to have a mix of a types of passwords.
When adding a password, the patch ensures that the password being
added uses the same hashing algorithm (md5 or scram-sha-256) as the
existing password, if any. Having all passwords of the same type
helps the server pick the corresponding authentication method during
connection attempt.

The v3 patch also had a few bugs that were exposed by cfbot's
automatic run. All those bugs have now been fixed, and the latest run
on the v4 branch [1] on my private Git repo shows a clean run [1].

The list of patches, and their commit titles are as follows:

> v4-0001-...patch Add new columns to pg_authid
> v4-0002-...patch Update password verification infrastructure to handle two passwords
> v4-0003-...patch Added SQL support for ALTER ROLE to manage two passwords
> v4-0004-...patch Updated pg_dumpall to support exporting a role's second password
> v4-0005-...patch Update system views pg_roles and pg_shadow
> v4-0006-...patch Updated pg_authid catalog documentation
> v4-0007-...patch Updated psql's describe-roles meta-command
> v4-0008-...patch Added documentation for ALTER ROLE command
> v4-0009-...patch Added TAP tests to prove that a role can use two passwords to login
> v4-0010-...patch pgindent run
> v4-0011-...patch Run pgperltidy on files changed by this patchset

Running pgperltidy updated many perl files unrelated to this patch, so
in the last patch I chose to include only the one perl file that is
affected by this patchset.

[1]: password_rollover_v4 (910f81be54)
https://github.com/gurjeet/postgres/commits/password_rollover_v4

[2]: https://cirrus-ci.com/build/4675613999497216

Best regards,
Gurjeet
http://Gurje.et