Announcing Credcheck extension to enforce username and password checks in PostgreSQL

PostgreSQL includes robust security standards around all aspects such as authentication, authorization, accounting and auditing. User management and role based segregation that satisfy enterprise standards can be implemented at ease. Similarly, organizations may be willing to enforce certain compliance around the password standards such as the minimum upper case characters or minimum number of numbers and special characters and the length of the password. Such username password checks are important in an environment where strong security policies are a must. In this Article, we shall discuss how we could use the extension credcheck to enforce username and password standards in PostgreSQL. 
                                                                                                                                                                                                                          credcheck extension to enforce username and password checks in PostgreSQL

While such a username or password rule is not available by default, there exists a hook in PostgreSQL called check_password_hook to enforce such policies. PostgreSQL Community has luckily helped us with the template passwordcheck, that can be used to modify the source code as per our needs. We have used the same module as a template and created this extension called credcheck that enforces the following standards.

Check Type Description Setting Value Accepted Not Accepted
username_min_length username minimum length of a username 4 ✓ abcd ✘ abc
username_min_special username minimum number of special characters 1 ✓ a@bc ✘ abcd
username_min_digit username minimum number of digits 1 ✓ a1bc ✘ abcd
username_min_upper username minimum number of upper case 2 ✓ aBC ✘ aBc
username_min_lower username minimum number of lower case 1 ✓ aBC ✘ ABC
username_min_repeat username maximum number of times a character should repeat 2 ✓ aaBCa ✘ aaaBCa
username_contain_password username username should not contain password on ✓ username - password ✘ username + password
username_contain username username should contain one of these characters a,b,c ✓ ade ✘ efg
username_not_contain username username should not contain one of these characters x,y,z ✓ ade ✘ axf
username_ignore_case username ignore case while performing the above checks on ✓ Ade ✘ aXf
password_min_length password minimum length of a password 4 ✓ abcd ✘ abc
password_min_special password minimum number of special characters 1 ✓ a@bc ✘ abc
password_min_digit password minimum number of digits in a password 1 ✓ a1bc ✘ abc
password_min_upper password minimum number of uppercase characters 1 ✓ Abc ✘ abc
password_min_lower password minimum number of lowercase characters 1 ✓ aBC ✘ ABC
password_min_repeat password maximum number of times a character should repeat 2 ✓ aab ✘ aaab
password_contain_username password password should not contain password on ✓ password - username ✘ password + username
password_contain password password should contain these characters a,b,c ✓ ade ✘ xfg
password_not_contain password password should not contain these characters x,y,z ✓ abc ✘ axf
password_ignore_case password ignore case while performing above checks on ✓ Abc ✘ aXf
Prerequisite
  1. This extension is supported on PostgreSQL 10 and above versions only.
  2. We must also have the devel package for compiling and installing the extension.
    postgresql-server-dev-<ver> in Ubuntu/Debian
    Or
    postgresql<ver>-devel in RedHat family OS

    Example

    $ sudo apt install postgresql-server-dev-13
    or
    $ sudo yum install postgresql13-devel
  3. Make sure pg_config binary is set in the current OS path.

    Example

    export PATH=/usr/lib/postgresql/13/bin:$PATH
  4. Should have make and gcc installed, for compiling this extension.
    $ sudo apt install make gcc
Installation of credcheck extension

We can use the following installation steps as example to install this extension.

  1. Clone or download this repository.
    $ git clone https://github.com/MigOpsRepos/credcheck
  2. Run “make install”. To fix permission issues, run the same command with “sudo” privilege
    cd credcheck
    sudo make install
  3. Create the extension using the following command. It is sufficient to create this extension in any one of the databases in the cluster. We need not create this extension in all the databases to enforce username and password checks.
    CREATE EXTENSION credcheck;
  4. Enable this extension by appending “credcheck” to “shared_preload_libraries” postgresql parameter.
    ALTER SYSTEM SET shared_preload_libraries TO 'credcheck';
  5. Changes to shared_preload_libraries require a restart of the PostgreSQL cluster.
    sudo systemctl restart postgresql-13
Enforcing User and Password standards

