An Overview of Trusted Extensions in PostgreSQL 13

In my previous blog, we explored new capabilities of Logical replication with Partition tables in PostgreSQL 13. Needless to say that there are multitude of such features in the mentioned release which will soon enhance experience for DBA and application developers alike.

While looking at PostgreSQL 13, I observed an entry which caught my attention:

PostgreSQL 13 introduces the concept of a “trusted extension”, which allows for a superuser to specify extensions that a user can install in their database so long as they have a CREATE privilege.

Let’s Rewind

We know that PostgreSQL has extension power to add feathers to its cap without disturbing much of its core. In other words, extensions enhance functional capabilities to PostgreSQL Server in a non-intrusive way. 

In fact, there are many third party organisations who have used extensions mechanism to generate amazing feature sets. TimescaleDB is one such extension where it is kind of changing the persona of PostgreSQL Server to make it more suitable for IOT workload.

Let’s take  a look at what was there before PostgreSQL 13 and why it was a real pain. Consider a hosted PostgreSQL Instance containing two roles:

• postgres (the super user)
• johnsmith (a normal user)

And the database wooliesdb.

John Smith would like to add the postgres extension hstore to wooliedb, since his application code is relying on that. Let’s try to do that.

 psql -U johnsmith -d wooliesdb

wooliesdb=>CREATE EXTENSION hstore;

ERROR:  permission denied to create extension "hstore"

HINT:  Must be superuser to create this extension.

The error clearly indicates that the extension can only be created by super user i.e. postgres. Even though extensions like hstore don’t have any security concern in context of its usage, it’s still only super users who can create this extension on the database.

What if the database was owned or created by johnsmith – we can try that too. In the following snippet, postgres superuser is allowing johnsmith to create altogether new database of his own to play around:

$ psql -U postgres 

postgres=# ALTER ROLE johnsmith CREATEDB;

postgres=# q

$ psql -U johnsmith -d wooliesdb

wooliesdb=>CREATE DATABASE jsDB;

wooliesdb=>c jsDB;

You are now connected to database "jsDB" as user "johnsmith".

jsDB=>CREATE EXTENSION hstore;

ERROR:  permission denied to create extension "hstore"

HINT:  Must be superuser to create this extension.

Ahh! It doesn’t make any difference. Even though johnsmith is the owner of jsDB,  he still can not install relevant, simple extensions to his database.

But that’s all in PostgreSQL server 12 (and below); it is going to change with PostgreSQL version 13. At the time of writing this blog – PostgreSQL version 13 is in Beta2 stage and the team is already writing a release announcement. I’m gonna try “trusted extensions” with Beta2 binaries.

Here Comes PostgreSQL 13

Expect a different behaviour with the concept of trusted extensions (at-least for contrib modules or pre-packaged extensions). Let’s check the behaviour with PostgreSQL13 for the same commands which we did for PostgreSQL12.

$ psql -U johnsmith -d wooliesdb

wooliesdb=>CREATE EXTENSION hstore;

ERROR:  permission denied to create extension "hstore"

HINT:  Must have CREATE privilege on current database to create this extension.

Which is pretty much the same i.e. johnsmith still can’t create the extension. But still there is a subtle difference – the HINT which suggests that CREATE privilege is missing. Our second set of commands should take care of that:

$ psql -U postgres 

postgres=# ALTER ROLE johnsmith CREATEDB;

postgres=# q

$ psql -U johnsmith -d wooliesdb

wooliesdb=>CREATE DATABASE jsDB;

wooliesdb=>c jsDB;

You are now connected to database "jsDB" as user "johnsmith".

jsDB=>CREATE EXTENSION hstore;

jsDB=>SELECT extname from pg_extension;

 extname

---------

 plpgsql

 hstore

(2 rows)

Things really worked this time. Superuser can allow same privileges on postgres db by executing the following command:

postgres=# GRANT CREATE ON DATABASE postgres FOR johnsmith;

But there is more to trusted extension than this, let’s try to create another extension:

jsDB=> create extension file_fdw;

ERROR:  permission denied to create extension "file_fdw"

HINT:  Must be superuser to create this extension.

The difference is coming from the fact that while hstore is marked as trusted, file_fdw is NOT marked as trusted. Where is that marked? It’s in the control file of the extensions.

$ cd /usr/pgsql-13/share/extension 

$ grep -l trusted hstore.control file_fdw.control;

hstore.control

In fact, there are 24 trusted and 24 not so trusted extensions coming with postgreSQL13.

In nutshell, superusers can relinquish control over such trusted extensions; and any user with the CREATE permissions on a database can enable trusted extensions without approaching his database administrator.

Conclusion

Behind the scenes, the behaviour is controlled by two parameters in the extension control file:

• superuser, which defaults to true
• trusted, which defaults to false

An extension can be created by a non-superuser only if both are true. In fact, a trusted extension is the installation or update script is run as the bootstrap superuser, not as the calling user. Remember that extensions might be written in a language which itself is not-trusted – hence the need.