Clusters, Databases, and Schemas

Postgres stacks three levels of namespace, and people routinely collapse the first two. A cluster is one running server with one data directory, one set of roles and one port. A database is an isolated namespace inside that cluster which a session connects to and cannot leave. A schema is a folder of tables inside a database.

Getting the levels straight answers a surprising share of the questions that reach a DBA. Why a table the migration definitely created does not appear in \dt. Why the join to the reporting database will not parse. Why a role created for one database shows up in all of them. Cartwheel keeps this simple on purpose: one cluster on pg-primary, one database called cartwheel, and two schemas inside it — public for the tables the app writes and analytics for the reporting objects the dashboard reads.

The Cluster

initdb creates a data directory, and one process tree serves it. Everything the previous topic described lives at this level: the shared memory region, the WAL, the background processes, the port. So do roles and tablespaces, which are stored once for the whole cluster rather than per database. That is why cartwheel_analytics, created while connected to cartwheel, is immediately visible from every other database on the same server.

On Debian the packaging keeps one directory per major version and cluster name, and gives you a command to list them.

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory
18  main    5432 online postgres /var/lib/postgresql/18/main

That single row is the whole cluster: version 18, the cluster name main, port 5432, and the data directory that holds every byte of Cartwheel's data plus the WAL that describes how it got there. A second cluster on the same host would be a second data directory, a second port, a second set of background processes and a second set of roles — genuinely separate servers that happen to share hardware. Because the boundary is this wide, cluster-level events are cluster-level emergencies: the transaction id wraparound scenario in Chapter 7 shuts down the whole thing, not one database.

Databases Are Walls, Not Folders

A session connects to exactly one database and stays there. The documentation states the limit without qualification: it is not possible to access more than one database per connection. There is no SELECT … FROM otherdb.public.orders in this engine, and no amount of privilege grants will produce one. Each database has its own catalogue, its own set of schemas, and its own copy of every extension it uses.

When data really does have to cross that wall, the answer is a foreign data wrapper — postgres_fdw, or the older dblink module. Both work, and both are a network round trip wearing the syntax of a join. The planner's information about the far side is limited, joins are often executed by pulling rows across the wire, and a query that looked like a two-table join becomes two queries and a transfer.

This is where habits from MySQL do real damage. There, "database" means what Postgres calls a schema, and joining across two of them is ordinary SQL. Port that layout to Postgres by creating one database per service on one cluster and every cross-service query turns into a foreign-data-wrapper call — plus a separate connection pool per database, because a pooled connection cannot switch databases either.

template0, template1, and postgres

CREATE DATABASE does not build a database from a specification. It copies an existing one, and by default it copies template1. Anything sitting in template1 is therefore inherited by every database created after it: install an extension there and it appears, unasked, in every future database on the cluster. That is occasionally what you want for a site-wide convention, and it is a trap the rest of the time, because "clean" no longer means clean.

template0 is the pristine copy that must never be modified after initdb. It exists so a database can be created with nothing in it but the standard objects, which is exactly what a pg_dump restore needs, and it is also the only way to create a database with a different encoding or locale than the cluster's own. The third database, postgres, is nothing special: an empty landing spot so that tools and administrators have somewhere to connect before they know which database they want.

Schemas and search_path

A schema is a namespace inside one database. Unqualified names, orders rather than public.orders, are resolved against search_path, and the default value is worth reading literally.

SHOW search_path;
--  "$user", public

SET search_path = analytics, public;
SELECT count(*) FROM daily_orders;   -- analytics.daily_orders
SELECT count(*) FROM orders;         -- public.orders

The first element, "$user", means "a schema named after the connecting role, if one exists"; when none exists the entry is simply ignored, which is how it goes unnoticed for years. The second is public. Change the setting and the same unqualified query reads different tables, resolving each name against the first schema in the list that contains it. Nothing about the SQL text records which schema was meant.

For Cartwheel that means the dashboard's queries against analytics and the application's queries against public are distinguished by a session setting rather than by the statement. A migration run by the wrong role, or a cron job that never set the path, resolves each name against whatever it finds first and reports success.

The public Schema Since 15

Before PostgreSQL 15, every user could create objects in the public schema of any database they could connect to. Since 15, CREATE is no longer granted to PUBLIC there, and the schema is owned by the pg_database_owner role, so by default the database's owner is the one who can create objects in it.

The detail that turns this into a support ticket is the scope. The new default applies to newly created clusters and to databases created after the upgrade, while an upgraded cluster or a restored dump keeps whatever permissions public already had. Chapter 14 covers the role and privilege model that makes this predictable. Until then, expect the same migration script to work on the old production database and fail with a permission error on a database created last week, on the same server, from the same file.

search_path as a Security Surface

A function declared SECURITY DEFINER executes with its owner's privileges. If its search_path is whatever the caller happened to set, the caller can create an object that shadows one the function meant to use (a table, a function, an operator) and the function will call it with the owner's rights. The temporary-object schema makes this worse, because it is normally writable by anyone and is searched first by default.

CREATE FUNCTION analytics.refresh_daily_orders() RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
-- trusted schemas first, pg_temp explicitly last
SET search_path = pg_catalog, analytics, pg_temp
AS $$ ... $$;

The SET clause attaches a fixed search_path to the function definition, so the body resolves names the same way no matter who calls it or what their session settings say. Listing pg_temp last is the part people leave out, and it is the part that matters: without it the temporary schema can be searched before the real one, and an attacker who can create a temporary table has chosen which code your definer function runs. Chapter 3 returns to this when Cartwheel's functions and triggers are written, and Chapter 14 puts it beside row-level security.