By default, the credcheck extension will not impose any critical checks unless the appropriate settings have been enabled in postgresql.auto.conf file.

The Credcheck extension will provide two sets of parameters (username checks and password checks). By enabling these parameters, we can control the required number of checks on the username as well as password.

For example, consider the below use case, where a password string should not contain any repeated characters.
To perform this check, let us set "credcheck.password_min_repeat" to 1. That means, every character should repeat at most once. 

postgres=# SET credcheck.password_min_repeat TO 1;
SET
postgres=# CREATE USER weak WITH PASSWORD 'week';
ERROR:  password characters are repeated more than the configured credcheck.password_min_repeat times

Here, the password specified as the word : “week” the character “e” is repeated 2 times, which is violating the credcheck condition. That is the reason, the user creation failed. Let us fix this with non repeatable characters as below.

postgres=# CREATE USER weak WITH PASSWORD 'wek';
CREATE ROLE

Let us test one more use case where passwords do not contain username. To enforce this check, let us enable the parameter "credcheck.password_contain_username".

postgres=# SET credcheck.password_contain_username TO on;
SET
postgres=# CREATE USER day WITH PASSWORD 'lastday';
ERROR:  password should not contain username

As you can see, the password “lastday” contains it’s username "day". Let us fix this by providing some other password.

postgres=# CREATE USER day WITH PASSWORD 'monthend';
CREATE ROLE

By using the above combination of checks, we make credentials a little stronger, which brings much better security to the database systems.

Please Note : The above session level settings may not enforce checks globally. We must set these settings in the postgresql.conf or postgresql.auto.conf files. Best method is to set them using the ALTER SYSTEM command followed by a SIGHUP or RELOAD as seen in the following example.

postgres=# ALTER SYSTEM SET credcheck.password_contain_username TO off;
postgres=# select pg_reload_conf();
Limitations

Credcheck extension comes with few limitations as described below.

  1. This extension will not support the checks on non plain text passwords. This means that if any user tries to create credentials with an encrypted password, then it will throw the following error message.

    Example

    postgres=# CREATE USER enc PASSWORD 'md55e4cc86d2d6a8b73bbefc4d5b91baa45';
    ERROR:  password type is not a plain text
  2. Username checks will not be enforced while creating a user without password, and while renaming the user if the user doesn't have a password defined. However, if the user that initially got created without a password is now assigned a password, then, the username check is also applicable while renaming the user. Please see the following example to understand this better.
    Example (username checks won't invoke here).
    postgres=# CREATE USER user1;

    Example (username checks won't invoke here)
    postgres=# ALTER USER user1 RENAME to test_user;

    Example (set a password to the user) - username, password check works here.
    postgres=# ALTER USER user1 WITH PASSWORD 'monthend';

    Example (username checks works here)
    postgres=# ALTER USER user1 RENAME to test_user;

    Example (username, password checks will invoke here and on the rename statement too)
    postgres=# CREATE USER user1 PASSWORD 'this is some plain text';
    CREATE ROLE
    postgres=# ALTER USER user1 RENAME to test_user;

Conclusion

PostgreSQL is highly extensible. While migrating to PostgreSQL, we may compare some of the features that are only seen in Oracle or any DBMS that is currently being used. However, PostgreSQL allows us to incorporate such features including custom requirements at ease. One of such examples is the pg_dbms_errlog extension that has been contributed by MigOps, to emulate the DBMS_ERRLOG package behavior of Oracle, for the purpose of Oracle compatibility with PostgreSQL during Migrations in the Open Source way !

Are you looking for a feature that you do not see in PostgreSQL and it has become a concern during migrations ? Do you need support in migrating to PostgreSQL or managing and tuning PostgreSQL ? Please Contact Us today by filling the following form.

3 thoughts on “Announcing Credcheck extension to enforce username and password checks in PostgreSQL”

  1. Great extension, folks!

    quick syntactical update:
    “`sql
    ALTER SYSTEM shared_preload_libraries TO ‘credcheck’;
    “`
    to be changed to
    “`sql
    ALTER SYSTEM SET shared_preload_libraries TO ‘credcheck’;
    “`

Leave a Comment

Your email address will not be published.

Scroll to Top