Feb 17, 2026

How to Build Trusted PostgreSQL Extensions Using pg_tle

What is pg_tle?

pg_tle (Trusted Language Extensions) is an AWS open-source PostgreSQL extension that allows you to:

Create extensions using JavaScript, Perl, pl/pgsql, Tcl, and SQL.
Avoid C compilation
Avoid server restarts for every extension change
Manage versions and upgrade paths dynamically
Prepare extensions that are compatible with RDS / Aurora environments

In short, the pg_tle extension builds PostgreSQL extensions directly from SQL and other languages like JavaScript, Perl, PL/pgSQL, and Tcl.

This is extremely powerful for cloud deployments.

Installing pg_tle from Source

First, clone the repository:

git clone https://github.com/aws/pg_tle.git
cd pg_tle

Build and install using PGXS:

make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install

Enable shared_preload_libraries

pg_tle requires preload.

Why are we setting the values for the shared_preload_libraries parameter in the postgres config file?

We set shared_preload_libraries so PostgreSQL loads pg_tle during server startup, before any client connections are created. This allows pg_tle to register its internal hooks and event triggers, which cannot be initialized at runtime. Without preloading, pg_tle cannot manage extensions or enforce its trusted execution model.

Find your PostgreSQL config file:

SHOW config_file;

Example output:

/etc/postgresql/18/main/postgresql.conf

Edit it:

sudo nano /etc/postgresql/18/main/postgresql.conf

Locate:

shared_preload_libraries

Change to:

shared_preload_libraries = 'pg_tle'

Restart PostgreSQL:

sudo systemctl restart postgresql

Now create the extension:

CREATE EXTENSION pg_tle;

Verify:

\dx

You should see the result like this

                               List of installed extensions
  Name  | Version | Default version | Schema |                Description                 
--------+---------+-----------------+--------+--------------------------------------------
 pg_tle | 1.5.2   | 1.5.2           | pgtle  | Trusted Language Extensions for PostgreSQL
(1 row)

Exploring Available pg_tle Functions

List pg_tle APIs:

\dx+ pg_tle

We can see a total 24 functions.Let’s explore the most important functions from this extension

Important functions:

pgtle.install_extension
pgtle.available_extensions
pgtle.available_extension_versions
pgtle.install_update_path
pgtle.set_default_version
pgtle.uninstall_extension

Creating Your First Extension (Hello World)

Let’s install an SQL-based extension.

SELECT pgtle.install_extension(
  'hello_world',
  '1.0',
  'A simple hello world extension',
  $_pgtle_$
    CREATE FUNCTION hello_world()
    RETURNS TEXT AS
    $$
      SELECT 'Hello from pg_tle extension!';
    $$
    LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

Activate it:

CREATE EXTENSION hello_world;

Test:

SELECT hello_world();

Output:

Hello from pg_tle extension!

You can also list the functions based on this extension like this command

\df+ hello_world

You get a result like this

List of functions
 Schema |    Name     | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Leakproof? | Access privileges | Language | Internal name | Description 
--------+-------------+------------------+---------------------+------+------------+----------+----------+----------+------------+-------------------+----------+---------------+-------------
 public | hello_world | text             |                     | func | immutable  | unsafe   | postgres | invoker  | no         |                   | sql      |               | 
(1 row)

No C code. No shared objects.

Pure SQL. This is one of the biggest advantages when we use the pg_tle extension in Amazon AWS

Creating a Utility Extension with Multiple Functions

Example:

SELECT pgtle.install_extension(
  'my_utils',
  '1.0',
  'Utility functions extension',
  $_pgtle_$
    CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
    RETURNS INTEGER AS $$ SELECT a + b; $$ LANGUAGE SQL IMMUTABLE;
    CREATE FUNCTION get_timestamp_text()
    RETURNS TEXT AS $$ SELECT NOW()::TEXT; $$ LANGUAGE SQL STABLE;
    CREATE FUNCTION reverse_string(input_text TEXT)
    RETURNS TEXT AS $$ SELECT reverse(input_text); $$ LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

Enable:

CREATE EXTENSION my_utils;

Use:

SELECT add_numbers(5,10);
 add_numbers 
-------------
          15
(1 row)
SELECT get_timestamp_text();
       get_timestamp_text        
----------------------------------
 2026-01-27 20:53:39.751444+05:30
(1 row)

SELECT reverse_string('PostgreSQL');
 reverse_string 
----------------
 LQSergtsoP
(1 row)

Extension Versioning (Real Example)

Install two versions:

Version 1.0

SELECT pgtle.install_extension(
  'my_versioned_ext',
  '1.0',
  'Versioned extension example',
  $_pgtle_$
    CREATE FUNCTION get_version()
    RETURNS TEXT AS $$ SELECT '1.0'; $$ LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

Version 2.0

SELECT pgtle.install_extension(
  'my_versioned_ext',
  '2.0',
  'Versioned extension example - v2',
  $_pgtle_$
    CREATE FUNCTION get_version()
    RETURNS TEXT AS $$ SELECT '2.0'; $$ LANGUAGE SQL IMMUTABLE;
    CREATE FUNCTION get_build_date()
    RETURNS TEXT AS $$ SELECT '2025-01-27'; $$ LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

Define Upgrade Path

SELECT pgtle.install_update_path(
  'my_versioned_ext',
  '1.0',
  '2.0',
  $_pgtle_$
    DROP FUNCTION IF EXISTS get_version();
    CREATE FUNCTION get_version()
    RETURNS TEXT AS $$ SELECT '2.0'; $$ LANGUAGE SQL IMMUTABLE;
    CREATE FUNCTION get_build_date()
    RETURNS TEXT AS $$ SELECT '2025-01-27'; $$ LANGUAGE SQL IMMUTABLE;
  $_pgtle_$
);

Set default version:

SELECT pgtle.set_default_version('my_versioned_ext','2.0');

Create:

CREATE EXTENSION my_versioned_ext;

Switching Default Versions

SELECT pgtle.set_default_version('my_versioned_ext','1.0');

Drop and recreate:

DROP EXTENSION my_versioned_ext;
CREATE EXTENSION my_versioned_ext;

Now version 1.0 is active.

You can check the installed version of the extension like this

select * from pg_extension;

Uninstalling Extensions

Normal drop:

DROP EXTENSION hello_world;

Remove pg_tle metadata:

SELECT pgtle.uninstall_extension('hello_world');

pg_tle fundamentally changes how we think about PostgreSQL extensions.Instead of system-level development, now we have database-level extension engineering. For developers working on PostgreSQL customization or as a database administrator or a PostgreSQL developer, this extension opens an entirely new workflow.If you already understand PostgreSQL internals, pg_tle feels like giving superpowers to SQL.