[YSQL] [PG15 online upgrade] Created profiles are missing after upgrade to PG15

[hariharasudhan-yb]

Jira Link: DB-15015

Description

After upgrading from version 2024.2.2.0-b7 to 2.25.1.0-b220, the created profiles are missing and are not attached to the respective users. Users are present while profiles are not there.

In PG11 before upgrade (2024.2.2.0-b7)

yugabyte=# SELECT * FROM pg_yb_role_profile;
 rolprfrole | rolprfprofile | rolprfstatus | rolprffailedloginattempts | rolprflockeduntil 
------------+---------------+--------------+---------------------------+-------------------
      16384 |         16385 | o            |                         3 | 
(1 row)


yugabyte=# SELECT * FROM pg_yb_profile;
     prfname      | prfmaxfailedloginattempts | prfpasswordlocktime 
------------------+---------------------------+---------------------
 testprofilename  |                         5 |                   0
 testprofilename1 |                        10 |                   0

In PG15 after upgrade (2.25.1.0-b220) while verifying:

 yugabyte=# SELECT * FROM pg_yb_role_profile;
 oid | rolprfrole | rolprfprofile | rolprfstatus | rolprffailedloginattempts | rolprflockeduntil 
-----+------------+---------------+--------------+---------------------------+-------------------
(0 rows)

yugabyte=# SELECT * FROM pg_yb_profile;
 oid | prfname | prfmaxfailedloginattempts | prfpasswordlocktime 
-----+---------+---------------------------+---------------------
(0 rows)

Test steps to repro:

• create universe with following tserver flags enabled

ysql_enable_profile : true
ysql_enable_auth : true
ysql_hba_conf_csv : local all yugabyte trust

• create a role/user with pass
• create profile and attach it to the created user with Limit in failed login attempts
• Do some failed login attempts with the profile attached user and verify the failed attempt count
• Lock the user and unlock the user and verify
• Do some more failed login attempts and verify the count
• Do software upgrade and finalize
• Check the profiles and user login attemps - > Failing here with No profiles are found

yugabyte=# SELECT * FROM pg_yb_role_profile;
 oid | rolprfrole | rolprfprofile | rolprfstatus | rolprffailedloginattempts | rolprflockeduntil 
-----+------------+---------------+--------------+---------------------------+-------------------
(0 rows)

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.