Archive for December, 2013

PostgreSQL and MySQL password hashes

December 28, 2013 Leave a comment

The fate of Norns await us all There is no way to escape The day to answer Oden’s call Or walk through hel’s gate (Amon Amarth – The Fate of Norns)

PostgreSQL and MySQL roles store their text passwords as hashes. Automation tools and scripts will often want to set those hashes, so it’s useful to know how to generate them correctly. There is a difference how PostgreSQL and MySQL hashes are generated. Authenticating via password – PostgreSQL can use ‘password’ or ‘md5’. Main difference is how the password is transferred between client and server. In the former case it is transferred in plaintext, and if the ‘md5’ is used then it’s transferred as a hash. But, PostgreSQL password is not a simple MD5 hash of a plaintext passphrase. PostgreSQL hashes combined name and password and adds ‘md5’ in front of the hash. This simple ruby script describes the process:

#!/usr/bin/env ruby
require 'digest/md5'
puts 'md5' + Digest::MD5.hexdigest(ARGV[0] + ARGV[1])

ARGV[0] is pgsql role password, and ARGV[1] is pgsql role name. So it’s interesting that PostgreSQL uses both role name and password to generate md5 password hash. On the other hand, MySQL doesn’t do any kind of combining, pure passphrase is hashed with SHA1 algorithm, but with two passes. Quick and easy way to generate MySQL password hashes is to use the MySQL-centric query ‘SELECT PASSWORD’:

mysql> SELECT PASSWORD('mypass');
| PASSWORD('mypass')                        |
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
1 row in set (0.03 sec)

But how does that query work under the hood? MySQL uses combination of two SHA1 passes for passwords hashing, and this little ruby snippet shows how:

#!/usr/bin/env ruby
require 'digest/sha1'
puts '*' + Digest::SHA1.hexdigest(Digest::SHA1.digest(ARGV[0])).upcase

MySQL uses two passes to make the life harder to potential attackers.

%d bloggers like this: