TLE stands for Trusted Language Extension and DbDev by SupaBase now supports installing them in your PostgreSQL instance, like NPM does for Javascript packages.

We've looked at TLEs before  in Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud  where I explained:

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.

This of course comes with security issues when the database invokes code like file system operations, or using statements that could interact with the operating system or database server process.

For that Trusted Language Extensions (TLE) came about. They are PostgreSQL extensions that you can safely run on your DB instance. Trusted Language Extensions do not provide access to the filesystem and are designed to prevent access to unsafe resources. 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. It's that part that DbDev makes much easier to do; instead of fiddling around trying to install your extension DbDev streamlines this process.

The dbdev client manager ironically comes as a TLE itself which must be installed in your PostgreSQL instance the old way, which means writting some code. 

To do so it requires pg_tle, yes the TLE framework itself, like any other TLE does , and pgsql-http , the PostgreSQL HTTP Client which is used to to fetch it from the web. If however your database is running on Supabase, these dependencies are already installed.

After installation you can now fetch and install extensions, referred to now as pglets (PostGres appLETs), as simple as :

-- Load the package from the package index
select
dbdev. install ('olirice-asciiplot');

-- Enable the extension
create extension "olirice-asciiplot" version '0. 2. 1';

Where olirice is the handle of the publisher and asciiplot is the name of the pglet.

It's important to note that dbdev is not just a package manager but provides a package registry too , which comes pre-loaded
with a handful of useful pglets, like the olirice-asciiplot above.

A sample of the the packages available on the registry are:

burggraf-pg_headerkit, which is toolkit for adding advanced features to PostgREST APIs like rate limiting, IP allowlisting/denylisting and request logging

and

olirice-index_advisor, which is a simple tool that takes a query and recommends indexes to minimize the “total_cost” according to the query’s explain plan.

For the time being the registry is read only and contains packages vetted by SupaBase, but they will be releasing the tooling necessary for third-parties to publish pglets to the registry under the goal to create an open ecosystem for packaging and discovering SQL.

More Information

The Database Package Manager For PostgreSQL trusted language extensions (TLEs)
 

Related Articles

Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud

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