Postgres MD5 Passwords16 Mar 2017
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';