Setting user Postgres passwords via MD5

Say you want to create a Postgres account for a user but you don’t want them to have to reset it after logging in, and you don’t want to do the “come type in a password on my computer” routine. Well, here’s one way around this.

1. Have the user generate an MD5 of their password on their local computer. Postgres uses the username as the salt for the hash, so the command to generate the md5 on a Mac would be (assuming a username of ‘ehoffman’ and a password of ‘abcdefg’):

   [evan@Evans-MacBook-Pro ~] $ echo -n abcdefgehoffman | md5
   95eebfcce27162773a3828689df9d79e
   

   The “-n” is important – without it, the newline gets included in the hash. After they generate their MD5, have them send it to you (along with their username).

2. Create the user’s account in the database (or ALTER ROLE if it already exists):

   CREATE ROLE ehoffman LOGIN INHERIT ENCRYPTED PASSWORD 'md595eebfcce27162773a3828689df9d79e';
   

   Syntax for an existing account:

   ALTER ROLE ehoffman ENCRYPTED PASSWORD 'md595eebfcce27162773a3828689df9d79e';
   

That’s it. This has the added benefit of the password never being logged in the DB logs or the .psql_history. The main downside is the possibility of user error.