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.
No comments