Supabase Makes Extensions Easier for Developers with Trusted Language Extensions for PostgreSQL

One of the reasons developers love PostgreSQL, beyond being an open source relational database, is that you can add new capabilities to support your applications. This is possible because of the extensible design of PostgreSQL, which allows for implementing new functionality such as data types or complex data processing without changes to the core database engine. For example, applications with maps and geographic information can use the PostGIS extension to add spatial data type support.

AWS partner Supabase is familiar with the extensibility of PostgreSQL and uses this to deliver features in its offering. Supabase is an open source Firebase alternative that provides the backend features developers need to build web and mobile apps. The Supabase platform is implemented as a thin usability layer on top of PostgreSQL. As a company built on open source, Supabase aims to provide its community with a self-hosting option that is cloud-provider agnostic. They are using Trusted Language Extensions for PostgreSQL to make the Supabase platform easier to deploy across multiple cloud providers and to make it easier for developers to add extensions in Supabase.

PostgreSQL extensions and the cloud

Within the PostgreSQL community more than 1,000 known open source extensions are available, in addition to an unknown number of commercial extensions that support applications from independent software vendors (ISVs). Amazon Relational Database Service (RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition support a curated set of 90+ extensions. Why the curation?

Traditionally, PostgreSQL extensions, particularly those written in C, have involved some risk of unsafe access to the database that are mitigated through how extensions are managed and deployed. For example, extensions require filesystem access to be installed, may involve privileged access to the database, access to shared memory, or network access. Under the AWS Shared Responsibility Model, managing the security risks of each of these 90+ extensions to infrastructure is the responsibility of AWS. Managing these security risks takes time. Other providers of managed PostgreSQL, like Supabase, often follow a similar model. With customers asking for support of more extensions on PostgreSQL, it is time to innovate on the extension model.

Trusted Language Extensions (TLE) for PostgreSQL is an open source development kit that lets developers create extensions in their preferred language with built-in safety guardrails. A PostgreSQL trusted language, such as JavaScript, Perl, or PL/pgSQL, provides security boundaries so that an unprivileged user can safely run code in the database. TLE is available as the open source PostgreSQL extension pg_tle and can be used with Amazon RDS and Amazon Aurora.

PostgreSQL extensions in Supabase

The Supabase development team recognized that TLE could make it easier for them to support more extensions for app developers using the Supabase platform. Because TLE is available under the Apache 2.0 license it also aligns with Supabase’s open source approach.

The Supabase platform includes more than 50 PostgreSQL extensions. For example, when an app developer using Supabase creates a project, a dedicated PostgreSQL database is included with row level security to control what data the app users can access. Row level security is integrated with JSON Web Tokens (JWT) authentication using the pgjwt extension, which is licensed under the MIT License, and written in the trusted languages SQL and PL/pgSQL.

However, managing and supporting 50 extensions was becoming a burden for the Supabase team. An important consideration for the Supabase development team is the customer experience when upgrading to a new version of the platform. They require a supported migration path for the code in the database that is in sync with the Supabase platform code. With the PostgreSQL extension packaging mechanism and the TLE management functionality, the Supabase team can install and upgrade both Supabase platform code and extension code following software development lifecycle (SDLC) best practices. The result is a successful upgrade experience for Supabase customers.

The Supabase platform now includes pg_tle to install and manage a number of trusted language extensions such as pgjwt. For app developers using Supabase, you can more easily add PostgreSQL extensions while skipping the Pull Request and the wait for Supabase to add an extension to your database. Tooling advances to make it easier to discover and install TLEs are underway with dbdev: A Database Package Manager.

Installing pgjwt on Amazon RDS

Here is an example of installing a TLE developed by a third party — in this case pgjwt developed by Supabase — on Amazon RDS.

As a prerequisite, you need to have TLE installed and created on your Amazon RDS or Amazon Aurora database instance. See “Working with Trusted Language Extensions for PostgreSQL” in the Amazon RDS User Guide or Amazon Aurora User Guide.

1.     In your SQL query tool of choice, install the pgjwt extension so that it is available in your database by providing the extension name, version, description, extension contents, and dependencies.

SELECT pgtle.install_extension(
'pgjwt', '0.2.0', 'JSON Web Token API for Postgresql',
$pg_tle$
-- NOTE: Copy the contents of
-- https://github.com/michelp/pgjwt/blob/master/pgjwt--0.2.0.sql
-- in here and remove the first line that contains "\echo"
$pg_tle$,
'{pgcrypto}'
);

2.     Now that pgjwt is installed in your database, you can address the dependency on the pgcrypto extension by creating it if it has not already been made available to database users. pgcrypto is installed as an Amazon RDS supported extension.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

3.     With pgjwt installed in your database, and the pgcrypto dependency available, you can now create the pgjwt extension so that its functionality is available to database users.

CREATE EXTENSION pgjwt;

You can now work with JSON Web Tokens, see how in the pgjwt README.

If you are no longer are using pgjwt, the following steps can be used to remove the extensions:

1.     From the database you installed pgjwt, remove user access to the extension functionality.

DROP EXTENSION pgjwt;

2.     Because you no longer need the dependent pgcrypto, its functionality can be removed from user access. It will remain if other database objects are dependent on pgcrypto.

DROP EXTENSION pgcrypto;

3.     With the functionality of both extensions removed from access by database users, remove the pgjwt extension from the database.

SELECT pgtle.uninstall_extension('pgjwt', '0.2.0');

The above steps also apply if you are running PostgreSQL on your local machine. For details see the pg_tle documentation in GitHub.

A community solution

As more applications are built and run in the cloud, developers need the ability to control their destiny and add PostgreSQL extensions without waiting for the cloud provider of choice to add support. Trusted Language Extensions for PostgreSQL provides the built-in safety guardrails to run PostgreSQL in managed and self-managed environments. Together with Supabase and other contributors, we are building a community and look forward to advancing features, tooling, and trusted languages that make it easier for everyone to safely run PostgreSQL extensions.

To learn more, see Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS. You can get started by launching a new Amazon RDS database instance directly from the AWS Console.