Cluster vs Database vs Schema Boundaries and Search Path Semantics

Learning outcomes

ServiceHub already has a PostgreSQL database cluster, a database named servicehub_lab, an app schema, and application/owner roles. Those names look simple, but they represent three different namespace boundaries. Confusing them leads directly to migration errors, failed cross-database queries, unexpected object resolution, and security defects.

This lesson makes the boundaries observable. You will prove that one PostgreSQL server instance can contain many databases, that an ordinary SQL session is connected to exactly one database at a time, that schemas are namespaces inside a database, and that search_path decides how PostgreSQL resolves unqualified names. You will also deliberately create a shadowing condition in a disposable schema and then repair it.

1. From cluster to database to schema

Start from the outside and move inward. The Chapter 01/02 instance has one initialized data directory. That server instance is the PostgreSQL database cluster. The cluster contains databases such as postgres, template0, template1, and the course database servicehub_lab. Inside servicehub_lab, schemas such as pg_catalog, information_schema, public, app, and extensions provide namespaces for objects.

PostgreSQL database cluster / one server instance|+-- postgres database+-- template0 database+-- template1 database+-- servicehub_lab database  <-- one ordinary session connects here    |    +-- pg_catalog schema    +-- information_schema    +-- public schema    +-- app schema    |   +-- customers    |   +-- technicians    |   +-- work_orders    +-- extensions schema

Two schemas in the same database can contain objects with the same unqualified name. Two different databases can also contain schemas and tables with identical names, but they are separate database namespaces. That distinction is why a migration that succeeded in servicehub_lab tells you nothing about a similarly named table in another database.

2. Prove which database the session is actually using

Use server-side identity rather than relying on a terminal title or connection string you remember typing. PostgreSQL exposes the current database, effective role, and namespace settings directly:

SELECT current_database() AS database_name,       session_user AS login_role,       current_user AS effective_role,       current_schema() AS first_existing_schema;SHOW search_path;SELECT current_schemas(true) AS effective_path_with_implicit_schemas,       current_schemas(false) AS explicit_path_only;

current_schema() returns the first valid schema in the effective search path. It is not necessarily the schema you conceptually think of as “the application schema.” current_schemas(true) can reveal implicitly searched schemas such as pg_catalog.

3. One session, one database

PostgreSQL schemas are not a substitute for separate databases, and separate databases are not just top-level schemas. An ordinary backend process connects to one database at startup. A query cannot directly write other_database.app.customers and expect PostgreSQL to jump to another database in the same cluster.

In psql, \c or \connect does not “switch the current database inside the same server transaction.” It closes/replaces the connection and establishes a new session connection to the requested database. Application drivers behave similarly: the database name is part of connection establishment.

\conninfoSELECT pg_backend_pid(), current_database();-- Reconnect to another database only if your admin role is allowed there.\c postgresSELECT pg_backend_pid(), current_database();-- Return to the disposable course database.\c servicehub_labSELECT pg_backend_pid(), current_database();

The backend PID normally changes because psql established another connection. If you need controlled access to objects in another database, PostgreSQL mechanisms include client-side multiple connections and foreign-data mechanisms such as postgres_fdw; those are explicit features with security and transaction semantics, not magical cross-database qualification.

4. How unqualified names are resolved

When you write SELECT * FROM work_orders, PostgreSQL searches schemas according to search_path. The first matching object that is visible in the path wins. If the object is outside the path, you must qualify it, for example app.work_orders.

The Chapter 01 application role uses a controlled database-specific setting:

SELECT rolname, rolconfigFROM pg_catalog.pg_rolesWHERE rolname = 'servicehub_app';SHOW search_path;SELECT to_regclass('work_orders') AS unqualified_resolution,       to_regclass('app.work_orders') AS explicit_resolution;

to_regclass() is useful in diagnostics because it returns NULL when a relation name cannot be resolved instead of raising an error. Casting a nonexistent name directly to regclass would error.

pg_catalog deserves special attention. PostgreSQL always searches it. If it is not explicitly listed, it is searched before the listed path entries. That ensures built-in functions, operators, types, and catalog objects remain available. Explicitly placing pg_catalog later in a path can alter that precedence, which is one reason security-sensitive code should use a deliberately safe path instead of inheriting arbitrary caller state.

5. Creation uses the first suitable schema

The search path affects object creation as well as lookup. An unqualified CREATE TABLE x (...) targets the current schema, which is the first effective schema in the path. PostgreSQL does not skip that schema merely because the role lacks CREATE; the statement fails instead. This is why a path beginning with pg_catalog is excellent for trusted lookup but a poor implicit DDL target.

SET ROLE servicehub_owner;SET search_path = app, pg_catalog;SELECT current_schema(), current_schemas(true);CREATE TABLE chapter03_scratch (    scratch_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    note text NOT NULL);SELECT 'chapter03_scratch'::regclass;DROP TABLE app.chapter03_scratch;RESET search_path;RESET ROLE;

