PostgreSQL Trusted Extensions for Beginners

Introduction

Recently, we had a long discussion in our internal chat about the concept of Trusted Extensions in PostgreSQL. It became clear that while the feature is very useful, it’s often misunderstood — especially by beginners. Let's fix that!

This post explains what trusted extensions are, why they exist, how they work, and provides some important hints and warnings for everyday use.

What Are PostgreSQL Extensions?

An extension is a package of SQL scripts, types, functions, and sometimes even compiled C code that extends PostgreSQL's capabilities.
Extensions are installed on the server and then enabled inside a database using:

Normally, installing or enabling an extension requires superuser privileges, because extensions can modify how the database server behaves.

What Does "Trusted" Mean?

Trusted extensions allow non-superusers (regular database users) to enable certain extensions themselves using CREATE EXTENSION, without needing superuser rights.

In simple words:

• If an extension is trusted, a user with CREATE privilege on a database can activate it.
• If an extension is not trusted, only a superuser can activate it.

Important:
"Trusted" does not mean:

• The extension is bug-free.
• The code has been officially audited by PostgreSQL core developers.
• It is completely safe in every possible scenario.

It simply means:

"We believe that enabling this extension should not allow users to bypass security or harm the database server."

How Does PostgreSQL Know if an Extension is Trusted?

Each extension has a control file (.control) where its properties are described.
Inside that file, the line:

tells PostgreSQL that the extension can be enabled by non-superusers.

If the line is missing, the extension is considered untrusted by default.

Example of a simple control file:

The same extension can be trusted on one server but untrusted on another, depending on what the local superuser decides!

Why Is Trusted Important for Cloud Providers?

Trusted extensions are especially important for PostgreSQL cloud providers. In managed database services), users typically don't have superuser access for safety reasons. Without trusted extensions, they would be severely limited in extending the functionality of their databases.

Thanks to trusted extensions:

• Users can self-service common tools like hstore, citext, pg_trgm, and more.
• Cloud providers can offer flexibility without compromising security.
• Managing thousands of customer databases becomes much easier!

As you can see, trusted extensions make PostgreSQL much more cloud-friendly!

Trusted = Toy Room Analogy

Let's use a simple analogy:
Imagine a kindergarten (your PostgreSQL server) full of kids (database users) who love to play with toys (extensions). You, as the principal/teacher (superuser), are responsible for making sure everyone is safe.

• There are two rooms with toys:
  • An open room: All kids can freely play here.
  • A locked room: Only staff can bring out toys under supervision.

You decide which toys go into which room:

• A soft ball is safe → It goes into the open room (trusted).
• Lego pieces might be dangerous for very young kids → They stay in the locked room (untrusted).
• Toy guns or slingshots are risky → Definitely kept locked.

The important thing is it’s not about who made the toy (extension author), or even how famous it is.
It’s about whether it’s safe to allow general users to play with it without direct supervision.

And yes — if a principal (admin) wants, they could technically move any toy to the open room — but it's on them if something goes wrong!

Why PostgreSQL Core Marks Some Extensions as Trusted

Some extensions that ship bundled with PostgreSQL (from the "contrib" modules) are marked trusted.
These usually provide safe functionality like new data types, functions, or indexes without dangerous side effects.

However, external extensions (e.g., developed by companies like CYBERTEC) are not usually automatically trusted — it's up to authors and administrators to make those decisions.

Important Hints, Warnings, and Notes

- Trusted does not mean bug-free.
Even trusted extensions could have bugs. Trust is a promise by the author — not a full audit!

- C Extensions deserve extra caution.
If an extension loads C code (.so files), it can still crash the database or introduce risks. Labeling them trusted requires careful review. Same applies for pgrx extension written in Rust, for example.

- Superusers have the final say.
Admins can override trust by editing control files or managing access rights manually.

- "Install" vs "Create" confusion.

• Installing an extension = Copying files to the server.
• Creating an extension = Registering it in a specific database with SQL.

- You don't have to trust blindly.
Even if an extension says "trusted", it's perfectly okay to review it yourself before allowing users to enable it.

Final Thoughts

Trusted extensions are an elegant PostgreSQL feature that helps balance security and flexibility.

• If you develop extensions, think carefully before marking them trusted.
• If you administer servers, regularly review what you allow users to install.
• If you use PostgreSQL in the cloud, appreciate how trusted extensions help you extend your database easily!

Used wisely, trusted extensions empower PostgreSQL to stay open, safe, and powerful for everyone.