Enhance PostgreSQL database security using hooks with Trusted Language Extensions

PostgreSQL has the ability to authenticate user names and passwords using native credentials, though it lacks the ability to enforce specific password complexity and other advanced authentication policies without external identity services like LDAP or Kerberos. Trusted Language Extensions (TLE) for PostgreSQL (pg_tle), an open-source development kit used to build and package extensions, lets you add the above functionality currently missing from the PostgreSQL core using the clientauth hook. Supported trusted languages which can be leveraged using TLE include PL/pgSQL, PL/v8, and PL/Rust.

At the time of launch, TLE supported password-check hooks, which you can use to build a custom password complexity check function and wrap it in an extension. The version of TLE referenced in this post, pg_tle v1.4.0, available on database instances in Amazon Relational Database Service (Amazon RDS) for PostgreSQL 16.2-R2, 15.6-R2, 14.11-R2, and 13.14-R2. and higher, added support for the “client authentication” hook, which runs after a user attempts authentication, and an improvement to the existing password checking hook.

In this post, we walk you through the enhancements made to hooks in TLE and show an example of how to create a client authentication hook.

What are the hooks in PostgreSQL?

Hooks are event-based functions used during various database operations that modify the way queries are run and how the PostgreSQL database reacts. There are various types of hooks—general hooks, planner hooks, executor hooks, and more. Hooks are used by many PostgreSQL extensions today. For example, the pg_stat_statements extension, which is used to track performance statistics for queries, uses ExecutorStart_hook, ExecutorRun_hook, and ExecutorFinish_hook to get the runtime information of a query.

Let’s take a look at the recent hooks and features supported with TLE.

Support for the cluster-wide passcheck hook

TLE supports the passcheck hook, which lets you build hooks that support your team’s password compliance requirements. You can create a custom function and provide additional validation rules on the user-supplied passwords. For more details on how to build a TLE to validate user passwords, refer to New – Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS. The passcheck hook, by default, runs only in the current database where it is registered. You need to explicitly register the hook in every database where you want to use this feature.

In pg_tle version 1.4.0 and higher, you can set the parameter pgtle.passcheck_db_name to control the cluster-wide settings of the passcheck hook in a single database. When this parameter is set, the registered functions in the specified database run across all the databases in the cluster. However, the passcheck functions runs as the database superuser (in Amazon RDS, this is rds_superuser), so we define the functions keeping in mind the security posture of code executed with the privileges of a database superuser. When using Amazon RDS, you can use the AWS Management Console or AWS Command Line Interface (AWS CLI) to enable the passcheck hook to take effect across all the databases. To enable passcheck, you need to set pgtle.enable_password_check to on or require. You can use the following command to enable the parameter via the AWS CLI:

aws rds modify-db-parameter-group \
   --region us-east-1 \
   --db-parameter-group-name pgtle-pg \
   --parameters "ParameterName=pgtle.passcheck_db_name, ParameterValue=name_of_database,ApplyMethod=immediate"

Support for the client authentication hook

TLE now also supports the client authentication hook, which gives you an additional control over the authentication process. The clientauth hook can be used in this case where we need the capability to keep track of authentication failures, and lock out users after a number of failed login attempts. This helps mitigate various attacks, such as dictionary attacks where hackers run password hash files that look for common words in dictionaries used as passwords. You can enable the client authentication hook in your RDS Postgres instance using the following command:

aws rds modify-db-parameter-group \
    --region us-east-1 \
    --db-parameter-group-name pgtle-pg \
    --parameters "ParameterName=pgtle.enable_clientauth,ParameterValue=on,ApplyMethod=immediate"

While pgtle.enable_clientauth is a dynamic parameter, enabling it does require a reboot. Let’s restart the DB cluster to enable this parameter:

aws rds reboot-db-instance\
    --region us-east-1 \
    --db-instance-identifier pg-tle-is-fun

To confirm that the hook is enabled, you can run the following command on the terminal:

SHOW pgtle.enable_clientauth;

This should produce the following output:

pgtle.enable_clientauth
-----------------------------
 on

The following code shows how you can write a sample function to lock out a user after a certain number of failed login attempts (in this example, five):

