Working example of a password profile v18

This example walks you through the process of creating a password profile and associating it with a role. The CREATE EXTENSION command is used to add the password profile functionality to your database.

• You need to load the password_profile libraries via shared_preload_libraries in postgresql.conf file.

  Add password_profile to the shared_preload_libraries line:

  → WrapCopy

  shared_preload_libraries = 'password_profile'

  Restart the database server.

• Create the extension in your database:

  → WrapCopy

  CREATE EXTENSION password_profile;

• Create a user and GRANT EXECUTE privileges to the user for all functions in the password_profile schema:

  → WrapCopy

  CREATE USER user1 WITH PASSWORD 'password';
  GRANT EXECUTE ON SCHEMA password_profile TO user1;

  → WrapCopy

  GRANT EXECUTE ON FUNCTION password_profile.pg_create_profile(text, numeric, numeric, numeric, numeric, text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_alter_profile(text, numeric, numeric, numeric, numeric, text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_rename_profile(text, text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_attach_role_profile(text, text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_role_account_lock(text, boolean) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_get_role_status(oid) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_get_role_status(text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_role_password_expire(text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_detach_role_profile(text) TO user1;
  GRANT EXECUTE ON FUNCTION password_profile.pg_drop_profile(text, boolean) TO user1;

• Create a new profile as a superuser or as user1

  → WrapCopy

  SELECT PASSWORD_PROFILE.PG_CREATE_PROFILE('my_profile');

  This command creates a new profile and adds an entry to PG_PROFILE catalog.

• Alter the profile to set failed_login_attempts to 2, password_lock_time to 1 day, password_life_time to 3 days and password_grace_time to 1 day.

  → WrapCopy

  SELECT PASSWORD_PROFILE.PG_ALTER_PROFILE('my_profile', 2, 1, 3, 1);
  
  SELECT * FROM pg_profile;

  Output

    oid  |  prfname  | prffailedloginattempts | prfpasswordlocktime | prfpasswordlifetime | prfpasswordgracetime | prfpasswordverifyfuncdb | prfpasswordverifyfunc
  -------+-----------+------------------------+---------------------+---------------------+----------------------+-------------------------+-----------------------
    6108 | default   |                     -2 |                  -2 |                  -2 |                   -2 |                         |
   16397 | myprofile |                      2 |               86400 |              259200 |                86400 |                       0 |                     0
   (2 rows)

  The prfpasswordverifyfunc and prfpasswordverifyfuncdb column values are null for DEFAULT profile. For myprofile they are set to 0, as no values were provided while creating this profile, so it referes to DEFAULT profile values.

• Attach the profile to a role

  → WrapCopy

  SELECT PASSWORD_PROFILE.PG_ATTACH_ROLE_PROFILE('user1', 'myprofile');
  
  SELECT * FROM pg_auth_profile;

  Output

   roleid | profileid | roleaccountstatus | rolefailedlogins | rolelockdate |       rolepasswordsetat       | rolepasswordexpire
  --------+-----------+-------------------+------------------+--------------+-------------------------------+--------------------
    16384 |     16397 |                 0 |                0 |              | 2025-08-23 05:02:28.70456+00 |
  (1 row)

  Here the rolepasswordsetat column shows the timestamp when the role is attached to the profile. This column value gets updated whenever the role password is updated.

• Now try one failed login attempt and then query:

  → WrapCopy

  SELECT roleid, password_profile.PG_GET_ROLE_STATUS('myuser'), rolefailedlogins, rolelockdate FROM pg_auth_profile;

  Output

  roleid | pg_get_role_status | rolefailedlogins | rolelockdate
  -------+--------------------+------------------+--------------
   16384 | OPEN               |                1 |
  (1 row)

  You can see the rolefailedlogins column has been incremented by 1 and the role status is open.

• Now try more failed log in attempts and then query again:

  → WrapCopy

  \c - myuser
  Password for user myuser:
  FATAL:  role "myuser" is locked
  Previous connection kept
  
  SELECT roleid, password_profile.PG_GET_ROLE_STATUS('myuser'), rolefailedlogins, rolelockdate FROM pg_auth_profile;

  Output

  roleid | pg_get_role_status | rolefailedlogins |          rolelockdate
  -------+--------------------+------------------+-------------------------------
   16384 | LOCKED(TIMED)      |                0 | 2025-08-24 07:53:58.524348+00
  (1 row)

  Due to multiple failed login attempts, the user account has been locked. The role status shows as LOCKED(TIMED), and the failed login attempt counter has been reset to 0.

  The TIMED status indicates that the account will automatically unlock after one day, as defined by the PASSWORD_LOCK_TIME parameter. A timestamp is also recorded when the account becomes locked.

  Once the lock period expires, the user will be able to log in again, and the status will return to OPEN.

• If the user successfully logs in after a few failed attempts but before reaching the maximum limit (set by FAILED_LOGIN_ATTEMPTS), the failed login counter is also reset to 0.

  → WrapCopy

  \c - myuser
  Password for user myuser:
  You are now connected to database "postgres" as user "myuser".
  
  SELECT roleid, password_profile.PG_GET_ROLE_STATUS('myuser'), rolefailedlogins, rolelockdate FROM pg_auth_profile;

  Output

  roleid | pg_get_role_status | rolefailedlogins |          rolelockdate
  -------+--------------------+------------------+-------------------------------
   16384 | OPEN               |                0 | 2025-08-24 07:53:58.524348+00
  (1 row)

  It preserves the last lock date of the role.