Roles, databases, and schemas
Schemas and search_path
Resolve unqualified table names deliberately and avoid granting broad access through a convenient default schema.
8 minute lesson
~~~
PostgreSQL looks for an unqualified name such as notes in each schema on the current search_path.
Set the runtime default to the application schema:
ALTER ROLE notes_app IN DATABASE notes_app
SET search_path = app, pg_catalog;Qualify migration and administration queries as app.notes when clarity matters. Never put a schema writable by an untrusted role before trusted schemas in the path.
Fresh PostgreSQL 15+ databases do not give every role CREATE on public. Clusters upgraded from PostgreSQL 14 or older can keep the earlier grant. Inspect it with \dn+ public, and remove it when present:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;Lesson completed