# 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](https://www.postgresql.org/docs/current/extend-extensions.html) in the PostgreSQL documentation.
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 the following RDS for PostgreSQL versions:
+ Version 18.1 and higher 18 versions
+ Version 17.1 and higher 17 versions
+ Version 16.1 and higher 16 versions
+ Version 15.2 and higher 15 versions
+ Version 14.5 and higher 14 versions
+ Version 13.12 and higher 13 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
](PostgreSQL_trusted_language_extension-terminology.md)
+ [
# Requirements for using Trusted Language Extensions for PostgreSQL
](PostgreSQL_trusted_language_extension-requirements.md)
+ [
# Setting up Trusted Language Extensions in your RDS for PostgreSQL DB instance
](PostgreSQL_trusted_language_extension-setting-up.md)
+ [
# Overview of Trusted Language Extensions for PostgreSQL
](PostgreSQL_trusted_language_extension.overview.md)
+ [
# Creating TLE extensions for RDS for PostgreSQL
](PostgreSQL_trusted_language_extension-creating-TLE-extensions.md)
+ [
# Dropping your TLE extensions from a database
](PostgreSQL_trusted_language_extension-creating-TLE-extensions.dropping-TLEs.md)
+ [
# Uninstalling Trusted Language Extensions for PostgreSQL
](PostgreSQL_trusted_language_extension-uninstalling-pg_tle-devkit.md)
+ [
# Using PostgreSQL hooks with your TLE extensions
](PostgreSQL_trusted_language_extension.overview.tles-and-hooks.md)
+ [
# Using Custom Data Types in TLE
](PostgreSQL_trusted_language_extension-custom-data-type.md)
+ [
# Function reference for Trusted Language Extensions for PostgreSQL
](PostgreSQL_trusted_language_extension-functions-reference.md)
+ [
# Hooks reference for Trusted Language Extensions for PostgreSQL
](PostgreSQL_trusted_language_extension-hooks-reference.md)
# 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\$1tle](https://github.com/aws/pg_tle) on 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](https://www.postgresql.org/docs/current/plperl-trusted.html) 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
The following are requirements for setting up and using the TLE development kit.
+ ** RDS for PostgreSQL versions** – Trusted Language Extensions is supported on RDS for PostgreSQL versions 13.12 and higher 13 versions, 14.5 and higher 14 versions, and 15.2 and higher versions only.
+ If you need to upgrade your RDS for PostgreSQL instance, see [Upgrades of the RDS for PostgreSQL DB engine](USER_UpgradeDBInstance.PostgreSQL.md).
+ 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](CHAP_GettingStarted.CreatingConnecting.PostgreSQL.md).
+ **Requires `rds_superuser` privileges** – To set up and configure the `pg_tle` extension, your database user role must have the permissions of the `rds_superuser` role. By default, this role is granted to the `postgres` 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](#PostgreSQL_trusted_language_extension-requirements-create-custom-params).
+ 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](PostgreSQL_trusted_language_extension-setting-up.md).
## 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.
### Console
**To create a custom DB parameter group and use it with your RDS for PostgreSQL DB instance**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Choose Parameter groups from the Amazon RDS menu.
1. Choose **Create parameter group**.
1. 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.
1. Choose **Create**. Your custom DB parameter group is created in your AWS Region. You can now modify your RDS for PostgreSQL DB instance to use it by following the next steps.
1. Choose **Databases** from the Amazon RDS menu.
1. Choose the RDS for PostgreSQL DB instance that you want to use with TLE from among those listed, and then choose **Modify.**
1. 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.
1. Choose **Continue** to save the change.
1. 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](PostgreSQL_trusted_language_extension-setting-up.md).
For more information working with DB parameter groups, see [DB parameter groups for Amazon RDS DB instances](USER_WorkingWithDBInstanceParamGroups.md).
### AWS CLI
You can avoid specifying the `--region` argument when you use CLI commands by configuring your AWS CLI with your default AWS Region. For more information, see [Configuration basics](https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-quickstart.html#cli-configure-quickstart-config) in the *AWS Command Line Interface User Guide*.
**To create a custom DB parameter group and use it with your RDS for PostgreSQL DB instance**
1. Use the [create-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-parameter-group.html) AWS CLI command to create a custom DB parameter group based on postgres14 for your AWS Region.
For Linux, macOS, or Unix:
```
aws rds create-db-parameter-group \
--region aws-region \
--db-parameter-group-name custom-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-name custom-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 AWS Region, so you can modify RDS for PostgreSQL DB instance to use it.
1. Use the [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) AWS 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-identifier your-instance-name \
--db-parameter-group-name custom-params-for-pg-tle \
--apply-immediately
```
For Windows:
```
aws rds modify-db-instance ^
--region aws-region ^
--db-instance-identifier your-instance-name ^
--db-parameter-group-name custom-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](PostgreSQL_trusted_language_extension-setting-up.md).
For more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
# 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 AWS Management Console or the AWS 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.
## Console
**To set up Trusted Language Extensions**
Perform the following steps using an account that's a member of the `rds_superuser` group (role).
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose your RDS for PostgreSQL DB instance.
1. Open the **Configuration** tab for your RDS for PostgreSQL DB instance. Among the Instance details, find the **Parameter group** link.
1. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
1. In the **Parameters** search field, type `shared_pre` to find the `shared_preload_libraries` parameter.
1. Choose **Edit parameters** to access the property values.
1. Add `pg_tle` to the list in the **Values** field. Use a comma to separate items in the list of values.
![\[Image of the shared_preload_libraries parameter with pg_tle added.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/apg_rpg_shared_preload_pg_tle.png)
1. Reboot the RDS for PostgreSQL DB instance so that your change to the `shared_preload_libraries` parameter takes effect.
1. When the instance is available, verify that `pg_tle` has been initialized. Use `psql` 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)
```
1. 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
```
1. 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's `postgres`.
```
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 the `pgtle_admin` and `postgres` roles are shown in the output. For more information, see [Understanding the rds\$1superuser role](Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.md).
```
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 |...
```
1. Close the `psql` session using the `\q` metacommand.
```
\q
```
To get started creating TLE extensions, see [Example: Creating a trusted language extension using SQL](PostgreSQL_trusted_language_extension-creating-TLE-extensions.md#PostgreSQL_trusted_language_extension-simple-example).
## AWS CLI
You can avoid specifying the `--region` argument when you use CLI commands by configuring your AWS CLI with your default AWS Region. For more information, see [Configuration basics](https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-quickstart.html#cli-configure-quickstart-config) in the *AWS Command Line Interface User Guide*.
**To set up Trusted Language Extensions**
1. Use the [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) AWS CLI command to add `pg_tle` to the `shared_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" \
--region aws-region
```
1. Use the [reboot-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/reboot-db-instance) AWS 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 \
--region aws-region
```
1. When the instance is available, you can verify that `pg_tle` has been initialized. Use `psql` 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;
```
1. 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's `postgres`.
```
GRANT pgtle_admin TO postgres;
GRANT ROLE
```
1. Close the `psql` session as follows.
```
labdb=> \q
```
To get started creating TLE extensions, see [Example: Creating a trusted language extension using SQL](PostgreSQL_trusted_language_extension-creating-TLE-extensions.md#PostgreSQL_trusted_language_extension-simple-example).
# 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.
![\[Image showing some of the components that make up the TLE development kit.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/apg-pg_tle-installed-view-in-pgAdmin.png)
You can see the following details.
1. 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.
1. TLE has its own schema, `pgtle`. This schema contains helper functions (3) for installing and managing the extensions that you create.
1. TLE provides over a dozen helper functions for installing, registering, and managing your extensions. To learn more about these functions, see [Function reference for Trusted Language Extensions for PostgreSQL](PostgreSQL_trusted_language_extension-functions-reference.md).
Other components of the `pg_tle` extension include the following:
+ **The `pgtle_admin` role** – The `pgtle_admin` role is created when the `pg_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 the `pgtle_admin` role to database users. In other words, grant the `pgtle_admin` role only to database users that are allowed to create, install, and manage new TLE extensions, such as `postgres`.
+ **The `pgtle.feature_info` table** – The `pgtle.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 have `pgtle_admin` privileges, you use the following Trusted Language Extensions functions to add and update that information in the table.
+ [pgtle.register\$1feature](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.register_feature)
+ [pgtle.register\$1feature\$1if\$1not\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.register_feature_if_not_exists)
+ [pgtle.unregister\$1feature](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.unregister_feature)
+ [pgtle.unregister\$1feature\$1if\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.unregister_feature_if_exists)
# 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.
+ [pgtle.install\$1extension](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.install_extension)
+ [pgtle.install\$1update\$1path](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.install_update_path)
+ [pgtle.register\$1feature](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.register_feature)
+ [pgtle.register\$1feature\$1if\$1not\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.register_feature_if_not_exists)
+ [pgtle.set\$1default\$1version](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.set_default_version)
+ [pgtle.uninstall\$1extension(name)](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.uninstall_extension-name)
+ [pgtle.uninstall\$1extension(name, version)](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.uninstall_extension-name-version)
+ [pgtle.uninstall\$1extension\$1if\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.uninstall_extension_if_exists)
+ [pgtle.uninstall\$1update\$1path](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.uninstall_update_path)
+ [pgtle.uninstall\$1update\$1path\$1if\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.uninstall_update_path_if_exists)
+ [pgtle.unregister\$1feature](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.unregister_feature)
+ [pgtle.unregister\$1feature\$1if\$1exists](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.unregister_feature_if_exists)
## 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](https://en.wikipedia.org/wiki/Taxicab_geometry) and [Euclidean geometry](https://en.wikipedia.org/wiki/Euclidean_geometry) in Wikipedia.
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](PostgreSQL_trusted_language_extension-setting-up.md).
**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.
1. 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
```
1. Create a TLE extension named `pg_distance` by copying the following code and pasting it into your `psql` 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 the `CREATE EXTENSION` command. In other words, you still need to create the extension to make its functions available to database users.
1. 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 the `CREATE` permissions in the database.
```
CREATE EXTENSION pg_distance;
```
1. To test the `pg_distance` TLE extension, you can use it to calculate the [Manhattan distance](https://en.wikipedia.org/wiki/Taxicab_geometry) between four points.
```
labdb=> SELECT manhattan_dist(1, 1, 5, 5);
8
```
To calculate the [Euclidean distance](https://en.wikipedia.org/wiki/Euclidean_geometry) 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` – The `IMMUTABLE` attribute ensures that the query optimizer can use optimizations to improve query response times. For more information, see [Function Volatility Categories](https://www.postgresql.org/docs/current/xfunc-volatility.html) in the PostgreSQL documentation.
+ `PARALLEL SAFE` – The `PARALLEL SAFE` attribute is another attribute that allows PostgreSQL to run the function in parallel mode. For more information, see [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html) in 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\$1update\$1path](PostgreSQL_trusted_language_extension-functions-reference.md#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**
1. 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=labdb
```
1. Modify 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.
1. 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\$1default\$1version](PostgreSQL_trusted_language_extension-functions-reference.md#pgtle.set_default_version).
1. 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**
1. 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
```
1. 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](PostgreSQL_trusted_language_extension-creating-TLE-extensions.md#PostgreSQL_trusted_language_extension-simple-example), 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.
1. 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**
1. 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
```
1. 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 the `CASCADE` 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 the `CASCADE` keyword.
1. 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](PostgreSQL_trusted_language_extension-setting-up.md).
# 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](PostgreSQL_trusted_language_extension-hooks-reference.md).
## 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](PostgreSQL_trusted_language_extension-setting-up.md).
**To set up the password-check hook example**
1. 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
```
1. Copy the code from the [Password-check hook code listing](#PostgreSQL_trusted_language_extension-example-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 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)
```
1. While still connected to the database, you can now create the extension.
```
CREATE EXTENSION my_password_check_rules;
```
1. 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)
```
1. Open another terminal session to work with the AWS 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](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) CLI command as shown in the following example.
```
aws rds modify-db-parameter-group \
--region aws-region \
--db-parameter-group-name your-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.
1. Open the `psql` session and query the database to verify that the password\$1check 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\$1check hook.
```
postgres=> SET password_encryption TO 'md5';
SET
postgres=> \password
Enter 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 databaseDropping your TLE extensions from a database](PostgreSQL_trusted_language_extension-creating-TLE-extensions.dropping-TLEs.md).
### 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 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_$
);
```
# Using Custom Data Types in TLE
PostgreSQL supports commands to register new base types (also known as scalar types) for efficiently handling complex data structures in your database. A base type allows you to customize how the data is stored internally, and how to convert it to and from an external textual representation. These custom data types are helpful when extending PostgreSQL to support functional domains where a built-in type such as number or text can't provide sufficient search semantics.
RDS for PostgreSQL enables you to create custom data types in your trusted language extension and define functions that support SQL and index operations for these new data types. Custom data types are available for the following versions:
+ RDS for PostgreSQL 15.4 and higher 15 versions
+ RDS for PostgreSQL 14.9 and higher 14 versions
+ RDS for PostgreSQL 13.12 and higher 13 versions
For more information, see [Trusted Language Base types](https://github.com/aws/pg_tle/blob/main/docs/09_datatypes.md).
# Function reference for Trusted Language Extensions for PostgreSQL
View the following reference documentation about functions available in Trusted Language Extensions for PostgreSQL. Use these functions to install, register, update, and manage your *TLE extensions*, that is, the PostgreSQL extensions that you develop using the Trusted Language Extensions development kit.
**Topics**
+ [
## pgtle.available\$1extensions
](#pgtle.available_extensions)
+ [
## pgtle.available\$1extension\$1versions
](#pgtle.available_extension_versions)
+ [
## pgtle.extension\$1update\$1paths
](#pgtle.extension_update_paths)
+ [
## pgtle.install\$1extension
](#pgtle.install_extension)
+ [
## pgtle.install\$1update\$1path
](#pgtle.install_update_path)
+ [
## pgtle.register\$1feature
](#pgtle.register_feature)
+ [
## pgtle.register\$1feature\$1if\$1not\$1exists
](#pgtle.register_feature_if_not_exists)
+ [
## pgtle.set\$1default\$1version
](#pgtle.set_default_version)
+ [
## pgtle.uninstall\$1extension(name)
](#pgtle.uninstall_extension-name)
+ [
## pgtle.uninstall\$1extension(name, version)
](#pgtle.uninstall_extension-name-version)
+ [
## pgtle.uninstall\$1extension\$1if\$1exists
](#pgtle.uninstall_extension_if_exists)
+ [
## pgtle.uninstall\$1update\$1path
](#pgtle.uninstall_update_path)
+ [
## pgtle.uninstall\$1update\$1path\$1if\$1exists
](#pgtle.uninstall_update_path_if_exists)
+ [
## pgtle.unregister\$1feature
](#pgtle.unregister_feature)
+ [
## pgtle.unregister\$1feature\$1if\$1exists
](#pgtle.unregister_feature_if_exists)
## pgtle.available\$1extensions
The `pgtle.available_extensions` function is a set-returning function. It returns all available TLE extensions in the database. Each returned row contains information about a single TLE extension.
### Function prototype
```
pgtle.available_extensions()
```
### Role
None.
### Arguments
None.
### Output
+ `name` – The name of the TLE extension.
+ `default_version` – The version of the TLE extension to use when `CREATE EXTENSION` is called without a version specified.
+ `description` – A more detailed description about the TLE extension.
### Usage example
```
SELECT * FROM pgtle.available_extensions();
```
## pgtle.available\$1extension\$1versions
The `available_extension_versions` function is a set-returning function. It returns a list of all available TLE extensions and their versions. Each row contains information about a specific version of the given TLE extension, including whether it requires a specific role.
### Function prototype
```
pgtle.available_extension_versions()
```
### Role
None.
### Arguments
None.
### Output
+ `name` – The name of the TLE extension.
+ `version` – The version of the TLE extension.
+ `superuser` – This value is always `false` for your TLE extensions. The permissions needed to create the TLE extension or update it are the same as for creating other objects in the given database.
+ `trusted` – This value is always `false` for a TLE extension.
+ `relocatable` – This value is always `false` for a TLE extension.
+ `schema` – Specifies the name of the schema in which the TLE extension is installed.
+ `requires` – An array containing the names of other extensions needed by this TLE extension.
+ `description` – A detailed description of the TLE extension.
For more information about output values, see [Packaging Related Objects into an Extension > Extension Files](https://www.postgresql.org/docs/current/extend-extensions.html#id-1.8.3.20.11) in the PostgreSQL documentation.
### Usage example
```
SELECT * FROM pgtle.available_extension_versions();
```
## pgtle.extension\$1update\$1paths
The `extension_update_paths` function is a set-returning function. It returns a list of all the possible update paths for a TLE extension. Each row includes the available upgrades or downgrades for that TLE extension.
### Function prototype
```
pgtle.extension_update_paths(name)
```
### Role
None.
### Arguments
`name` – The name of the TLE extension from which to get upgrade paths.
### Output
+ `source` – The source version for an update.
+ `target` – The target version for an update.
+ `path` – The upgrade path used to update a TLE extension from `source` version to `target` version, for example, `0.1--0.2`.
### Usage example
```
SELECT * FROM pgtle.extension_update_paths('your-TLE');
```
## pgtle.install\$1extension
The `install_extension` function lets you install the artifacts that make up your TLE extension in the database, after which it can be created using the `CREATE EXTENSION` command.
### Function prototype
```
pgtle.install_extension(name text, version text, description text, ext text, requires text[] DEFAULT NULL::text[])
```
### Role
None.
### Arguments
+ `name` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `version` – The version of the TLE extension.
+ `description` – A detailed description about the TLE extension. This description is displayed in the `comment` field in `pgtle.available_extensions()`.
+ `ext` – The contents of the TLE extension. This value contains objects such as functions.
+ `requires` – An optional parameter that specifies dependencies for this TLE extension. The `pg_tle` extension is automatically added as a dependency.
Many of these arguments are the same as those that are included in an extension control file for installing a PostgreSQL extension on the file system of a PostgreSQL instance. For more information, see the [Extension Files](http://www.postgresql.org/docs/current/extend-extensions.html#id-1.8.3.20.11) in [Packaging Related Objects into an Extension](https://www.postgresql.org/docs/current/extend-extensions.html) in the PostgreSQL documentation.
### Output
This functions returns `OK` on success and `NULL` on error.
+ `OK` – The TLE extension has been successfully installed in the database.
+ `NULL` – The TLE extension hasn't been successfully installed in the database.
### Usage example
```
SELECT pgtle.install_extension(
'pg_tle_test',
'0.1',
'My first pg_tle extension',
$_pgtle_$
CREATE FUNCTION my_test()
RETURNS INT
AS $$
SELECT 42;
$$ LANGUAGE SQL IMMUTABLE;
$_pgtle_$
);
```
## pgtle.install\$1update\$1path
The `install_update_path` function provides an update path between two different versions of a TLE extension. This function allows users of your TLE extension to update its version by using the `ALTER EXTENSION ... UPDATE` syntax.
### Function prototype
```
pgtle.install_update_path(name text, fromvers text, tovers text, ext text)
```
### Role
`pgtle_admin`
### Arguments
+ `name` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `fromvers` – The source version of the TLE extension for the upgrade.
+ `tovers` – The destination version of the TLE extension for the upgrade.
+ `ext` – The contents of the update. This value contains objects such as functions.
### Output
None.
### Usage example
```
SELECT pgtle.install_update_path('pg_tle_test', '0.1', '0.2',
$_pgtle_$
CREATE OR REPLACE FUNCTION my_test()
RETURNS INT
AS $$
SELECT 21;
$$ LANGUAGE SQL IMMUTABLE;
$_pgtle_$
);
```
## pgtle.register\$1feature
The `register_feature` function adds the specified internal PostgreSQL feature to the `pgtle.feature_info` table. PostgreSQL hooks are an example of an internal PostgreSQL feature. The Trusted Language Extensions development kit supports the use of PostgreSQL hooks. Currently, this function supports the following feature.
+ `passcheck` – Registers the password-check hook with your procedure or function that customizes PostgreSQL's password-check behavior.
### Function prototype
```
pgtle.register_feature(proc regproc, feature pg_tle_feature)
```
### Role
`pgtle_admin`
### Arguments
+ `proc` – The name of a stored procedure or function to use for the feature.
+ `feature` – The name of the `pg_tle` feature (such as `passcheck`) to register with the function.
### Output
None.
### Usage example
```
SELECT pgtle.register_feature('pw_hook', 'passcheck');
```
## pgtle.register\$1feature\$1if\$1not\$1exists
The `pgtle.register_feature_if_not_exists` function adds the specified PostgreSQL feature to the `pgtle.feature_info` table and identifies the TLE extension or other procedure or function that uses the feature. For more information about hooks and Trusted Language Extensions, see [Using PostgreSQL hooks with your TLE extensions](PostgreSQL_trusted_language_extension.overview.tles-and-hooks.md).
### Function prototype
```
pgtle.register_feature_if_not_exists(proc regproc, feature pg_tle_feature)
```
### Role
`pgtle_admin`
### Arguments
+ `proc` – The name of a stored procedure or function that contains the logic (code) to use as a feature for your TLE extension. For example, the `pw_hook` code.
+ `feature` – The name of the PostgreSQL feature to register for the TLE function. Currently, the only available feature is the `passcheck` hook. For more information, see [Password-check hook (passcheck)](PostgreSQL_trusted_language_extension-hooks-reference.md#passcheck_hook).
### Output
Returns `true` after registering the feature for the specified extension. Returns `false` if the feature is already registered.
### Usage example
```
SELECT pgtle.register_feature_if_not_exists('pw_hook', 'passcheck');
```
## pgtle.set\$1default\$1version
The `set_default_version` function lets you specify a `default_version` for your TLE extension. You can use this function to define an upgrade path and designate the version as the default for your TLE extension. When database users specify your TLE extension in the `CREATE EXTENSION` and `ALTER EXTENSION ... UPDATE` commands, that version of your TLE extension is created in the database for that user.
This function returns `true` on success. If the TLE extension specified in the `name` argument doesn't exist, the function returns an error. Similarly, if the `version` of the TLE extension doesn't exist, it returns an error.
### Function prototype
```
pgtle.set_default_version(name text, version text)
```
### Role
`pgtle_admin`
### Arguments
+ `name` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `version` – The version of the TLE extension to set the default.
### Output
+ `true` – When setting default version succeeds, the function returns `true`.
+ `ERROR` – Returns an error message if a TLE extension with the specified name or version doesn't exist.
### Usage example
```
SELECT * FROM pgtle.set_default_version('my-extension', '1.1');
```
## pgtle.uninstall\$1extension(name)
The `uninstall_extension` function removes all versions of a TLE extension from a database. This function prevents future calls of `CREATE EXTENSION` from installing the TLE extension. If the TLE extension doesn't exist in the database, an error is raised.
The `uninstall_extension` function won't drop a TLE extension that's currently active in the database. To remove a TLE extension that's currently active, you need to explicitly call `DROP EXTENSION` to remove it.
### Function prototype
```
pgtle.uninstall_extension(extname text)
```
### Role
`pgtle_admin`
### Arguments
+ `extname` – The name of the TLE extension to uninstall. This name is the same as the one used with `CREATE EXTENSION` to load the TLE extension for use in a given database.
### Output
None.
### Usage example
```
SELECT * FROM pgtle.uninstall_extension('pg_tle_test');
```
## pgtle.uninstall\$1extension(name, version)
The `uninstall_extension(name, version)` function removes the specified version of the TLE extension from the database. This function prevents `CREATE EXTENSION` and `ALTER EXTENSION` from installing or updating a TLE extension to the specified version. This function also removes all update paths for the specified version of the TLE extension. This function won't uninstall the TLE extension if it's currently active in the database. You must explicitly call `DROP EXTENSION` to remove the TLE extension. To uninstall all versions of a TLE extension, see [pgtle.uninstall\$1extension(name)](#pgtle.uninstall_extension-name).
### Function prototype
```
pgtle.uninstall_extension(extname text, version text)
```
### Role
`pgtle_admin`
### Arguments
+ `extname` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `version` – The version of the TLE extension to uninstall from the database.
### Output
None.
### Usage example
```
SELECT * FROM pgtle.uninstall_extension('pg_tle_test', '0.2');
```
## pgtle.uninstall\$1extension\$1if\$1exists
The `uninstall_extension_if_exists` function removes all versions of a TLE extension from a given database. If the TLE extension doesn't exist, the function returns silently (no error message is raised). If the specified extension is currently active within a database, this function doesn't drop it. You must explicitly call `DROP EXTENSION` to remove the TLE extension before using this function to uninstall its artifacts.
### Function prototype
```
pgtle.uninstall_extension_if_exists(extname text)
```
### Role
`pgtle_admin`
### Arguments
+ `extname` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
### Output
The `uninstall_extension_if_exists` function returns `true` after uninstalling the specified extension. If the specified extension doesn't exist, the function returns `false`.
+ `true` – Returns `true` after uninstalling the TLE extension.
+ `false` – Returns `false` when the TLE extension doesn't exist in the database.
### Usage example
```
SELECT * FROM pgtle.uninstall_extension_if_exists('pg_tle_test');
```
## pgtle.uninstall\$1update\$1path
The `uninstall_update_path` function removes the specific update path from a TLE extension. This prevents `ALTER EXTENSION ... UPDATE TO` from using this as an update path.
If the TLE extension is currently being used by one of the versions on this update path, it remains in the database.
If the update path specified doesn't exist, this function raises an error.
### Function prototype
```
pgtle.uninstall_update_path(extname text, fromvers text, tovers text)
```
### Role
`pgtle_admin`
### Arguments
+ `extname` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `fromvers` – The source version of the TLE extension used on the update path.
+ `tovers` – The destination version of the TLE extension used on the update path.
### Output
None.
### Usage example
```
SELECT * FROM pgtle.uninstall_update_path('pg_tle_test', '0.1', '0.2');
```
## pgtle.uninstall\$1update\$1path\$1if\$1exists
The `uninstall_update_path_if_exists` function is similar to `uninstall_update_path` in that it removes the specified update path from a TLE extension. However, if the update path doesn't exist, this function doesn't raise an error message. Instead, the function returns `false`.
### Function prototype
```
pgtle.uninstall_update_path_if_exists(extname text, fromvers text, tovers text)
```
### Role
`pgtle_admin`
### Arguments
+ `extname` – The name of the TLE extension. This value is used when calling `CREATE EXTENSION`.
+ `fromvers` – The source version of the TLE extension used on the update path.
+ `tovers` – The destination version of the TLE extension used on the update path.
### Output
+ `true` – The function has successfully updated the path for the TLE extension.
+ `false` – The function wasn't able to update the path for the TLE extension.
### Usage example
```
SELECT * FROM pgtle.uninstall_update_path_if_exists('pg_tle_test', '0.1', '0.2');
```
## pgtle.unregister\$1feature
The `unregister_feature` function provides a way to remove functions that were registered to use `pg_tle` features, such as hooks. For information about registering a feature, see [pgtle.register\$1feature](#pgtle.register_feature).
### Function prototype
```
pgtle.unregister_feature(proc regproc, feature pg_tle_features)
```
### Role
`pgtle_admin`
### Arguments
+ `proc` – The name of a stored function to register with a `pg_tle` feature.
+ `feature` – The name of the `pg_tle` feature to register with the function. For example, `passcheck` is a feature that can be registered for use by the trusted language extensions that you develop. For more information, see [Password-check hook (passcheck)](PostgreSQL_trusted_language_extension-hooks-reference.md#passcheck_hook).
### Output
None.
### Usage example
```
SELECT * FROM pgtle.unregister_feature('pw_hook', 'passcheck');
```
## pgtle.unregister\$1feature\$1if\$1exists
The `unregister_feature` function provides a way to remove functions that were registered to use `pg_tle` features, such as hooks. For more information, see [Using PostgreSQL hooks with your TLE extensions](PostgreSQL_trusted_language_extension.overview.tles-and-hooks.md). Returns `true` after successfully unregistering the feature. Returns `false` if the feature wasn't registered.
For information about registering `pg_tle` features for your TLE extensions, see [pgtle.register\$1feature](#pgtle.register_feature).
### Function prototype
```
pgtle.unregister_feature_if_exists('proc regproc', 'feature pg_tle_features')
```
### Role
`pgtle_admin`
### Arguments
+ `proc` – The name of the stored function that was registered to include a `pg_tle` feature.
+ `feature` – The name of the `pg_tle` feature that was registered with the trusted language extension.
### Output
Returns `true` or `false`, as follows.
+ `true` – The function has successfully unregistered the feature from extension.
+ `false` – The function wasn't able to unregister the feature from the TLE extension.
### Usage example
```
SELECT * FROM pgtle.unregister_feature_if_exists('pw_hook', 'passcheck');
```
# Hooks reference for Trusted Language Extensions for PostgreSQL
Trusted Language Extensions for PostgreSQL supports PostgreSQL hooks. A *hook* is an internal callback mechanism available to developers for extending PostgreSQL's core functionality. By using hooks, developers can implement their own functions or procedures for use during various database operations, thereby modifying PostgreSQL's behavior in some way. For example, you can use a `passcheck` hook to customize how PostgreSQL handles the passwords supplied when creating or changing passwords for users (roles).
View the following documentation to learn about the passcheck hook available for your TLE extensions. To learn more about the available hooks including the client authentication hook, see [Trusted Language Extensions hooks](https://github.com/aws/pg_tle/blob/main/docs/04_hooks.md).
## Password-check hook (passcheck)
The `passcheck` hook is used to customize PostgreSQL behavior during the password-checking process for the following SQL commands and `psql` metacommand.
+ `CREATE ROLE username ...PASSWORD` – For more information, see [CREATE ROLE](https://www.postgresql.org/docs/current/sql-createrole.html) in the PostgreSQL documentation.
+ `ALTER ROLE username...PASSWORD` – For more information, see [ALTER ROLE](https://www.postgresql.org/docs/current/sql-alterrole.html) in the PostgreSQL documentation.
+ `\password username` – This interactive `psql` metacommand securely changes the password for the specified user by hashing the password before transparently using the `ALTER ROLE ... PASSWORD` syntax. The metacommand is a secure wrapper for the `ALTER ROLE ... PASSWORD` command, thus the hook applies to the behavior of the `psql` metacommand.
For an example, see [Password-check hook code listing](PostgreSQL_trusted_language_extension.overview.tles-and-hooks.md#PostgreSQL_trusted_language_extension-example-hook_code_listing).
**Contents**
+ [
### Function prototype
](#passcheck_hook-prototype)
+ [
### Arguments
](#passcheck_hook-arguments)
+ [
### Configuration
](#passcheck_hook-configuration)
+ [
### Usage notes
](#passcheck_hook-usage)
### Function prototype
```
passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean)
```
### Arguments
A `passcheck` hook function takes the following arguments.
+ `username` – The name (as text) of the role (username) that's setting a password.
+ `password` – The plaintext or hashed password. The password entered should match the type specified in `password_type`.
+ `password_type` – Specify the `pgtle.password_type` format of the password. This format can be one of the following options.
+ `PASSWORD_TYPE_PLAINTEXT` – A plaintext password.
+ `PASSWORD_TYPE_MD5` – A password that's been hashed using MD5 (message digest 5) algorithm.
+ `PASSWORD_TYPE_SCRAM_SHA_256` – A password that's been hashed using SCRAM-SHA-256 algorithm.
+ `valid_until` – Specify the time when the password becomes invalid. This argument is optional. If you use this argument, specify the time as a `timestamptz` value.
+ `valid_null` – If this Boolean is set to `true`, the `valid_until` option is set to `NULL`.
### Configuration
The function `pgtle.enable_password_check` controls whether the passcheck hook is active. The passcheck hook has three possible settings.
+ `off` – Turns off the `passcheck` password-check hook. This is the default value.
+ `on` – Turns on the `passcode` password-check hook so that passwords are checked against the table.
+ `require` – Requires a password check hook to be defined.
### Usage notes
To turn the `passcheck` hook on or off, you need to modify the custom DB parameter group for your RDS for PostgreSQL DB instance.
For Linux, macOS, or Unix:
```
aws rds modify-db-parameter-group \
--region aws-region \
--db-parameter-group-name your-custom-parameter-group \
--parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"
```
For Windows:
```
aws rds modify-db-parameter-group ^
--region aws-region ^
--db-parameter-group-name your-custom-parameter-group ^
--parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"
```