Schemas and search_path

When you write SELECT * FROM notes without a schema, PostgreSQL has to decide which notes you mean. It looks for an unqualified name such as notes in each schema on the current search_path, in order, and uses the first match.

Check what your session resolves against right now:

SHOW search_path;

   search_path
-----------------
 "$user", public

That default means: first a schema named after the current role, if one exists, then public. Our tables live in app, which is on neither, so a bare notes fails with “relation does not exist” even though the table is right there.

Set the path for the runtime role

You could qualify every query, but for the application role it is cleaner to set the default once:

ALTER ROLE notes_app IN DATABASE notes_app
SET search_path = app, pg_catalog;

Every new connection made by notes_app to this database now resolves notes as app.notes. The setting applies at connection time, so already-open sessions keep their old path until they reconnect.

Verify it the way the application will experience it:

\connect notes_app notes_app
SHOW search_path;
SELECT count(*) FROM notes;

If the count works without a schema prefix, the path is right.

For migration and administration work, my advice is the opposite: qualify queries as app.notes when clarity matters. Admin sessions connect as different roles with different paths, and an explicit name cannot be resolved into the wrong table.

The security angle

The search path is also a security boundary. Name resolution happens in path order, so never put a schema writable by an untrusted role before trusted schemas in the path. A hostile object created there can shadow the table or function you meant, and your queries execute against the impostor.

The historical version of this problem is public. Fresh PostgreSQL 15+ databases do not give every role CREATE on public. Clusters upgraded from PostgreSQL 14 or older can keep the earlier grant, where any role could create objects in everyone’s default path. Inspect it with \dn+ public, and remove it when present:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

With the runtime path set and public locked down, “which table did I just query” stops being a question you need to ask.