PostgreSQL Tutorial: Check Password Strength

August 21, 2024

Summary: in this tutorial, you will learn how to check password strength in PostgreSQL.

Table of Contents

Introduction

passwordcheck is a PostgreSQL contrib module able to check if raw password strings are able to respect some policies. For encrypted password, which is what should be used in most cases to avoid passing plain text passwords over the wire have limited checks, still it is possible to check for example for MD5-hashed entries if they match the user name. For plain text password, things get a bit more advanced, with the following characteristics:

Minimum length of 8 characters.
Check if password has the user name.
Check if password includes both letters and non-letters.
Optionally use cracklib for more checks.

Note that all those characteristics are decided at compilation time and that it is not possible to configure it, except by forking the code and creating your own module.

PostgreSQL passwordcheck examples

passwordcheck must be enabled via shared_preload_libraries:

SHOW shared_preload_libraries;
 shared_preload_libraries
--------------------------
 passwordcheck
(1 row)

Example usage with passwords provided as plain text:

ALTER ROLE foo PASSWORD 'foo';
ERROR:  password is too short

ALTER ROLE postgres PASSWORD 'postgres';
ERROR:  password must not contain user name

ALTER ROLE foo PASSWORD 'foobar';
ERROR:  password must not contain user name

ALTER ROLE foo PASSWORD 'boobarbaz';
ERROR:  password must contain both letters and nonletters

With passwords provided in encrypted format, only a simple check that the password matches the username is possible:

ALTER ROLE foo ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:eUHVJZ5WSsEomBt9BgHJzQ==$x57IR2ZcoKl8tlz5I2w636bquX3JmCYmi4LlDIa5lIY=:viXV52BLraOFRaw1gCp22K9VgLe6Rvi1nvcWNR6PTe4=';
ERROR:  password must not equal user name

Other passwords will be accepted as-is; here the value foobar (which would be rejected as plain text) is being passed as the encrypted password:

ALTER ROLE foo ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:aiSTMi/J+KkJa9WFMgiahg==$f//pUq3yGJ6E8UbCxJWcGvnylc0aNuZR6WuLAcYSzWA=:yzMz+l1TIAYQ28tdQ2YopKwQiIjMwpGAariL2jmeaxU=';

Setting up cracklib

Install cracklib on a Linux based system:

yum install cracklib cracklib-devel cracklib-dicts

Create the word indexes that get used by CrackLib:

create-cracklib-dict /usr/share/dict/*

This command creates the CrackLib dictionary from the word lists, usually the default dictionary path is /usr/lib/cracklib_dict.

Uncomment two lines in the passwordcheck Makefile and rebuild the module.

# uncomment the following two lines to enable cracklib support
# PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/usr/lib/cracklib_dict"'
# SHLIB_LINK = -lcrack

Install the passwordcheck module again.

Examples of passwordcheck with cracklib

After integrated cracklib into passwordcheck module, let’s do some testing with passwords provided as plain text:

ALTER USER user1 PASSWORD '123';
ERROR:  password is too short

ALTER USER user1 PASSWORD '123456789';
ERROR:  password must contain both letters and nonletters

ALTER USER user1 PASSWORD 'Hello123456789';

ALTER USER user1 PASSWORD 'Hello123456';

ALTER USER user1 PASSWORD 'He123456';
ERROR:  password is easily cracked

ALTER USER user1 PASSWORD 'He123456789';
ERROR:  password is easily cracked

ALTER USER user1 PASSWORD 'He@123456789';
ERROR:  password is easily cracked

ALTER USER user1 PASSWORD 'Hello@123456';