By default, PostgreSQL internally encrypts sensitive data, such as roles' passwords. However, database users can also encrypt and decrypt sensitive data using the pgcrypto extension.
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...