Postgres MD5 Passwords

We use Ansible to deploy and configure our PostgreSQL servers. This works well because we can define all of our users in code and have version control for our database infrastructure in Github. However, there are security concerns when adding user credentials to Github (even with private Github repositories). Thankfully, Postgres makes it easy to get around this issue by using MD5 password hashes when creating users so we do not need to store the actual password.

The main problem I have run into with MD5 hashes is remember how to create them in the format Postgres is expecting. The MD5 hash is generated by supplying the password AND username. Leaving out the username will result in a hash that does not work (trust me, I’ve made this mistake too many times). Here’s an example of one way to generate a Postgres MD5 hash using python:

python -c "from passlib.apps import postgres_context;
import getpass;
print postgres_context.encrypt(getpass.getpass(), user='<username>')"

Another easy way to generate the MD5 hash from a command line:

echo "md5`echo -n "passwordUSERNAME" | md5`"

I kept forgetting these commands and accidentally leaving out the usernames when running them. Here’s a script that I now use that works great if you’re running MacOS.

#!/bin/sh
#
# AUTHOR: David Hollenberger
#
# PURPOSE: Generate Postgres MD5 password hashes from MacOS command line
#

read -p "Enter Username: `echo $'\n> '` " myuser
read -s -p "Enter Password: `echo $'\n> '` " mypassword
printf "\n"

md5=`md5 -q -s $mypassword$myuser`
pgmd5=`printf "md5%s" "$md5"`

echo $pgmd5 | pbcopy
printf "$pgmd5"
printf "\nMD5 Copied to clipboard\n"

The script will prompt for your username and password. It will also copy the resulting MD5 has to the clipboard.

Enter Username:
>  foo
Enter Password:
>
md571458c44e0c04fbbb800bc4c470d5eb5

MD5 hash copied to clipboard

Once you have the MD5 you can create or alter your postgres user.

create role foo with encrypted password 'md571458c44e0c04fbbb800bc4c470d5eb5';