CREATE EXTENSION IF NOT EXISTS pg_tle;

SELECT pgtle.install_extension(
    'client_lockout',
    '1.0',
    'Lock out users after 5 consecutive failed login attempts',
$_pgtle_$
    CREATE SCHEMA client_lockout;

    CREATE TABLE client_lockout.failed_attempts (
        user_name           text    PRIMARY KEY,
        num_failed_attempts integer
    );

    CREATE FUNCTION client_lockout.hook_function(port pgtle.clientauth_port_subset, status integer)
    RETURNS void AS $$
        DECLARE
            num_attempts integer;
        BEGIN
            -- Get number of consecutive failed attempts by this user
            SELECT COALESCE(num_failed_attempts, 0) FROM client_lockout.failed_attempts
                WHERE user_name = port.user_name
                INTO num_attempts;

            -- If at least 5 consecutive failed attempts, reject
            IF num_attempts >= 5 THEN
                RAISE EXCEPTION '% has failed 5 or more times consecutively, please contact the database administrator', port.user_name;
            END IF;

            -- If password is wrong, increment counter
            IF status = -1 THEN
                INSERT INTO client_lockout.failed_attempts (user_name, num_failed_attempts)
                    VALUES (port.user_name, 1)
                    ON CONFLICT (user_name) DO UPDATE SET num_failed_attempts = client_lockout.failed_attempts.num_failed_attempts + 1;
            END IF;

            -- If password is right, reset counter to 0
            IF status = 0 THEN
                INSERT INTO client_lockout.failed_attempts (user_name, num_failed_attempts)
                    VALUES (port.user_name, 0)
                    ON CONFLICT (user_name) DO UPDATE SET num_failed_attempts = 0;
            END IF;
        END
    $$ LANGUAGE plpgsql;

    -- Allow extension owner to reset the password attempts of any user to 0
    CREATE FUNCTION client_lockout.reset_attempts(target_user_name text)
    RETURNS void AS $$
        BEGIN
            INSERT INTO client_lockout.failed_attempts (user_name, num_failed_attempts)
                VALUES (target_user_name, 0)
                ON CONFLICT (user_name) DO UPDATE SET num_failed_attempts = 0;
        END
    $$ LANGUAGE plpgsql;

    SELECT pgtle.register_feature('client_lockout.hook_function', 'clientauth');

    REVOKE ALL ON SCHEMA client_lockout FROM PUBLIC;
$_pgtle_$
);

CREATE EXTENSION client_lockout;

Now the hook is active. If we supply the wrong password five times, the connection will fail:

$ psql -d postgres -U tle_user
Password for user test:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  tle_user has failed 5 or more times consecutively, please contact the database administrator

To unlock the user, use this command:

postgres=# select client_lockout.reset_attempts('tle_user');
 reset_attempts
----------------

(1 row)

Note that if the database client has set the sslmode parameter to allow or prefer, the client will automatically attempt to re-connect if the first connection fails. This will trigger the clientauth function twice and may cause users to be locked out sooner than expected.

Cleanup

Complete the following steps to clean up the extension you created in the database

1. To disable the hook, set the value of pgtle.enable_clientauth to off:

   aws rds modify-db-parameter-group \
       --region us-east-1 \
       --db-parameter-group-name pgtle-pg \
       --parameters "ParameterName=pgtle.enable clientauth, ParameterValue=off,ApplyMethod=immediate"
   

2. Next, drop the extension client_lockout:

   DROP EXTENSION client_lockout;

3. Finally, uninstall the extension:

   SELECT pgtle.uninstall_extension('client_lockout');

Conclusion

In this post, we reviewed the enhancements made to hooks in TLE and demonstrated how to create a client authentication hook. While core PostgreSQL has the ability to authenticate user names and passwords using native credentials, it lacks the ability to enforce specific password complexity and other advanced authentication policies without external identity services like LDAP or Kerberos. Using enhanced PostgreSQL database hooks with TLE allows you to create these types of authentication policies without external providers, as demonstrated in this post.

We welcome your comments and feedback in the comments section.

About the Authors