PostgreSQL: Best way for Password Encryption using pgcrypto’s Cryptographic functions

This is going to be one of most important article for the PostgreSQL community because I am going to share, what should be our best practice to store encrypted passwords into PostgreSQL Database Server.

I am sharing some of the important pgcrypto’s cryptographic functions for PostgreSQL password encryption.

Generally, people are using MD5 and SHA algorithm for password encryption, but both are easy to break and vulnerable, so we should not use this in our general practice.

The PostgreSQL provides pgcrypto module or extension with the set of cryptography functions.

First, We need to install the pgcrypto extension in PostgreSQL.

Below is a list of hashing algorithm along with its require bit size.

  • MD5 = 128-bit hash value.
  • SHA1 = 160-bit hash value.
  • SHA224 = 224-bit hash value.
  • SHA256 = 256-bit hash value.
  • SHA384 = 384-bit hash value.
  • SHA512 = 512-bit hash value.


Other Important Hashing Functions:

digest() :
It computes a binary hash of the given data. It supports all standard algorithms like: md5, sha1, sha224, sha256, sha384 and sha512.
We can use ENCODE() to convert a binary string into hexadecimal.

hmac() :
It calculates hashed MAC for data with the key. This is similar to digest(), but more secure because it requires the key.We can use ENCODE() to convert a binary string into hexadecimal.

crypt() and gen_salt() :
This is more secure than any other cryptographic functions.
This is specially designed for hashing passwords.
crypt() does the hashing and gen_salt() prepare algorithm parameters for it.

Generally, when we are using another algorithm like md5(), it generates a same string for same password text.

The most important point is,
It generates a random string even for same password text.

Supported algorightms for crypt():
Reference taken from the PostgreSQL official document.

PostgreSQL Gen_Salt Algorithms

Below is a small demonstration of this:

Create a sample table:

Insert a sample encrypted passwords:

Check the result and identify different string for even a same password:

PostgreSQL Crypto Password

Check and authenticate given password:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

1 Comment on "PostgreSQL: Best way for Password Encryption using pgcrypto’s Cryptographic functions"

Notify of

Sort by:   newest | oldest | most voted
2 days 10 hours ago

Very useful information.
Thanx a lot.