In PostgreSQL, why is CREATE on the public schema combined with the default search_path a privilege-escalation risk, and what changed in PostgreSQL 15?
answer
- unqualified names resolve through the path
- pg_catalog first, but not always the winner
- exact argument types beat casts
- pg_database_owner owns public now
- upgraded databases keep old grants
basics
~20 sWith search_path "$user", public, anyone who can create objects in public can plant a function or operator that a privileged session's unqualified call resolves to. Since 15, new databases give PUBLIC only USAGE on public; upgraded databases keep CREATE until revoked.
solid answer
~50 sThe default `search_path` is `"$user", public`, with `pg_catalog` searched implicitly first. Before 15, `PUBLIC` held `CREATE` on schema `public`, so any login could create objects there. Function and operator resolution prefers an exact match of argument types over a candidate that needs a type conversion, so a hostile user can create, say, a `lower(varchar)` in `public` that captures a call the built-in `pg_catalog.lower(text)` would otherwise serve. When a superuser, the table owner or a migration runs that unqualified query, the planted code runs with their privileges. Since 15, new databases have `public` owned by `pg_database_owner` and `PUBLIC` holds only `USAGE`; databases upgraded from 14 or earlier keep the old grant, so run `REVOKE CREATE ON SCHEMA public FROM PUBLIC`. Then keep application schemas writable only by trusted roles, pin per-role paths, and schema-qualify names in privileged code.
code
sql · 11 linesSELECT n.nspname,
pg_get_userbyid(n.nspowner) AS owner,
n.nspacl
FROM pg_namespace AS n
WHERE n.nspname = 'public';
-- needed on databases that started life on 14 or earlier
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER ROLE hr_app SET search_path = hr;
ALTER ROLE hr_migrator SET search_path = hr;go deeper
Know the default search_path is "$user", public and that unqualified CREATE puts objects in the first existing schema of the path.
Explain how an object created in public can capture an unqualified call, and that 15 changed the default to USAGE only for PUBLIC.
Audit upgraded databases for the leftover CREATE grant, pin per-role paths, qualify names in privileged code, and anticipate 17's restricted path for maintenance.
Set a fleet-wide schema-usage policy covering who may own databases, which schemas are writable, and how migrations and extensions are placed.
## How PostgreSQL resolves an unqualified name `search_path` lists the schemas searched for names written without a schema. Its default is `"$user", public`: `$user` stands for a schema named after the current role and is skipped when no such schema exists. Two more schemas are searched implicitly: - `pg_catalog` is searched **before** the listed schemas unless you place it in the path explicitly; - the session's temporary schema (`pg_temp`) is searched first of all, but only for relation and data type names, never for functions or operators. The first existing schema in the path is also where an unqualified `CREATE` puts a new object, which is why objects land in `public` by default. ## Where the trap is Function and operator resolution does not just take the first name it meets. Among all visible candidates it prefers one whose argument types match the call **exactly** over one that needs a type conversion. A call such as `lower(job_title)` on a `varchar` column normally reaches `pg_catalog.lower(text)` through a conversion, so a `lower(varchar)` created in any schema on the path wins. The attack then runs like this: 1. An untrusted login that can create objects in `public` creates `public.lower(varchar)` whose body does what its author wants with the caller's rights, for instance altering the author's role. 2. A privileged session (a superuser, the owner of `hr.employees`, a migration) runs a query containing the unqualified call. 3. The planted function executes with that session's privileges. Operators follow the same exact-match preference, so a planted `=` or `||` for a specific pair of types is just as effective as a planted function. The calls do not have to be typed by a person either: unqualified references in the body of a PL/pgSQL function, including a trigger function, are resolved when it runs, through the executing session's `search_path`, unless the function sets its own path. (Views are different: their references are bound when the view is created.) Running such code as a privileged role is what turns a nuisance into privilege escalation. The manual's own term for a layout that prevents this is a **secure schema usage pattern**; a path that contains schemas writable by untrusted users is never one. ## What changed in PostgreSQL 15 | | Database created on 15 or later | Database upgraded from 14 or earlier | |---|---|---| | `PUBLIC` on schema `public` | `USAGE` only | `USAGE` and `CREATE` | | action needed | none for the user-private-schema pattern | `REVOKE CREATE ON SCHEMA public FROM PUBLIC` | In new databases `public` is owned by `pg_database_owner`, a predefined role whose only member is the current database owner, so each database owner governs `public` in that database. The upgrade column matters because an upgrade carries the old grants forward: a cluster that started life on 13 is still exposed on 18 until someone revokes. ## Hardening checklist 1. On any database with history before 15, run `REVOKE CREATE ON SCHEMA public FROM PUBLIC` and audit `public` for objects named like those in `pg_catalog`. 2. Put application objects in a dedicated schema (`hr`) owned by a `NOLOGIN` owner role, with `CREATE` granted to nobody else. 3. Pin paths per role, for example `ALTER ROLE hr_app SET search_path = hr`, or drop `public` from everyone's path with `ALTER ROLE ALL SET search_path = "$user"`. 4. Schema-qualify names in code that privileged roles run: migrations, maintenance jobs, and anything called by triggers. Functions declared `SECURITY DEFINER` need their own `SET search_path`, a separate topic. 5. Remember the documented limit: if an untrusted user owns the database or holds `ADMIN` on a relevant role, no secure schema usage pattern exists, because a database owner can set the default path for sessions in that database, and an ADMIN holder can grant itself the role it administers. ## Related hardening in 17 Since PostgreSQL 17, maintenance operations (`ANALYZE`, `CLUSTER`, `CREATE INDEX`, `CREATE MATERIALIZED VIEW`, `REFRESH MATERIALIZED VIEW`, `REINDEX` and `VACUUM`) run with a restricted `search_path`. Functions used by expression indexes and materialized views that reference other schemas must therefore set their own path or qualify names; otherwise a `VACUUM` or `REINDEX` that used to work starts failing to find them. ## What to check on a running cluster - `SELECT nspacl FROM pg_namespace WHERE nspname = 'public'` shows whether `PUBLIC` still has `C` (create) in the ACL. - `SHOW search_path` in each application role's session confirms the pinned path. - psql's `\df public.*` lists functions someone has created in `public`, the first place to look for planted look-alikes.
- If pg_catalog is searched first in PostgreSQL, why doesn't that protect calls to built-in functions?Resolution considers every visible candidate and prefers an exact argument-type match over one needing a conversion. A planted `lower(varchar)` in `public` beats `pg_catalog.lower(text)` for a `varchar` argument, even though `pg_catalog` is searched earlier. Being searched first mainly decides between candidates with identical argument types.
- After upgrading to PostgreSQL 17 or 18, REINDEX on an expression index starts failing to find a helper function. Why?Since 17, maintenance operations such as `REINDEX`, `VACUUM`, `ANALYZE` and `REFRESH MATERIALIZED VIEW` run with a restricted `search_path`. A function used by the index that relies on an unqualified reference to another schema no longer resolves; give the function its own `SET search_path` or schema-qualify its references.
- Because pg_catalog is searched first, built-in function calls cannot be hijacked.
- pg_upgrade to 15 or later removes CREATE on public from PUBLIC automatically.
- Only superusers can create objects in the public schema.
- Dropping $user from search_path is what made PostgreSQL 15 secure.
- A schema-private layout stays secure even if an untrusted user owns the database.
Assumes PostgreSQL 18; PUBLIC lost CREATE on schema public in databases created on 15 or later, and 17 made maintenance commands run with a restricted search_path.