PostgreSQL Trusted Languages – List and Security Considerations

functionspostgresqlpostgresql-extensionsSecurity

I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment.

But I couldn't find a definitive answer. For example, the Postgres documentation here suggest that there is a both a trusted and an untrusted PL for perl, a language that I am not very familiar with. Here, it says the TRUSTED keyword is something you can specify when creating a PL language, which may be ignored under circumstances.

But I also have the (possibly wrong) impression that certain PL languages are trusted only (e.g. JavaScript PL/V8) or untruested only (Python). Other posts seem to suggest that one can tweak pg to make even python a trusted language with something like:

UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'plpythonu';

My question is more practical than theoretical. Basically:

Is it generally true that any PL can be both set up to be trusted and untrusted?

If not, what are the specific languages that can be set up to be trusted or are trusted.

(By "trusted", I mean normal users should have effectively the same rights when creating/executing/modifying functions in the PL language. Also, I am using Postgres 9.x)

Best Answer

Is it generally true that any PL can be both set up to be trusted and untrusted?

No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted, it'll either notice that and ERROR or it'll silently run without sandboxing as if it were untrusted.

Don't modify catalogs directly.

If not, what are the specific languages that can be set up to be trusted or are trusted.

Just these,

  • PL/PgSQL runs as trusted.
  • PL/Perl can run as trusted or untrusted.
  • PL/TCL can run as trusted or untrusted.

That's it for the built-in PLs. Notably, PL/Python cannot run as trusted due to fundamental design limitations with the CPython interpreter that make creation of a restricted execution environment impossible. It's possible to create a "trusted-ish" Python runtime where an attacker is protected from simple mistakes but can break out of the sandbox with effort, but PL/Python does not support this, nor is any patch to enable it likely to be accepted.

There are also a bunch of out-of-tree procedural languages, some of which support trusted mode. Notably, PL/V8 (JavaScript), PL/Lua and PL/Java support trusted mode. There are probably others.

(On a side note, I really would love to see adoption of in-core a trusted-enabled PL/Javascript as a default in-core PL, or even better, PL/C# for .NET via Mono or .NET Core).

Related Question