In PostgreSQL, why can a non-superuser application role run CREATE EXTENSION ltree but get permission denied for amcheck?
answer
- a flag in the control file
- CREATE on the database, not the schema
- the script runs as someone else
- no reach outside the database
basics
~20 sltree's control file says trusted = true, so any role with CREATE privilege on the current database may install it, and its script runs as the bootstrap superuser. amcheck is not marked trusted, so only a superuser can install it.
solid answer
~40 sInstall scripts usually need superuser rights (C functions, base types), so the control-file flag `superuser` defaults to true. A second flag, `trusted = true`, relaxes that: any role holding `CREATE` on the current database may run `CREATE EXTENSION`, and PostgreSQL runs the install or update script as the bootstrap superuser. The calling role owns the extension, so it can `DROP` or update it. The member objects, though, belong to the bootstrap superuser unless the script hands some over with `@extowner@`. Contrib marks as trusted only modules that cannot reach outside the database, such as `pgcrypto`, `hstore`, `ltree`, `uuid-ossp` and `citext`. `amcheck`, `pg_stat_statements`, `postgres_fdw`, `dblink` and `file_fdw` are not trusted. A non-superuser therefore gets `permission denied to create extension "amcheck"` with the hint 'Must be superuser to create this extension'.
code
sql · 9 lines-- as role catalog_owner, owner of database shop (not a superuser)
CREATE EXTENSION ltree; -- ok: trusted, caller has CREATE on shop
CREATE EXTENSION amcheck;
-- ERROR: permission denied to create extension "amcheck"
-- HINT: Must be superuser to create this extension.
SELECT name, version, superuser, trusted
FROM pg_available_extension_versions
WHERE name IN ('ltree', 'pgcrypto', 'amcheck');go deeper
Recall that some extensions such as pgcrypto and ltree can be installed without superuser, while others such as amcheck need one.
Explain the superuser and trusted control-file flags, the CREATE-on-database requirement, the switch to the bootstrap superuser, and who owns the extension versus its members.
Show security judgement: install unvetted extensions only into schemas no untrusted role can write, grant amcheck-style functions to specific roles, and never flip trusted on by hand.
Decide the extension policy for a multi-team platform: which extensions teams may self-serve, and how privileged installs are requested and audited.
## Two control-file flags decide who may install Every extension's control file can set two booleans: | `superuser` | `trusted` | Who can run `CREATE EXTENSION` | Script runs as | |---|---|---|---| | `true` (default) | `false` (default) | superusers only | the superuser who ran it | | `true` | `true` | superusers, or any role with `CREATE` on the current database | the bootstrap superuser | | `false` | ignored | anyone with the privileges the script's own commands need | the calling role | `superuser = true` is the default because most install scripts create C-language functions, base types or operator classes, which need superuser rights. `trusted = true` is the extension author's promise that letting an ordinary database owner run the script hands out nothing beyond the database: no file-system access, no OS commands, no path to escalate privileges. ## What happens when an app role installs a trusted extension 1. PostgreSQL reads `ltree.control` and sees `superuser = true` and `trusted = true`. 2. It checks that the calling role has `CREATE` on the **current database**. By default `PUBLIC` gets only `CONNECT` and `TEMPORARY` on a database, so in practice the caller is the database owner or a role granted `CREATE` on it explicitly. 3. It switches to the **bootstrap superuser** (the role `initdb` created) for the duration of the script, and runs it. 4. The `pg_extension` row is owned by the **calling role**, so that role can later run `DROP EXTENSION ltree` or `ALTER EXTENSION ltree UPDATE`. The member objects belong to the bootstrap superuser unless the script assigns some of them to the caller through the `@extowner@` placeholder. That means the caller cannot `ALTER` or replace individual functions. When the flags do not allow the install, the error is SQLSTATE `42501`: *permission denied to create extension "amcheck"*, with the hint *Must be superuser to create this extension*. For a trusted extension the hint instead asks for `CREATE` privilege on the current database. ## Which modules are trusted The default installation trusts the contrib modules that stay inside the database: - **Trusted:** `btree_gin`, `btree_gist`, `citext`, `cube`, `dict_int`, `fuzzystrmatch`, `hstore`, `intarray`, `isn`, `lo`, `ltree`, `pgcrypto`, `pg_trgm`, `seg`, `tablefunc`, `tcn`, `tsm_system_rows`, `tsm_system_time`, `unaccent`, `uuid-ossp`. - **Not trusted:** modules that read files, connect to other servers, inspect raw pages or shared memory, or verify storage. Examples are `amcheck`, `pageinspect`, `pg_buffercache`, `pgstattuple`, `pg_stat_statements`, `postgres_fdw`, `dblink` and `file_fdw`. Procedural languages follow the same rule. `plpgsql` and `plperl` are trusted. `plperlu` and `plpython3u` can do anything the server's OS account can, so they need a superuser. You can see the flag without opening any files: `SELECT name, version, superuser, trusted FROM pg_available_extension_versions;`. ## Why trusted is a security boundary A trusted extension's script runs as a superuser, but in a schema the *caller* picked. A hostile role that can create objects in that schema could plant a function or operator that the script then resolves through `search_path`, running that code as superuser. This is the trojan-horse attack the manual warns about. So marking an extension trusted takes real extra care in writing its scripts. And for any extension whose scripts have not been vetted, install it only into a schema where no untrusted role has `CREATE`. The same care applies when you open up an untrusted extension after a superuser installs it. `amcheck` revokes `EXECUTE` on its functions from `PUBLIC`, and its documentation warns that corruption reports can leak information about the data. Grant those functions to a monitoring role on purpose, not to `PUBLIC`. ## Where this matters in practice Where application teams never get superuser, trusted extensions are what lets them add `pgcrypto`, `hstore` or `ltree` on their own. Anything outside the trusted set has to be installed by whoever holds superuser, whether a DBA or a privileged platform process, and then exposed with targeted `GRANT`s. A database owner who wants `amcheck` asks for it rather than for superuser. ## Checking who can install what - `pg_available_extension_versions` shows the `superuser` and `trusted` flags for every version on disk, including whether an update to a newer version would still be trusted. - `SELECT has_database_privilege('catalog_owner', current_database(), 'CREATE')` shows whether a role passes the trusted-extension check in the current database. - `\dx+ ltree` in psql lists the member objects the install script created, which is what `DROP EXTENSION` will remove. - Granting `CREATE` on a database is a real grant of power: the role can create schemas there and install any trusted extension. Give it to the owner of an application's database, not to every login role.
- Can a PostgreSQL superuser make amcheck installable by an app role just by granting that role a privilege?No. Trust is read from the control file, and no `GRANT` changes it. The usual pattern is for a superuser to run `CREATE EXTENSION amcheck` once per database and then `GRANT EXECUTE` on the specific functions to a monitoring role, since amcheck's scripts revoke `EXECUTE` from `PUBLIC`. Adding `trusted = true` to the control file would turn an extension never designed for it into a privilege-escalation risk.
- Why can a PostgreSQL database owner install plperl but not plperlu or plpython3u?`plperl` runs Perl in a restricted interpreter with no file or OS access, and its control file says `trusted = true`, so a role with `CREATE` on the database may install it. `plperlu` and `plpython3u` (the 'u' means untrusted) can do anything the server's OS account can, so their control files are not trusted and only a superuser installs them. `plpgsql` is trusted and installed by default.
- A trusted extension's install script runs with the calling role's own privileges.
- Any role that can connect to a database can install a trusted extension there.
- GRANT CREATE ON SCHEMA public lets a non-superuser install amcheck.
- The non-superuser who installs a trusted extension owns its functions and can redefine them.
- Trusted means the extension's code was security-audited, so its schema choice does not matter.