Trusted Language Extensions on Amazon allow you to write PostgreSQL user-defined functions for its cloud database offerings in one of several supported programming languages.

But let's take it from the beginning. PostgreSQL allows user defined functions to be written in other languages besides SQL and C. These other languages are generically called procedural languages (PLs). Procedural languages aren't built into the PostgreSQL server; they are offered by loadable modules. That way you can extend your database with powerful features not found in SQL. For instance you can write a PL/Perl procedure to accept a string from your SQL to apply regular expressions to it in order to tokenize it.

The way it works is by defining a PL/Perl function using the standard SQL CREATE FUNCTION syntax, with its body being ordinary Perl code. The PL/Perl interpreter wraps this code inside a Perl subroutine.

Here's an example straight from the Postgres manual:

CREATE OR REPLACE FUNCTION perl_set_int(int)

RETURNS SETOF INTEGER AS $$
    foreach (0..$_[0]) {
     return_next($_);
    }
  return undef;

$$ LANGUAGE plperl;

You can then run it with:

SELECT * FROM perl_set_int(5);

Of course, this creates an issue with trust - if the database can invoke any arbitrary code wrapped into a udf, like file system operations or using statements that could interact with the operating system or database server process, this could provoke security issues. For instance:

CREATE FUNCTION badfunc() RETURNS integer AS $$
  my $tmpfile = "/tmp/badfile";
  open my $fh, '>', $tmpfile
    or elog(ERROR, qq{could not open the file 
      "$tmpfile": $!});
  print $fh "Testing writing to a file\n";
  close $fh
    or elog(ERROR, qq{could not close the file
       "$tmpfile": $!});
  return 1;
$$ LANGUAGE plperl;

This example will not work because file system operations are not allowed for security reasons, per plperl.

Postgres offers the concept of trusted and untrusted languages, with languages such as Perl offering both variants. The example above could run using the Untrusted variant of Perl, plperlu. However, only database superusers have privileges for creating untrusted PL/Perl user-defined functions. And only database superusers and other database users that are explicitly granted the permissions can execute untrusted PL/Perl user-defined functions.

It goes without saying that these procedural functions are not allowed to run on a Cloud environment since installing a new PostgreSQL extension involves having access to the underlying filesystem. Many managed service providers, or systems running databases in containers, disallow users from accessing the filesystem for security and safety reasons. This makes it challenging to add new extensions in these environments, as users either need to request for a managed service provider to build an extension or rebuild a container image.

For that reason Amazon has developed the Trusted Language Extensions (TLE) 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. Trusted Language Extensions do not provide access to the filesystem and are 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 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.

The documentation contains plenty of code examples on how to build your own extension in those languages mentioned above. However there's also a new player in town, for which there's no TLE support yet, that of PL/Rust, which comes with its own distinct advantages:

PL/Rust is a loadable procedural language that enables writing PostgreSQL functions in the Rust programming language. These functions are compiled to native machine code. Unlike other procedural languages, PL/Rust functions are not interpreted.

The top advantages of PL/Rust include writing natively-compiled functions to achieve the absolute best performance, access to Rust's large development ecosystem, and Rust's compile-time safety guarantees.

I bet we'll be seeing support for this one too very shortly. . .

More Information

New – Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS

Trusted Language Extensions for PostgreSQL (pg_tle)

PL/Rust Guide

Related Articles

Azure CosmosDB for PostgreSQL Reaches General Availability

PostgreSQL 15 Released - What's New

pgsqlite - Load SQLite databases into PostgresSQL

Running PostgreSQL Inside Your Browser

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info