When creating a role with password and login options, one can see the role's details in the pg_shadow catalog relation. Note that it is not recommended to use the following format to create the password:

CREATE ROLE <role_name> <with options> PASSWORD 'some_password';

The CREATE ROLE statement can appear in pg_stat_activity as well as the server logs, as follows:

postgres=# SELECT query FROM pg_stat_activity;
                query 
--------------------------------------
 
 
 SELECT query FROM pg_stat_activity;
 create role c password 'c';

All passwords stored in pg_shadow are encrypted with salt; finally, renaming an account will rest the password as follows:

postgres=# ALTER ROLE a RENAME TO b;
NOTICE: MD5 password cleared because of role rename

When creating a user with a password, it is recommended to use the password psql meta command because it ensures that the password does not appear in clear text form in the psql history command, server log files, or elsewhere. To change the password, one can invoke the meta command using a superuser role as follows:

postgres=#password <some_role_name>
Enter new password: 
Enter it again: 
ERROR: role "<some_role_name>" does not exist