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.
This allows the superuser to set a password validity period of e.g.,
60 days, and for users to create new passwords before the old ones
expire, and use both until the old one expires. This will aid in
password rollovers for apps and other systems that need to connect
with password authentication.
The first patch simply moves password to a new catalog, no functional changes.
The second patch allows multiple passwords to be used simultaneously.
The third adds per-password expiration, SQL grammar, and the GUC.
Some future work intended to build on this includes:
- disallowing password reuse
- transitioning between password mechanisms
Example output (note the NOTICES can go away, but are helpful for
demo/testing purposes):
postgres=# alter system set password_valid_duration = '1 day';
NOTICE: Setting password duration to "1 day"
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# create user joshua password 'a' expires in '5 minutes';
NOTICE: Setting password duration to "1 day"
NOTICE: Password will expire at: "2022-03-02 14:52:31.217193" (from SQL)
CREATE ROLE
---
$ psql -h 127.0.0.1 -U joshua postgres
Password for user joshua:
psql (12.7, server 15devel)
WARNING: psql major version 12, server major version 15.
Some psql features might not work.
Type "help" for help.
postgres=> alter role joshua passname 'newone' password 'asdf' expires
in '1 year';
ERROR: must be superuser to override password_validity_duration GUC
postgres=> alter role joshua passname 'newone' password 'asdf';
NOTICE: Password will expire at: "2022-03-03 14:47:53.728159" (from GUC)
ALTER ROLE
postgres=>
--
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)