Working with Trusted Language Extensions for PostgreSQL
Trusted Language Extensions for PostgreSQL is an open source development kit for building
PostgreSQL extensions. It allows you to build high performance PostgreSQL extensions
and safely run them on your RDS for PostgreSQL DB instance.
By using Trusted Language Extensions (TLE) for PostgreSQL, you can create PostgreSQL extensions that follow the
documented approach for extending PostgreSQL functionality. For more information, see
Packaging
Related Objects into an Extension
One key benefit of TLE is that you can use it in environments that don't provide access to the file system underlying the PostgreSQL instance. Previously, installing a new extension required access to the file system. TLE removes this constraint. It provides a development environment for creating new extensions for any PostgreSQL database, including those running on your RDS for PostgreSQL DB instances.
TLE is designed to prevent access to unsafe resources for the extensions that you create using TLE. Its runtime environment limits the impact of any extension defect to a single database connection. TLE also gives database administrators fine-grained control over who can install extensions, and it provides a permissions model for running them.
TLE is supported on RDS for PostgreSQL version 14.5
and higher versions. The Trusted Language Extensions development environment and runtime are packaged as
the pg_tle
PostgreSQL extension, version 1.0.1. It supports creating extensions
in JavaScript, Perl, Tcl, PL/pgSQL, and SQL.
You install the pg_tle
extension in your RDS for PostgreSQL DB instance in the same way that you
install other PostgreSQL extensions. After the pg_tle
is set up, developers can
use it to create new PostgreSQL extensions, known as TLE extensions.
In the following topics, you can find information about how to set up Trusted Language Extensions and how to get started creating your own TLE extensions.
Topics
- Terminology
- Requirements for using Trusted Language Extensions for PostgreSQL
- Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance
- Overview of Trusted Language Extensions for PostgreSQL
- Creating TLE extensions for RDS for PostgreSQL
- Dropping your TLE extensions from a database
- Uninstalling Trusted Language Extensions for PostgreSQL
- Using PostgreSQL hooks with your TLE extensions
- Functions reference for Trusted Language Extensions for PostgreSQL
- Hooks reference for Trusted Language Extensions for PostgreSQL
Terminology
To help you better understand Trusted Language Extensions, view the following glossary for terms used in this topic.
- Trusted Language Extensions for PostgreSQL
-
Trusted Language Extensions for PostgreSQL is the official name of the open source development kit that's packaged as the
pg_tle
extension. It's available for use on any PostgreSQL system. For more information, see aws/pg_tleon GitHub. - Trusted Language Extensions
-
Trusted Language Extensions is the short name for Trusted Language Extensions for PostgreSQL. This shortened name and its abbreviation (TLE) are also used in this documentation.
- trusted language
-
A trusted language is a programming or scripting language that has specific security attributes. For example, trusted languages typically restrict access to the file system, and they limit use of specified networking properties. The TLE development kit is designed to support trusted languages. PostgreSQL supports several different languages that are used to create trusted or untrusted extensions. For an example, see Trusted and Untrusted PL/Perl
in the PostgreSQL documentation. When you create an extension using Trusted Language Extensions, the extension inherently uses trusted language mechanisms. - TLE extension
-
A TLE extension is a PostgreSQL extension that's been created by using the Trusted Language Extensions (TLE) development kit.
Requirements for using Trusted Language Extensions for PostgreSQL
Use the following requirements for setting up and using the TLE development kit.
RDS for PostgreSQL 14.5 or higher version – Trusted Language Extensions is supported on RDS for PostgreSQL version 14.5 and higher releases only.
If you need to upgrade your RDS for PostgreSQL instance, see Upgrading the PostgreSQL DB engine for Amazon RDS.
If you don't yet have an Amazon RDS DB instance running PostgreSQL, you can create one. For more information, see RDS for PostgreSQL DB instance, see Creating and connecting to a PostgreSQL DB instance.
Requires
rds_superuser
privileges – To set up and configure thepg_tle
extension, your database user role must have the permission of therds_superuser
role. By default, this role is granted to thepostgres
user that creates the RDS for PostgreSQL DB instance.Requires a custom DB parameter group – Your RDS for PostgreSQL DB instance must be configured with a custom DB parameter group.
If your RDS for PostgreSQL DB instance isn't configured with a custom DB parameter group, you should create one and associate it with your RDS for PostgreSQL DB instance. For a short summary of steps, see Creating and applying a custom DB parameter group.
If your RDS for PostgreSQL DB instance is already configured using a custom DB parameter group, you can set up Trusted Language Extensions. For details, see Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
Creating and applying a custom DB parameter group
Use the following steps to create a custom DB parameter group and configure your RDS for PostgreSQL DB instance to use it.
To create a custom DB parameter group and use it with your RDS for PostgreSQL DB instance
Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/
. Choose Parameter groups from the Amazon RDS menu.
Choose Create parameter group.
In the Parameter group details page, enter the following information.
For Parameter group family, choose postgres14.
For Type, choose DB Parameter Group.
For Group name, give your parameter group a meaningful name in the context of your operations.
For Description, enter a useful description so that others on your team can easily find it.
Choose Create. Your custom DB parameter group is created in your Amazon Web Services Region. You can now modify your RDS for PostgreSQL DB instance to use it by following the next steps.
Choose Databases from the Amazon RDS menu.
Choose the RDS for PostgreSQL DB instance that you want to use with TLE from among those listed, and then choose Modify.
In the Modify DB instance settings page, find Database options in the Additional configuration section and choose your custom DB parameter group from the selector.
Choose Continue to save the change.
Choose Apply immediately so that you can continue setting up the RDS for PostgreSQL DB instance to use TLE.
To continue setting up your system for Trusted Language Extensions, see Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
For more information working with DB parameter groups, see Working with DB parameter groups.
You can avoid specifying the --region
argument when you use CLI commands by configuring your Amazon CLI
with your default Amazon Web Services Region. For more information, see Configuration
basics in the Amazon Command Line Interface User Guide.
To create a custom DB parameter group and use it with your RDS for PostgreSQL DB instance
Use the create-db-parameter-group Amazon CLI command to create a custom DB parameter group based on postgres14 for your Amazon Web Services Region.
For Linux, macOS, or Unix:
aws rds create-db-parameter-group \ --region
aws-region
\ --db-parameter-group-namecustom-params-for-pg-tle
\ --db-parameter-group-family postgres14 \ --description "My custom DB parameter group for Trusted Language Extensions"For Windows:
aws rds create-db-parameter-group ^ --region
aws-region
^ --db-parameter-group-namecustom-params-for-pg-tle
^ --db-parameter-group-family postgres14 ^ --description "My custom DB parameter group for Trusted Language Extensions"Your custom DB parameter group is available in your Amazon Web Services Region, so you can modify RDS for PostgreSQL DB instance to use it.
Use the modify-db-instance Amazon CLI command to apply your custom DB parameter group to your RDS for PostgreSQL DB instance. This command immediately reboots the active instance.
For Linux, macOS, or Unix:
aws rds modify-db-instance \ --region
aws-region
\ --db-instance-identifieryour-instance-name
\ --db-parameter-group-namecustom-params-for-pg-tle
\ --apply-immediatelyFor Windows:
aws rds modify-db-instance ^ --region
aws-region
^ --db-instance-identifieryour-instance-name
^ --db-parameter-group-namecustom-params-for-pg-tle
^ --apply-immediately
To continue setting up your system for Trusted Language Extensions, see Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
For more information, see Working with parameter groups.
Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance
The following steps assume that your RDS for PostgreSQL DB instance is associated with a custom DB parameter group. You can use the Amazon Web Services Management Console or the Amazon CLI for these steps.
When you set up Trusted Language Extensions in your RDS for PostgreSQL DB instance, you install it in a specific database for use by the database users who have permissions on that database.
To set up Trusted Language Extensions
Perform the following steps using an account that's a member of the rds_superuser
group (role).
Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/
. -
In the navigation pane, choose your RDS for PostgreSQL DB instance.
-
Open the Configuration tab for your RDS for PostgreSQL DB instance. Among the Instance details, find the Parameter group link.
-
Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
-
In the Parameters search field, type
shared_pre
to find theshared_preload_libraries
parameter. -
Choose Edit parameters to access the property values.
-
Add
pg_tle
to the list in the Values field. Use a comma to separate items in the list of values. Reboot the RDS for PostgreSQL DB instance so that your change to the
shared_preload_libraries
parameter takes effect.When the instance is available, verify that
pg_tle
has been initialized. Usepsql
to connect to the RDS for PostgreSQL DB instance, and then run the following command.SHOW shared_preload_libraries;
shared_preload_libraries -------------------------- rdsutils,pg_tle (1 row)
With the
pg_tle
extension initialized, you can now create the extension.CREATE EXTENSION pg_tle;
You can verify that the extension is installed by using the following
psql
metacommand.labdb=>
\dx
List of installed extensions Name | Version | Schema | Description ---------+---------+------------+-------------------------------------------- pg_tle | 1.0.1 | pgtle | Trusted-Language Extensions for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
Grant the
pgtle_admin
role to the primary user name that you created for your RDS for PostgreSQL DB instance when you set it up. If you accepted the default, it'spostgres
.labdb=>
GRANT pgtle_admin TO postgres;GRANT ROLE
You can verify that the grant has occurred by using the
psql
metacommand as shown in the following example. Only thepgtle_admin
andpostgres
roles are shown in the output. For more information, see Understanding the rds_superuser role.labdb=>
\du
List of roles Role name | Attributes | Member of -----------------+---------------------------------+----------------------------------- pgtle_admin | Cannot login | {} postgres | Create role, Create DB +| {rds_superuser,pgtle_admin} | Password valid until infinity |...
Close the
psql
session using the\q
metacommand.\q
To get started creating TLE extensions, see Example: Creating a trusted language extension using SQL.
You can avoid specifying the --region
argument when you use CLI commands by configuring your Amazon CLI
with your default Amazon Web Services Region. For more information, see Configuration
basics in the Amazon Command Line Interface User Guide.
To set up Trusted Language Extensions
Use the modify-db-parameter-group Amazon CLI command to add
pg_tle
to theshared_preload_libraries
parameter.aws rds modify-db-parameter-group \ --db-parameter-group-name
custom-param-group-name
\ --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_tle,ApplyMethod=pending-reboot" \ --regionaws-region
-
Use the reboot-db-instance Amazon CLI command to reboot the RDS for PostgreSQL DB instance and initialize the
pg_tle
library.aws rds reboot-db-instance \ --db-instance-identifier
your-instance
\ --regionaws-region
When the instance is available, you can verify that
pg_tle
has been initialized. Usepsql
to connect to the RDS for PostgreSQL DB instance, and then run the following command.SHOW shared_preload_libraries;
shared_preload_libraries -------------------------- rdsutils,pg_tle (1 row)
With
pg_tle
initialized, you can now create the extension.CREATE EXTENSION pg_tle;
Grant the
pgtle_admin
role to the primary user name that you created for your RDS for PostgreSQL DB instance when you set it up. If you accepted the default, it'spostgres
.GRANT pgtle_admin TO postgres;
GRANT ROLE
Close the
psql
session as follows.labdb=>
\q
To get started creating TLE extensions, see Example: Creating a trusted language extension using SQL.
Overview of Trusted Language Extensions for PostgreSQL
Trusted Language Extensions for PostgreSQL is a PostgreSQL extension that you install in your RDS for PostgreSQL DB instance in the same way that you set up other PostgreSQL
extensions. In the following image of an example database in the pgAdmin client tool,
you can view some of the components that comprise the pg_tle
extension.

