skip to content

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?

level: seniorimportance: should knowfreq 46%

answer

  1. unqualified names resolve through the path
  2. pg_catalog first, but not always the winner
  3. exact argument types beat casts
  4. pg_database_owner owns public now
  5. upgraded databases keep old grants

basics

~20 s

With 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 s

The 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 lines
sql
SELECT 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

for a junior

Know the default search_path is "$user", public and that unqualified CREATE puts objects in the first existing schema of the path.

for a middle

Explain how an object created in public can capture an unqualified call, and that 15 changed the default to USAGE only for PUBLIC.

for a senior

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.

for a principal

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.