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.
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.
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.
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.
How user passwords are created, reused, and validated.
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.
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
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
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
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 |
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
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.
Looking to innovate through PostgreSQL?