Learning PostgreSQL 11 - Third Edition

PostgreSQL role password encryption

When creating a role with password and login options, you 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. All passwords stored in pg_shadow are encrypted with salt; finally, renaming an account will reset the password as follows:

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

When creating a user with a password, it is recommended to use the \password psql client meta command because ...