How to reset YSQL superuser password?

Environment:

• YB Database (YSQL) - All versions

Minimum requirement:

You need to have ssh access via yugabyte user to Tserver node’s.

Please follow the instructions below to change the password for the superuser (admin user) of Yugabyte PostgreSQL (YSQL).

Step 1:

Identify the PostgreSQL process id and ysql_hba.conf location using below command.

[yugabyte@localhost~]$ ps -ef |grep postgres
yugabyte  7781  7730  0 Oct10 ?        00:00:49 /home/yugabyte/yb-software/yugabyte-2.14.2.0-b25-centos-x86_64/postgres/bin/postgres -D /mnt/d0/pg_data -p 5433 -h 10.9.77.127 -k /tmp/.yb.5687764934865305182 -c unix_socket_permissions=0700 -c logging_collector=on -c log_directory=/mnt/d0/yb-data/tserver/logs -c yb_pg_metrics.node_name=yb-dev-cluster1-n3 -c yb_pg_metrics.port=13000 -c config_file=/mnt/d0/pg_data/ysql_pg.conf 
-c hba_file=/mnt/d0/pg_data/ysql_hba.conf

Here the PostgreSQL pid is 7781 and ysql_hba.conf location is /mnt/d0/pg_data/ysql_hba.conf

[yugabyte@localhost ~]$ cat /mnt/d0/pg_data/ysql_hba.conf
This is an autogenerated file, do not edit manually!
Internal configuration:
host all all all md5

Step 2:

Edit the above file and change the authentication method from md5 to trust for connections from the same host as below

[yugabyte@localhost ~]$ vi /mnt/d0/pg_data/ysql_hba.conf
# This is an autogenerated file, do not edit manually!
# Internal configuration:
# local all postgres yb-tserver-key
host all yugabyte samehost trust

Step 3:

Once this the done reload PostgreSQL process to read the configuration changes for in the ysql_hba.conf file using below command . This kill command will not kill the PostgreSQL process but will only have the running process reload the changes from ysql_hba.conf file

[yugabyte@localhost ~]$ kill -1 7781

Once this is done , now you can login to Ysqlsh with out password and change the it as below.

yugabyte=# alter user yugabyte with password 'newpassword';
ALTER ROLE

Step4:

Once the password is changed, Re-edit the /mnt/d0/pg_data/ysql_hba.conf file to revert back the changes so that no unauthorised person can login

Step 5:

Try logging in using new password to YSQL