How to check when a DB user password was changed

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article explains how to get the time of password change from existing views in GPDB.

Environment

Product Version: 6.20

Resolution

The table gp_stat_last_shoperation can be used to get that information.

Here below is an example:

## We did a password change
gpadmin=# alter user jimmy11 with password 'xxxxxxx';
ALTER ROLE
Time: 11.379 ms

## From the view pg_stat_last_shoperation, we can get the time of change and user oid for the password change
gpadmin=# select * from pg_stat_last_shoperation order by statime desc limit 1;
 classid | objid | staactionname | stasysid | stausename | stasubtype |           statime            
---------+-------+---------------+----------+------------+------------+------------------------------
    1260 | 16430 | ALTER         |       10 | gpadmin    | PASSWORD   | 2023-10-26 15:12:07.94769+08
(1 row)

## We can get the user name via the user oid that aquired from the last step
gpadmin=# select rolname from pg_authid where oid = 16430;
 rolname 
---------
 jimmy11
(1 row)