Procedural Languages, Trusted vs Untrusted Code, and Operational Risk

Learning outcomes

Server-side functions are not all equal. SQL and PL/pgSQL operate inside PostgreSQL's normal SQL privilege environment. Optional languages can embed another runtime, and untrusted languages or C can potentially access operating-system resources or crash a backend. The language choice therefore changes the security and operational blast radius, not just syntax preference.

1. A procedural language is a handler registered in each database

SELECT l.lanname,       l.lanispl,       l.lanpltrusted,       pg_get_userbyid(l.lanowner) AS owner,       l.lanplcallfoid::regprocedure AS call_handler,       l.laninline::regprocedure AS inline_handler,       l.lanvalidator::regprocedure AS validator,       l.lanaclFROM pg_language AS lORDER BY l.lanname;

sql, internal, and c are language mechanisms known by the server. PL/pgSQL is normally installed in every new PostgreSQL database and appears as a trusted procedural language. A procedural language delegates execution to handler functions; those handlers are compiled server code even when the function body itself is Python, Perl, Tcl, or PL/pgSQL text.

2. Trusted means constrained to normal database privileges

A language marked trusted promises that code written in that language cannot bypass the database security environment to reach arbitrary external resources. PostgreSQL grants USAGE on trusted languages to PUBLIC by default, though administrators can revoke it. Untrusted languages can only be used by superusers to create functions.

SELECT lanname,       lanpltrusted,       has_language_privilege(current_user, oid, 'USAGE') AS current_user_can_use,       lanaclFROM pg_languageWHERE lanname IN ('plpgsql','c','sql')ORDER BY lanname;

3. Safe mandatory lab: a trusted PL/pgSQL function

DROP SCHEMA IF EXISTS ch19_language_sandbox CASCADE;CREATE SCHEMA ch19_language_sandbox AUTHORIZATION servicehub_owner;GRANT USAGE, CREATE ON SCHEMA ch19_language_sandbox TO servicehub_app;

SET ROLE servicehub_app;CREATE FUNCTION ch19_language_sandbox.normalize_region(p_region text)RETURNS textLANGUAGE plpgsqlIMMUTABLESTRICTAS $$BEGIN  RETURN lower(btrim(p_region));END$$;SELECT ch19_language_sandbox.normalize_region('  NORTH  ');RESET ROLE;

The function is allowed because the role has schema CREATE and language USAGE. Trusted does not mean “automatically safe logic”; SQL injection, search-path mistakes, expensive loops, and incorrect volatility labels still need review.

4. Same role, untrusted native C: rejected

SET ROLE servicehub_app;CREATE FUNCTION ch19_language_sandbox.native_demo()RETURNS integerAS '$libdir/not_a_real_library', 'demo'LANGUAGE c;-- Expected: permission denied for language cRESET ROLE;

The server rejects the non-superuser before arbitrary native code can be registered. C-language routines run inside PostgreSQL backend processes and are not sandboxed from the server process address space. A bug can crash a backend or corrupt memory; malicious code can be far worse.

5. Optional PLs are packaging + runtime + database dependencies

SELECT name, default_version, installed_version, commentFROM pg_available_extensionsWHERE name LIKE 'pl%'ORDER BY name;SELECT name, version, superuser, trusted, requiresFROM pg_available_extension_versionsWHERE name LIKE 'pl%'ORDER BY name, version;

Standard PostgreSQL distributions can provide PL/Perl, PL/Tcl, and PL/Python components, but operating-system packages differ. PL/Python is untrusted (plpython3u): Python code is not sandboxed from the host, so only superusers can create such functions. Some language families provide both trusted and untrusted variants with different capabilities.

If an optional runtime is missing from pg_available_extensions, that is a packaging state—not a reason to create a fake language definition by hand.

6. Why CREATE LANGUAGE is not a routine application operation

-- Conceptual/admin-only; do not execute in the mandatory lab:-- CREATE LANGUAGE custom_pl--   HANDLER custom_pl_call_handler--   INLINE custom_pl_inline_handler--   VALIDATOR custom_pl_validator;-- Current practice is normally:-- CREATE EXTENSION language_extension_name;

Registering or changing a procedural language itself requires superuser privilege because the handler is compiled server code. Contemporary language packages conventionally ship as extensions; installation adds the handler and language object together.

7. Untrusted code expands your patch and incident surface

A language extension can be “successfully installed” while its runtime module imports fail at function execution. Upgrade and disaster-recovery testing must exercise representative routines, not only query catalog rows.

8. Wrong approach: treating the database as an unrestricted app server

9. Governance query and cleanup

SELECT n.nspname,       p.proname,       l.lanname,       l.lanpltrusted,       pg_get_userbyid(p.proowner) AS owner,       p.prosecdefFROM pg_proc AS pJOIN pg_namespace AS n ON n.oid = p.pronamespaceJOIN pg_language AS l ON l.oid = p.prolangWHERE n.nspname NOT IN ('pg_catalog','information_schema')ORDER BY l.lanname, n.nspname, p.proname;DROP SCHEMA ch19_language_sandbox CASCADE;

Authoritative references

Extension, FDW, language, and server-code behavior is privilege-, package-, version-, and platform-sensitive. These PostgreSQL 18 primary sources define the mechanisms used here.

• PostgreSQL 18 — Procedural Languages
• PostgreSQL 18 — CREATE LANGUAGE
• PostgreSQL 18 — pg_language
• PostgreSQL 18 — PL/Python