How to Implement the Password Verify Function in a EDB PostgreSQL Advanced Server Database

Security is the measure role of the database in terms of data and user level. One of the most important security concerns is a user’s  password, which should be both confidential and complex. In this post, I’ll explain and implement the procedure to create a complex password verify function. The profile feature isn’t available in the open-source community version of PostgreSQL. However, there’s an option to implement a password verify function in EnterpriseDB (EDB) PostgreSQL Advanced Server, which allows users to generate strong and complex passwords. 
 
 
 

What is EDB?

EnterpriseDB (EDB) provides software and services based on the open-source database PostgreSQL, and is one of the largest contributors to PostgreSQL. EDB provides flexibility in that it offers many features that can help achieve high availability, disaster recovery, multi-master replication, bi-directional replication, and even multiple customize options to create extensions.

What is the purpose of the Role feature in the database?

The roles act as templates that help assign permissions to a database user. Each role has a pre-defined or customized set of privileges that are granted to a database user account with this role. You can edit sets of privileges that correspond to different roles.

What is the purpose of the Profile feature in the database?

A profile is a database object that can help restrict database usage by a system user, and even restrict users from performing operations that exceed reasonable resource utilization.

What are Enforce password practices?

How user passwords are created, reused, and validated.

Password verify functions

The Password Verify Functions value specifies a PL/SQL function to be used for password verification when users assign this profile log into a database. This function can be used to validate password strength by requiring passwords to pass a strength test written in PL/SQL.

Complexity check exceptions

This function verifies the complexity of a password string. Besides the password string, it accepts a few other values to describe the complexity to define custom password
verify functions.

chars – All characters (i.e. string length)
letter – Alphabetic characters A-Z and a-z
upper – Uppercase letters A-Z
lower – Lowercase letters a-z
digit – Numeric characters 0-9
special – All characters not in A-Z, a-z, 0-9 except DOUBLE QUOTE which is a password delimiter

Verify Password Function

It enforces stronger password complexity in EDB PostgreSQL.
Exception – 1: Password doesn’t include username
Exception – 2: Must Contains 2 lowercase
Exception – 3: Must Contains 2 Uppercase
Exception – 4: Must Contains 2 Digits
Exception – 5: Must Contains 2 special Characters

Step 1: Create Function To Verify Password

edb=# CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar2)
edb-# RETURNS boolean IMMUTABLE
edb-#  LANGUAGE plpgsql
edb-# AS $function$
edb$# BEGIN
edb$#   IF (length(new_password) < 10)
edb$#   THEN
edb$#    -- raise_application_error(-20001, 'too short');
edb$#     RAISE EXCEPTION  'too short';
edb$#   END IF;
edb$#   IF  new_password= old_password
edb$#   THEN
edb$#    -- raise_application_error(-20002, 'includes old password');
edb$#     RAISE exception 'includes old password';
edb$#   END IF;
edb$#    IF substring(upper(new_password) FROM upper(user_name)) IS NOT NULL
edb$#   THEN
edb$#    -- raise_application_error(-20003, 'New passowrd not allowed to include username');
edb$#    RAISE exception 'New password not be allowed to contain username';
edb$#   END IF;
edb$#    IF (SELECT count(*)
edb$#      from regexp_matches(new_password,'[a-z]','g')   )<2
edb$#   THEN
edb$#    -- raise_application_error(-20004, 'Must be at least TWO lowercase character');
edb$#    RAISE exception 'Must be at least 2 lowercase characters';
edb$#   END IF;
edb$#    IF (SELECT count(*)
edb$#      from regexp_matches(new_password,'[A-Z]','g')   )<2
edb$#   THEN
edb$#    -- raise_application_error(-20005, 'Must be at least TWO uppercase character');
edb$#    RAISE exception 'Must be at least 2 uppercase characters';
edb$#   END IF;
edb$#   IF (SELECT count(*)
edb$#      from regexp_matches(new_password,'[0-9]','g')   )<2
edb$#   THEN
edb$#    -- raise_application_error(-20006, 'Must be at least TWO digit');
edb$#    RAISE exception 'Must be at least 2 digits';
edb$#   END IF;
edb$#   IF (SELECT count(*)
edb$#      from regexp_matches(new_password,'[^A-Za-z0-9]','g')   )<2
edb$#   THEN
edb$#    -- raise_application_error(-20006, 'Must be at least TWO special character');
edb$#     RAISE exception  'Must be at least 2 special characters';
edb$#   END IF;
edb$# return 1;
edb$# END;
edb$# $function$
edb-# ;
CREATE FUNCTION

