Databases, Schemas, Search Paths, and Ownership

PostgreSQL has several naming and control layers before a query ever reaches a table. A server instance contains databases. Each database contains schemas. Schemas contain objects such as tables, views, functions, sequences, types, and indexes. Roles own those objects, and privileges decide who may use them. The search_path setting decides how an unqualified name such as orders is resolved into a specific object such as app.orders.

The outcome of this lesson is practical: you should be able to choose when to create another database, when to create another schema, how to make object names resolve predictably, and how to avoid ownership mistakes that block migrations or accidentally expose objects. This matters early in a PostgreSQL course because later lessons on tables, permissions, extensions, functions, and application access all depend on these boundaries.

How PostgreSQL Organizes Names

A PostgreSQL cluster is one running server environment managed by one data directory. Inside that cluster are databases. A client connects to exactly one database at a time. Ordinary SQL queries cannot join tables across databases because each database has its own catalog entries, schemas, and object namespace. Databases are therefore a coarse boundary, useful for separate applications, separate lifecycle management, or administrative isolation.

A schema is a namespace inside one database. Schemas are lighter than databases: objects in different schemas can be queried together in one transaction, one connection, and one SQL statement. A schema lets you separate application objects from reporting objects, extension objects, staging tables, or tenant-specific objects without creating another database.

Internally, PostgreSQL records schemas in pg_namespace, tables and many table-like objects in pg_class, and roles in pg_roles. A table name is not globally unique by itself. The pair of schema plus object name is what identifies it inside a database. When you write sales.orders, PostgreSQL does not need to guess. When you write only orders, it searches schemas according to search_path.

Search Path Mechanics

search_path is a comma-separated list of schemas. PostgreSQL checks each schema in order when resolving an unqualified object name. The special token $user means a schema with the same name as the current role, if one exists. The common default is effectively "$user", public. That means a role named analyst will first look for objects in schema analyst, then in public.

Temporary schemas have special behavior. If a session creates a temporary table, PostgreSQL uses a session-local temporary schema. Temporary tables can shadow permanent tables for unqualified table names. This is useful for scratch work, but it is another reason production SQL should qualify important object names when ambiguity would be dangerous.

Name resolution is not only a convenience feature. It affects security and correctness. If a migration, function, or application query uses unqualified names, changing search_path can make the same SQL touch a different object. For application code, prefer schema-qualified names in migrations and security-sensitive SQL. For interactive work, a controlled search_path can reduce typing while staying understandable.

Ownership and Privileges

Ownership is not the same as permission. The owner of an object can alter or drop it, grant privileges on it, and usually manage its definition. Privileges such as SELECT, INSERT, USAGE, and CREATE control what other roles may do. A role may have permission to read a table without owning it. A migration role may own tables while an application role can only read and write rows.

Schema permissions are often misunderstood. To access an object inside a schema, a role commonly needs USAGE on the schema plus the relevant privilege on the object. To create objects in a schema, the role needs CREATE on that schema. Granting SELECT on a table is not enough if the schema itself is hidden from the role.

PostgreSQL ships with a public schema in new databases. Treat it as a shared namespace, not as a dumping ground. Many teams create an application schema such as app, move application tables there, and restrict broad create privileges in public. That produces clearer ownership and reduces the chance that unqualified names resolve to unexpected objects.

Syntax Anatomy

The core commands are small, but their effects are important. CREATE DATABASE creates a separate database. CREATE SCHEMA creates a namespace inside the connected database. ALTER ... OWNER TO changes who controls an object. GRANT and REVOKE adjust privileges. SET search_path changes name resolution for the current session, while ALTER ROLE ... SET search_path or ALTER DATABASE ... SET search_path changes defaults for future sessions.

CREATE ROLE course_owner LOGIN;
CREATE ROLE course_app LOGIN;
CREATE DATABASE course_lab OWNER course_owner;

This example creates two roles and a database owned by course_owner. The owner role is suitable for migrations and schema management. The application role should normally receive only the privileges it needs. CREATE DATABASE must be run from another database, often postgres, because you cannot create the database you are currently connected to.

Example 1: Schema Names Are Separate Namespaces

Two schemas can contain tables with the same unqualified name. This is not a conflict because the full name includes the schema.

CREATE SCHEMA app;
CREATE SCHEMA reporting;

