CODEWITHSUNDEEP
postgresqlmd5role
RalphShnelvar
RalphShnelvar

Reputation: 607

Postgres: MD5 Password / Plain password

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 "[email protected]"

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

What am I not understanding?

Upvotes: 14

Views: 48109

Answers (5)

Dominic Mayhew
Dominic Mayhew

Reputation: 1

When the password hash method is set to SCRAM-SHA-256, the prefix is "SCRAM-SHA-256$4096:".

For example, if the password is "password", the hashed value is 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8. The SQL statement,

CREATE ROLE role_name PASSWORD 'SCRAM-SHA-256$4096:5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8';

will create user role_name with password "password".

Upvotes: 0

Ankur Agarwal
Ankur Agarwal

Reputation: 24768

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

Upvotes: 0

RCross
RCross

Reputation: 5118

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())

Upvotes: 36

RalphShnelvar
RalphShnelvar

Reputation: 607

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.

Upvotes: -1

zerkms
zerkms

Reputation: 254926

Postgresql hashed passwords have md5 prefix:

md548503dfd58720bd5ff35c102065a52d7

Upvotes: 3

Related Questions