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. 
                                                                                                                                                                                                                         

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.

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.