CREATE TABLE app.events (
    id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE reporting.events (
    id integer PRIMARY KEY,
    label text NOT NULL
);

INSERT INTO app.events VALUES (1, 'application event');
INSERT INTO reporting.events VALUES (1, 'reporting event');

SELECT label FROM app.events;
SELECT label FROM reporting.events;

The first SELECT returns application event. The second returns reporting event. The table name events is reused safely because each object lives in a different schema. This is the main reason schemas are useful for organizing related objects inside one database.

Example 2: Search Path Chooses the First Match

When a name is unqualified, PostgreSQL checks search_path from left to right. The following statements use the two tables from the previous example.

SET search_path = reporting, app;
SELECT label FROM events;

SET search_path = app, reporting;
SELECT label FROM events;

With reporting first, SELECT label FROM events returns reporting event. With app first, the same unqualified query returns application event. This deterministic behavior is convenient, but it can surprise teams when session defaults differ between local shells, migration tools, and application connection pools.

Example 3: Schema Usage and Table Privileges Work Together

This example grants an application role enough access to read and write one schema without making it the owner.

GRANT USAGE ON SCHEMA app TO course_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO course_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO course_app;

The first grant allows the role to refer to names inside app. The second grant covers existing tables. The ALTER DEFAULT PRIVILEGES command affects tables created later by the role that runs the command, so it should be issued by the table-creating owner role. Without the default privilege step, today’s tables may work while next week’s newly created table fails for the application.

Example 4: Ownership Is for Change Control

An application role should not usually own its production tables. Ownership gives too much definition-level control. A separate owner role makes migrations explicit.

ALTER TABLE app.events OWNER TO course_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER ROLE course_app SET search_path = app;

The first statement makes course_owner responsible for the table definition. The second removes broad object creation in the shared public schema. The third gives future course_app sessions a convenient default path. Even with that default, migrations should still use names such as app.events so they behave the same under every session configuration.

Design Choices and Trade-Offs

Create a separate database when you need a strong administrative boundary: separate backups, separate connection targets, separate extensions, or a clean lifecycle for dropping and recreating all objects. The cost is that ordinary SQL cannot query across those databases directly, so cross-application reporting becomes more complex.

Create a schema when the objects belong in the same database but need organization, naming separation, or different privileges. Schemas work well for application modules, extension isolation, audit objects, staging imports, and reporting layers. The trade-off is that search_path becomes part of the environment, so unqualified names must be controlled.

Use one owner role for schema changes and narrower runtime roles for applications and analysts. This separation makes accidental destructive changes less likely. The trade-off is operational discipline: migrations, default privileges, and object ownership must be kept consistent, or access failures will appear later.

Failure Modes and Troubleshooting

Symptom: a query fails with relation "events" does not exist. Cause: the table exists, but not in any schema currently listed in search_path, or the role lacks schema visibility. Diagnose: run SHOW search_path; and query information_schema.tables for the table’s schema. Correct: use the schema-qualified name, set the expected search path, or grant USAGE on the schema.

Symptom: an application can see a schema but gets permission denied for table events. Cause: the role has USAGE on the schema but lacks table privileges, or a new table was created after grants were applied. Diagnose: inspect table grants with \dp app.events in psql or check information_schema.role_table_grants. Correct: grant privileges on existing tables and configure default privileges from the owner role.

Symptom: a migration fails with must be owner of table. Cause: the connected role has data privileges but does not own the table and is not a member of the owning role. Diagnose: check the owner in pg_class joined to pg_namespace, or use \dt+ app.events. Correct: run migrations as the owner role, transfer ownership deliberately, or grant role membership according to your administration model.

Symptom: a function or script touches the wrong table. Cause: an unqualified name resolved through a different search_path than expected. Diagnose: log or display SHOW search_path;, then repeat the query with schema-qualified names. Correct: qualify names in stored code and migrations, and set safe role or database defaults for interactive sessions.

Security, Performance, and Reliability Implications

The main security risk is name confusion combined with excessive creation privileges. If untrusted roles can create objects in schemas that appear early in another user’s search_path, unqualified function or relation names may resolve unexpectedly. Restrict CREATE on shared schemas and qualify names in privileged code.

The performance cost of schemas and search path lookup is usually not the limiting factor. The larger performance concern is operational: a query may accidentally use a different table than intended, such as a small staging table instead of the production table, producing misleading tests or reports. Reliability improves when ownership, grants, and default privileges are part of migrations rather than manual afterthoughts.

Hands-On Lab

Prerequisites: access to a disposable PostgreSQL database as a role allowed to create schemas and roles. Do not run this lab in a shared production database. If your environment does not permit role creation, use existing disposable roles and adapt the names.

  1. Connect to a scratch database with psql.
  2. Create two schemas, two same-named tables, and sample rows using the Example 1 SQL.
  3. Run the Example 2 SQL and confirm that the result changes when the path order changes.
  4. Create or choose an application role, then apply the grants from Example 3.
  5. Open a new session as the application role and run SELECT label FROM app.events;. It should return application event.
  6. Try CREATE TABLE app.should_fail(id integer); as the application role. It should fail unless you intentionally granted CREATE on the schema.
  7. Verify ownership with \dt+ app.events or a catalog query.
SELECT n.nspname AS schema_name,
       c.relname AS object_name,
       r.rolname AS owner_name
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_roles AS r ON r.oid = c.relowner
WHERE n.nspname IN ('app', 'reporting')
  AND c.relname = 'events'
ORDER BY n.nspname;

The verification query should show two rows, one for app.events and one for reporting.events, with the role that owns each table. For cleanup, drop only the lab objects you created: DROP SCHEMA reporting CASCADE; and DROP SCHEMA app CASCADE;. Drop disposable roles only after confirming they own no remaining objects.

Assessment Exercises

  1. You have one application database with operational tables and monthly reporting tables. When would you use a separate schema instead of a separate database, and what problem would that avoid?
  2. A query works in psql but fails in the application with relation does not exist. List the first three checks you would perform and why.
  3. Explain why granting SELECT on app.events may still not let a role read from that table.
  4. Design a role split for migrations and runtime access. Which role owns tables, which role reads and writes rows, and which grants are needed?
  5. A team wants to rely on search_path for all SQL. Identify one convenience, one correctness risk, and one rule you would require before approving it.

Summary

Databases are PostgreSQL’s coarse connection and administration boundary. Schemas are namespaces inside a database. search_path turns unqualified names into specific schema objects by checking schemas in order. Ownership controls who can change object definitions, while privileges control what other roles may do with those objects. Good PostgreSQL design uses these layers deliberately: separate databases for separate lifecycles, schemas for organized namespaces, qualified names for important SQL, owner roles for migrations, and narrow runtime grants for applications.