Many PostgreSQL database servers are configured to log some or all SQL statements that they run. By default these logs are plaintext with no redaction of sensitive information. This means that the plain text password could be logged when a user changes their database password. Fortunately we can pre-generate the md5 hash of the password in the format that postgres expects and use that in place of the real password.
The encrypted format of the postgres password is the string "md5" followed by the md5 hash of a string comprised of the password followed by the postgres username.
Assuming a username of "myuser" and a password of "mypass"
$ echo "md5$(echo -n "mypassmyuser" | md5sum | cut -f 1 -d ' ')"
md5dd9c52d41abcc8c5de5d717d9fd2efee
Assuming a username of "myuser" and a password of "mypass"
% echo "md5$(echo -n "mypassmyuser" | md5)"
md5dd9c52d41abcc8c5de5d717d9fd2efee
Windows does not provide a native tool for generating an md5 hash for a string.
As an alternative, Web-based md5 hash generators, such as MD5 Hash Generator, may be used.
Use "passwordusername" as the text to be hashed.
The encrypted password will be "md5" plus the hash value returned by the website.
% psql "postgresql://myuser@pgdvlp-na.db.es.cloud.vt.edu:5432/mydb"
Password for user myuser:
psql (14.1, server 10.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.</p>
mydb=> alter user myuser password 'md5dd9c52d41abcc8c5de5d717d9fd2efee';
ALTER ROLE
mydb=>
% psql "postgresql://myuser:mypassword@pgdvlp-na.db.es.cloud.vt.edu:5432/mydb"
psql (14.1, server 10.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mydb=>