For application DML, a controlled default path can be ergonomic. For migrations and privileged routines, explicit schema qualification is still clearer and independent of caller path state.

6. Deliberately wrong approach: put a writable schema first

Suppose an application trusts an unqualified function named servicehub_status(), but its path begins with a schema where an untrusted role can create objects. That role can create another object with the same name, causing unqualified lookup to resolve the attacker-controlled object first. This is namespace shadowing.

We will demonstrate the mechanism without running privileged code. As an administrator in the disposable database, create two temporary teaching schemas and two harmless functions:

CREATE SCHEMA ch03_trusted;CREATE SCHEMA ch03_untrusted;CREATE FUNCTION ch03_trusted.servicehub_status()RETURNS textLANGUAGE sqlIMMUTABLEAS $$ SELECT 'trusted implementation'::text $$;CREATE FUNCTION ch03_untrusted.servicehub_status()RETURNS textLANGUAGE sqlIMMUTABLEAS $$ SELECT 'shadow implementation'::text $$;SET search_path = ch03_untrusted, ch03_trusted, pg_catalog;SELECT servicehub_status();SET search_path = ch03_trusted, pg_catalog;SELECT servicehub_status();SELECT ch03_trusted.servicehub_status();

The first unqualified call resolves to ch03_untrusted.servicehub_status(); after changing the path, it resolves to the trusted function. The explicitly qualified call is unambiguous regardless of path.

The security lesson is broader than this toy function. If a user can create objects in any schema searched before a trusted object, users of unqualified names can be tricked into resolving attacker-controlled objects. PostgreSQL documentation explicitly treats writable schemas in a search path as a trust boundary.

7. The public schema: inspect, do not assume

Fresh PostgreSQL 15+ databases have safer defaults than older clusters, but upgraded databases can preserve historical privileges. Chapter 01 already ran REVOKE CREATE ON SCHEMA public FROM PUBLIC as a course convention. Verify rather than assume:

SELECT n.nspname AS schema_name,       n.nspowner::regrole AS owner,       has_schema_privilege('PUBLIC', n.oid, 'USAGE') AS public_usage,       has_schema_privilege('PUBLIC', n.oid, 'CREATE') AS public_createFROM pg_catalog.pg_namespace AS nWHERE n.nspname = 'public';\dn+ public

PUBLIC in a privilege statement means every role, not the schema named public. The capitalization difference is useful in prose, but PostgreSQL keywords are case-insensitive unless quoted.

8. Hands-on lab: predict resolution before executing

1. Connect as servicehub_app and record current_database(), search_path, and current_schemas(true).
2. Predict what to_regclass('work_orders') will return, then verify it.
3. As an administrator, create ch03_alpha and ch03_beta, each with a table named marker containing a different text value.
4. Set search_path = ch03_alpha, ch03_beta, pg_catalog, query unqualified marker, then reverse the two schemas and repeat.
5. Query both tables with schema-qualified names and explain why those results no longer depend on search_path.
6. Run the function-shadowing demonstration above, then clean up the teaching schemas.

CREATE SCHEMA ch03_alpha;CREATE SCHEMA ch03_beta;CREATE TABLE ch03_alpha.marker(value text PRIMARY KEY);CREATE TABLE ch03_beta.marker(value text PRIMARY KEY);INSERT INTO ch03_alpha.marker VALUES ('alpha');INSERT INTO ch03_beta.marker VALUES ('beta');SET search_path = ch03_alpha, ch03_beta, pg_catalog;SELECT * FROM marker;  -- alphaSET search_path = ch03_beta, ch03_alpha, pg_catalog;SELECT * FROM marker;  -- betaSELECT * FROM ch03_alpha.marker;SELECT * FROM ch03_beta.marker;RESET search_path;DROP SCHEMA ch03_alpha CASCADE;DROP SCHEMA ch03_beta CASCADE;DROP SCHEMA ch03_trusted CASCADE;DROP SCHEMA ch03_untrusted CASCADE;

9. Production judgment and next bridge

Treat namespace configuration as part of security and deployment policy. Record database boundaries, schema owners, CREATE/USAGE grants, and role-specific search paths. Privileged code should not trust arbitrary caller paths, and migrations should make object targets explicit.

Lesson 2 adds the other half of the namespace story: PostgreSQL roles. You will distinguish login identity from effective identity, inherited privileges from SET ROLE, and privileges from ownership.

Authoritative references

• PostgreSQL 18 — Schemas and search_path
• PostgreSQL 18 — Client Connection Defaults / search_path
• PostgreSQL 18 — CREATE SCHEMA
• PostgreSQL 18 — Privileges
• PostgreSQL 18 — Session/System Information Functions
• PostgreSQL 18 — Object Identifier Types