Step 2: Check function using meta command

edb=# \df sys.verify_password
                                                                List of functions
 Schema |      Name       | Result data type |                                     Argument data types                                     | Type
--------+-----------------+------------------+---------------------------------------------------------------------------------------------+------
 sys    | verify_password | boolean          | user_name character varying, new_password character varying, old_password character varying | func
(1 row)

edb=# \df+ sys.verify_password
List of functions
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------
Schema              | sys
Name                | verify_password
Result data type    | boolean
Argument data types | user_name character varying, new_password character varying, old_password character varying
Type                | func
Volatility          | immutable
Parallel            | unsafe
Owner               | enterprisedb
Security            | invoker
Access privileges   |
Language            | plpgsql
Source code         |                                                                                            +
                    | BEGIN                                                                                      +
                    |   IF (length(new_password) < 10)                                                           +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20001, 'too short');                                        +
                    |     RAISE EXCEPTION  'too short';                                                          +
                    |   END IF;                                                                                  +
                    |   IF  new_password= old_password                                                           +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20002, 'includes old password');                            +
                    |     RAISE exception 'includes old password';                                               +
                    |   END IF;                                                                                  +
                    |    IF substring(upper(new_password) FROM upper(user_name)) IS NOT NULL                     +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20003, 'New passowrd not allowed to include username');     +
                    |    RAISE exception 'New password not be allowed to contain username';                      +
                    |   END IF;                                                                                  +
                    |    IF (SELECT count(*)                                                                     +
                    |      from regexp_matches(new_password,'[a-z]','g')   )<2                                   +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20004, 'Must be at least TWO lowercase character');         +
                    |    RAISE exception 'Must be at least 2 lowercase characters';                              +
                    |   END IF;                                                                                  +
                    |    IF (SELECT count(*)                                                                     +
                    |      from regexp_matches(new_password,'[A-Z]','g')   )<2                                   +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20005, 'Must be at least TWO uppercase character');         +
                    |    RAISE exception 'Must be at least 2 uppercase characters';                              +
                    |   END IF;                                                                                  +
                    |   IF (SELECT count(*)                                                                      +
                    |      from regexp_matches(new_password,'[0-9]','g')   )<2                                   +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20006, 'Must be at least TWO digit');                       +
                    |    RAISE exception 'Must be at least 2 digits';                                            +
                    |   END IF;                                                                                  +
                    |   IF (SELECT count(*)                                                                      +
                    |      from regexp_matches(new_password,'[^A-Za-z0-9]','g')   )<2                            +
                    |   THEN                                                                                     +
                    |    -- raise_application_error(-20006, 'Must be at least TWO special character');           +
                    |     RAISE exception  'Must be at least 2 special characters';                              +
                    |   END IF;                                                                                  +
                    | return 1;                                                                                  +
                    | END;                                                                                       +
                    |
Description         |

Step 3: Create Profile

edb=# CREATE PROFILE EDB limit FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 3 PASSWORD_REUSE_TIME 180 PASSWORD_REUSE_MAX 5 PASSWORD_VERIFY_FUNCTION verify_password;
CREATE PROFILE

Step 4: Let’s Verify Password Exception

edb=# create user athar password 'pythian' profile EDB;
ERROR:  too short
CONTEXT:  PL/pgSQL function verify_password(character varying,character varying,character varying) line 6 at RAISE    ====> Exception 1

edb=# create user athar password 'atharfahad' profile EDB;
ERROR:  New password not be allowed to contain username
CONTEXT:  PL/pgSQL function verify_password(character varying,character varying,character varying) line 16 at RAISE   =====> Exception 2

edb=# create user athar password 'pythiandbconsultant' profile EDB;
ERROR:  Must be at least 2 uppercase characters
CONTEXT:  PL/pgSQL function verify_password(character varying,character varying,character varying) line 28 at RAISE   =====> Exception 4

edb=# create user athar password 'PYthiandbconsultant' profile EDB;
ERROR:  Must be at least 2 digits
CONTEXT:  PL/pgSQL function verify_password(character varying,character varying,character varying) line 34 at RAISE   =====> Exception 5

edb=# create user athar password 'PYthiandbconsultant12' profile EDB;
ERROR:  Must be at least 2 special characters
CONTEXT:  PL/pgSQL function verify_password(character varying,character varying,character varying) line 40 at RAISE   =====> Exception 6

edb=# create user athar password 'PYthiandbconsultant12#$' profile EDB;    ==== Done all complex check
CREATE ROLE

I hope you found this post helpful. Feel free to drop questions in the comments section, and sign up for the next post.