You can see the following details.
-
The Trusted Language Extensions (TLE) for PostgreSQL development kit is packaged as the
pg_tle
extension. As such,pg_tle
is added to the available extensions for the database in which it's installed. TLE has its own schema,
pgtle
. This schema contains helper functions (3) for installing and managing the extensions that you create.TLE provides over a dozen helper functions for installing, registering, and managing your extensions. To learn more about these functions, see Functions reference for Trusted Language Extensions for PostgreSQL.
Other components of the pg_tle
extension include the following:
The
pgtle_admin
role – Thepgtle_admin
role is created when thepg_tle
extension is installed. This role is privileged and should be treated as such. We strongly recommend that you follow the principle of least privilege when granting thepgtle_admin
role to database users. In other words, grant thepgtle_admin
role only to database users that are allowed to create, install, and manage new TLE extensions, such aspostgres
.The
pgtle.feature_info
table – Thepgtle.feature_info
table is a protected table that contains information about your TLEs, hooks, and the custom stored procedures and functions that they use. If you havepgtle_admin
privileges, you use the following Trusted Language Extensions functions to add and update that information in the table.
Creating TLE extensions for RDS for PostgreSQL
You can install any extensions that you create with TLE
in any
RDS for PostgreSQL DB instance that has the
pg_tle
extension installed. The pg_tle
extension is scoped
to the PostgreSQL database in which it's installed. The extensions that you create using TLE are scoped to the
same database.
Use the various pgtle
functions to install the code that makes up your TLE
extension. The following Trusted Language Extensions functions all require the pgtle_admin
role.
Example: Creating a trusted language extension using SQL
The following example shows you how to create a TLE extension named pg_distance
that
contains a few SQL functions for calculating distances using different formulas.
In the listing, you can find the function for calculating the Manhattan distance and the function
for calculating the Euclidean distance. For more information about the difference between these formulas, see
Taxicab geometry
You can use this example in your own
RDS for PostgreSQL DB instance
if you have the pg_tle
extension set up as detailed in
Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
Note
You need to have the privileges of the pgtle_admin
role to follow this procedure.
To create the example TLE extension
The following steps use an example database named labdb
. This
database is owned by the postgres
primary user. The
postgres
role also has the permissions of the
pgtle_admin
role.
Use
psql
to connect to RDS for PostgreSQL DB instance.psql --host=
db-instance-123456789012
.aws-region
.rds.amazonaws.com --port=5432
--username=postgres
--password --dbname=labdbCreate a TLE extension named
pg_distance
by copying the following code and pasting it into yourpsql
session console.SELECT pgtle.install_extension ( 'pg_distance', '0.1', 'Distance functions for two points', $_pg_tle_$ CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int) RETURNS float8 AS $$ SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm); $$ LANGUAGE SQL; CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8) RETURNS float8 AS $$ SELECT dist(x1, y1, x2, y2, 1); $$ LANGUAGE SQL; CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8) RETURNS float8 AS $$ SELECT dist(x1, y1, x2, y2, 2); $$ LANGUAGE SQL; $_pg_tle_$ );
You see the output, such as the following.
install_extension --------------- t (1 row)
The artifacts that make up the
pg_distance
extension are now installed in your database. These artifacts include the control file and the code for the extension, which are items that need to be present so that the extension can be created using theCREATE EXTENSION
command. In other words, you still need to create the extension to make its functions available to database users.To create the extension, use the
CREATE EXTENSION
command as you do for any other extension. As with other extensions, the database user needs to have theCREATE
permissions in the database.CREATE EXTENSION pg_distance;
To test the
pg_distance
TLE extension, you can use it to calculate the Manhattan distancebetween four points. labdb=>
SELECT manhattan_dist(1, 1, 5, 5);
8
To calculate the Euclidean distance
between the same set of points, you can use the following. labdb=>
SELECT euclidean_dist(1, 1, 5, 5);
5.656854249492381
The pg_distance
extension loads the functions in the database and makes
them available to any users with permissions on the database.
Modifying your TLE extension
To improve query performance for the functions packaged in this TLE extension, add the following two PostgreSQL attributes to their specifications.
IMMUTABLE
– TheIMMUTABLE
attribute ensures that the query optimizer can use optimizations to improve query response times. For more information, see Function Volatility Categoriesin the PostgreSQL documentation. PARALLEL SAFE
– ThePARALLEL SAFE
attribute is another attribute that allows PostgreSQL to run the function in parallel mode. For more information, see CREATE FUNCTIONin the PostgreSQL documentation.
In the following example, you can see how the
pgtle.install_update_path
function is used to add these attributes
to each function to create a version 0.2
of the
pg_distance
TLE extension. For more information about this
function, see pgtle.install_update_path. You need to have the pgtle_admin
role to
perform this task.
To update an existing TLE extension and specify the default version
Connect to RDS for PostgreSQL DB instance using
psql
or another client tool, such as pgAdmin.psql --host=
db-instance-123456789012
.aws-region
.rds.amazonaws.com --port=5432
--username=postgres
--password --dbname=labdbModify the existing TLE extension by copying the following code and pasting it into your
psql
session console.SELECT pgtle.install_update_path ( 'pg_distance', '0.1', '0.2', $_pg_tle_$ CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int) RETURNS float8 AS $$ SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm); $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8) RETURNS float8 AS $$ SELECT dist(x1, y1, x2, y2, 1); $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8) RETURNS float8 AS $$ SELECT dist(x1, y1, x2, y2, 2); $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE; $_pg_tle_$ );
You see a response similar to the following.
install_update_path --------------------- t (1 row)
You can make this version of the extension the default version, so that database users don't have to specify a version when they create or update the extension in their database.
To specify that the modified version (version 0.2) of your TLE extension is the default version, use the
pgtle.set_default_version
function as shown in the following example.SELECT pgtle.set_default_version('pg_distance', '0.2');
For more information about this function, see pgtle.set_default_version.
With the code in place, you can update the installed TLE extension in the usual way, by using
ALTER EXTENSION ... UPDATE
command, as shown here:ALTER EXTENSION pg_distance UPDATE;
Dropping your TLE extensions from a database
You can drop your TLE extensions by using the DROP EXTENSION
command
in the same way that you do for other PostgreSQL extensions. Dropping the extension
doesn't remove the installation files that make up the extension, which allows
users to re-create the extension. To remove the extension and its installation files, do
the following two-step process.
To drop the TLE extension and remove its installation files
Use
psql
or another client tool to connect to the RDS for PostgreSQL DB instance.psql --host=
.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname
Drop the extension as you would any PostgreSQL extension.
DROP EXTENSION
your-TLE-extension
For example, if you create the
pg_distance
extension as detailed in Example: Creating a trusted language extension using SQL, you can drop the extension as follows.DROP EXTENSION pg_distance;
You see output confirming that the extension has been dropped, as follows.
DROP EXTENSION
At this point, the extension is no longer active in the database. However, its installation files and control file are still available in the database, so database users can create the extension again if they like.
If you want to leave the extension files intact so that database users can create your TLE extension, you can stop here.
If you want to remove all files that make up the extension, continue to the next step.
To remove all installation files for your extension, use the
pgtle.uninstall_extension
function. This function removes all the code and control files for your extension.SELECT pgtle.uninstall_extension('
your-tle-extension-name
');For example, to remove all
pg_distance
installation files, use the following command.SELECT pgtle.uninstall_extension('pg_distance');
uninstall_extension --------------------- t (1 row)
Uninstalling Trusted Language Extensions for PostgreSQL
If you no longer want to create your own TLE extensions using TLE, you
can drop the pg_tle
extension and remove all artifacts. This action
includes dropping any TLE extensions in the database and dropping the pgtle
schema.
To drop the pg_tle
extension and its schema from a database
Use
psql
or another client tool to connect to the RDS for PostgreSQL DB instance.psql --host=
.111122223333
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname
Drop the
pg_tle
extension from the database. If the database has your own TLE extensions still running in the database, you need to also drop those extensions. To do so, you can use theCASCADE
keyword, as shown in the following.DROP EXTENSION pg_tle CASCADE;
If the
pg_tle
extension isn't still active in the database, you don't need to use theCASCADE
keyword.Drop the
pgtle
schema. This action removes all the management functions from the database.DROP SCHEMA pgtle CASCADE;
The command returns the following when the process completes.
DROP SCHEMA
The
pg_tle
extension, its schema and functions, and all artifacts are removed. To create new extensions using TLE, go through the setup process again. For more information, see Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
Using PostgreSQL hooks with your TLE extensions
A hook is a callback mechanism available in PostgreSQL that allows developers to call custom functions or other routines during regular database operations. The TLE development kit supports PostgreSQL hooks so that you can integrate custom functions with PostgreSQL behavior at runtime. For example, you can use a hook to associate the authentication process with your own custom code, or to modify the query planning and execution process for your specific needs.
Your TLE extensions can use hooks. If a hook is global in scope, it applies across all databases. Therefore, if your TLE extension uses a global hook, then you need to create your TLE extension in all databases that your users can access.
When you use the pg_tle
extension to build your own Trusted Language Extensions, you can
use the available hooks from a SQL API to build out the functions of your extension. You
should register any hooks with pg_tle
. For some hooks, you might also need
to set various configuration parameters. For example, the passcode
check
hook can be set to on, off, or require. For more information about specific requirements
for available pg_tle
hooks, see Hooks reference for Trusted Language Extensions for PostgreSQL.
Example: Creating an extension that uses a PostgreSQL hook
The example discussed in this section uses a PostgreSQL hook to check the password provided during specific SQL operations and prevents
database users from setting their passwords to any of those contained in the
password_check.bad_passwords
table. The table contains the top-ten
most commonly used, but easily breakable choices for passwords.
To set up this example in your RDS for PostgreSQL DB instance, you must have already installed Trusted Language Extensions. For details, see Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance.
To set up the password-check hook example
Use
psql
to connect to RDS for PostgreSQL DB instance.psql --host=
db-instance-123456789012
.aws-region
.rds.amazonaws.com --port=5432
--username=postgres
--password --dbname=labdb
Copy the code from the Password-check hook code listing and paste it into your database.
SELECT pgtle.install_extension ( 'my_password_check_rules', '1.0', 'Do not let users use the 10 most commonly used passwords', $_pgtle_$ CREATE SCHEMA password_check; REVOKE ALL ON SCHEMA password_check FROM PUBLIC; GRANT USAGE ON SCHEMA password_check TO PUBLIC; CREATE TABLE password_check.bad_passwords (plaintext) AS VALUES ('123456'), ('password'), ('12345678'), ('qwerty'), ('123456789'), ('12345'), ('1234'), ('111111'), ('1234567'), ('dragon'); CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext); CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean) RETURNS void AS $$ DECLARE invalid bool := false; BEGIN IF password_type = 'PASSWORD_TYPE_MD5' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE ('md5' || md5(bp.plaintext || username)) = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common password dictionary'; END IF; ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE bp.plaintext = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary'; END IF; END IF; END $$ LANGUAGE plpgsql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC; SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck'); $_pgtle_$ );
When the extension has been loaded into your database, you see the output such as the following.
install_extension ------------------- t (1 row)
While still connected to the database, you can now create the extension.
CREATE EXTENSION my_password_check_rules;
You can confirm that the extension has been created in the database by using the following
psql
metacommand.\dx
List of installed extensions Name | Version | Schema | Description -------------------------+---------+------------+------------------------------------------------------------- my_password_check_rules | 1.0 | public | Prevent use of any of the top-ten most common bad passwords pg_tle | 1.0.1 | pgtle | Trusted-Language Extensions for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows)
Open another terminal session to work with the Amazon CLI. You need to modify your custom DB parameter group to turn on the password-check hook. To do so, use the modify-db-parameter-group CLI command as shown in the following example.
aws rds modify-db-parameter-group \ --region
aws-region
\ --db-parameter-group-nameyour-custom-parameter-group
\ --parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"When the parameter is successfully turned on, you see the output such as the following.
( "DBParameterGroupName": "docs-lab-parameters-for-tle" }
It might take a few minutes for the change to the parameter group setting to take effect. This parameter is dynamic, however, so you don't need to restart the RDS for PostgreSQL DB instance for the setting to take effect.
Open the
psql
session and query the database to verify that the password_check hook has been turned on.labdb=>
SHOW pgtle.enable_password_check;pgtle.enable_password_check ----------------------------- on (1 row)
The password-check hook is now active. You can test it by creating a new role and using one of the bad passwords, as shown in the following example.
CREATE ROLE test_role PASSWORD 'password';
ERROR: Cannot use passwords from the common password dictionary
CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 21 at RAISE
SQL statement "SELECT password_check.passcheck_hook(
$1::pg_catalog.text,
$2::pg_catalog.text,
$3::pgtle.password_types,
$4::pg_catalog.timestamptz,
$5::pg_catalog.bool)"
The output has been formatted for readability.
The following example shows that pgsql
interactive metacommand \password
behavior
is also affected by the password_check hook.
postgres=>
SET password_encryption TO 'md5';SET
postgres=>
\passwordEnter new password for user "postgres":
*****
Enter it again:
*****
ERROR: Cannot use passwords from the common password dictionary CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 12 at RAISE SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"
You can drop this TLE extension and uninstall its source files if you want. For more information, see Dropping your TLE extensions from a database.
Password-check hook code listing
The example code shown here defines the specification for the
my_password_check_rules
TLE extension. When you copy this code
and paste it into your database, the code for the
my_password_check_rules
extension is loaded into the database,
and the password_check
hook is registered for use by the
extension.
SELECT pgtle.install_extension ( 'my_password_check_rules', '1.0', 'Do not let users use the 10 most commonly used passwords', $_pgtle_$ CREATE SCHEMA password_check; REVOKE ALL ON SCHEMA password_check FROM PUBLIC; GRANT USAGE ON SCHEMA password_check TO PUBLIC; CREATE TABLE password_check.bad_passwords (plaintext) AS VALUES ('123456'), ('password'), ('12345678'), ('qwerty'), ('123456789'), ('12345'), ('1234'), ('111111'), ('1234567'), ('dragon'); CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext); CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean) RETURNS void AS $$ DECLARE invalid bool := false; BEGIN IF password_type = 'PASSWORD_TYPE_MD5' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE ('md5' || md5(bp.plaintext || username)) = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common password dictionary'; END IF; ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE bp.plaintext = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary'; END IF; END IF; END $$ LANGUAGE plpgsql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC; SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck'); $_pgtle_$ );