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:
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:
While pgtle.enable_clientauth is a dynamic parameter, enabling it does require a reboot. Let’s restart the DB cluster to enable this parameter:
To confirm that the hook is enabled, you can run the following command on the terminal:
This should produce the following output:
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):
Now the hook is active. If we supply the wrong password five times, the connection will fail:
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
To disable the hook, set the value of pgtle.enable_clientauth to off:
Next, drop the extension client_lockout:
Finally, uninstall the extension:
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
Peter Celentano is a Senor Specialist Solutions Architect with Amazon Web Services, focusing on managed PostgreSQL. He works with AWS customers to design scalable, secure, performant, and robust database architectures on the cloud.
Sukhpreet Kaur Bedi is a Senor Database Specialist Solutions Architect with AWS focusing on Amazon RDS/Aurora PostgreSQL engines. She helps customers innovate on the AWS platform by building highly available, scalable, and secure database architectures.
Read MoreAWS Database Blog
