How to get a md5 password for a user on PostgreSQL? 如何在PostgreSQL上为用户获取md5密码？

If you look through table pg_authid on PostgreSQL, you can find the values of column rolpassword are hashed strings. For example, a string like "md58d11c0b8417d3947cb544f1174fd44ce".

How to get rolpassword? The formula is:

"md5" + md5(password + username)

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

PostgreSQL:

# select 'md5' || md5('password123' || 'admin')md53f84a3c26198d9b94054ca7a3839366d

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

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

 

Reference

1. RCross. Answer: Postgres: MD5 Password / Plain password. Nov 21 '19 at 16:40