Postgres: MD5 Password / Plain password

Question

I'm trying to understand how role passwords are supposed to operate in Postgres.

https://www.postgresql.org/docs/current/static/sql-createrole.html says for ENCRYPTED / UNENCRYPTED

If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is,

So my unencrypted password is: MyPassword .

The MD5 hash of "MyPassword" is 48503dfd58720bd5ff35c102065a52d7

If I do

-- See https://www.postgresql.org/docs/9.6/static/sql-alterrole.html
ALTER ROLE "MeOhMy"
LOGIN
PASSWORD '48503dfd58720bd5ff35c102065a52d7'
;

And then attempt to use "MyPassword" when doing

  sudo -u postgres psql meohmy -h 127.0.0.1 -d meohmy_development

I, of course, first get prompted for my sudo password and then I get prompted by Postgres "Password for meohmy"

If I enter MyPassword I get

FATAL:  password authentication failed for user "ralph@dos32.com"

If I enter, instead, 48503dfd58720bd5ff35c102065a52d7 then I can sign in.

What am I not understanding?

MD5 is not encryption as hash function are not encryption, they are one-way non-reversible methods. Encryption assumes decryption, that with the correct key encrypted data can be returned to it's us un-encrypted state. — zaph, Jul 30 '17 at 14:04
MD5 is not a secure or acceptable password verifier. When saving a password verifier just using a hash function is not sufficient and just adding a salt does little to improve the security. Instead iterate over an HMAC with a random salt for about a 100ms duration and save the salt with the hash. Use a function such as `PBKDF2`, `Rfc2898DeriveBytes`, `password_hash`, `Bcrypt`, `passlib.hash` or similar functions. The point is to make the attacker spend a lot of time finding passwords by brute force. — zaph, Jul 30 '17 at 14:04
While you are absolutely correct in both your comments, there are two considerations: 1) I am limited to the facilities Postgres is giving me. 2) The data I am protecting isn't that valuable so spending time to protect it with the highest grade of security just isn't worth it. — RalphShnelvar, Jul 30 '17 at 21:15

RCross · Answer 1 · 2019-11-21T16:52:33.583

To create an md5 password for PostgreSQL, the formula is:

"md5" + md5(password + username)

Here are 3 ways you can create one, where the username is "admin" and the password is "password123"...

Linux:

# echo -n "md5"; echo -n "password123admin" | md5sum | awk '{print $1}'
md53f84a3c26198d9b94054ca7a3839366d

NOTE: The -n is critical to avoid including the newline character in your hash!

MacOS:

➜ echo -n "md5"; md5 -qs "password123admin"                                                                                                                                                                                   
md53f84a3c26198d9b94054ca7a3839366d

Python 2:

>>> import hashlib
>>> print("md5" + hashlib.md5("password123" + "admin").hexdigest())
md53f84a3c26198d9b94054ca7a3839366d

Python 3:

as above, but use binary strings

print("md5" + hashlib.md5(b"password123" + b"admin").hexdigest())

In the Linux example I think its better to use single quotes instead of double quotes. For example, if the password has the special character `$`, the double quotes would interpret it as a variable. Like `echo -n "password$123admin"` vs `echo -n 'password$123admin'`. [See other interesting behaviors here](https://stackoverflow.com/a/42082956/8185511). — tk3, Nov 30 '22 at 15:53

score 3 · Answer 2 · answered Jul 30 '17 at 01:32

3

Postgresql hashed passwords have md5 prefix:

md548503dfd58720bd5ff35c102065a52d7

answered Jul 30 '17 at 01:32

zerkms

249,484
69
436
539

Where is this documented!!?! All I have found is https://stackoverflow.com/questions/14918763/generating-postgresql-user-password – RalphShnelvar Jul 30 '17 at 02:33
1

@RalphShnelvar https://www.postgresql.org/docs/current/static/catalog-pg-authid.html – gilad905 Feb 13 '18 at 11:36

score 0 · Answer 3 · answered Sep 14 '20 at 21:09

Using Postgres11 on GCP Cloud SQL. Gitlab version gitlab-ee 13.3.4 Omnibus install

# gitlab-ctl pg-password-md5 gitlab_user
Enter password:
Confirm password:

and

# echo -n <password for gitlab_user>gitlab_user | md5sum

are equivalent.

Note: My db user is gitlab_user

score -1 · Answer 4 · answered Jul 30 '17 at 02:37

-1

The answer provided by @zerkms is partially correct. It led me to the right answer.

The answer provided in Generating postgresql user password is the answer that works for me.

answered Jul 30 '17 at 02:37

RalphShnelvar

557
1
6
17

1

Just a link is insufficient, it does not fully help future readers and may become invalid. – zaph Jul 30 '17 at 14:08
Ok ... for future readers the article I pointed at says: `pghash = "md5" + hashlib.md5(password + username).hexdigest()` – RalphShnelvar Jul 30 '17 at 21:18

Postgres: MD5 Password / Plain password

4 Answers4

Linked