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:

Anvesh Patel

Leave a Reply

2 Comments on "PostgreSQL: Best way for Password Encryption using pgcrypto’s Cryptographic functions"

Notify of
Sort by:   newest | oldest | most voted

Very useful information.
Thanx a lot.

Ashvin patel

nice !!
It’s very useful for me.
Thanks a lot.