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 because the statement can appear in pg_stat_activity
or the server logs:
CREATE ROLE <role_name> WITH LOGIN PASWWORD 'role_password';
The passwords in pg_catalog
are encrypted with a slat by default, as shown in the following example. Note how passwd
for the a
and b
roles are different even though they have the same password:
CREATE ROLE a WITH LOGIN PASWWORD 'a'; CREATE ROLE b WITH LOGIN PASWWORD 'a'; SELECT usename, passwd FROM pg_shadow WHERE usename IN ('a','b'); usename | passwd ---------+------------------------------------- b ...