|Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud|
|Written by Nikos Vaggalis|
|Tuesday, 23 May 2023|
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:
You can then run it with:
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:
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.
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. . .
or email your comment to: email@example.com
|Last Updated ( Tuesday, 23 May 2023 )|