Schema Organisation

Namespace your tables, set search_path, and lock down public schema access

The analytics team needs their own tables in beer_db without touching the production tables. Without schemas, every table lives in public — a shared namespace where any user with CONNECT can accidentally CREATE TABLE analytics_cache and pollute the production catalog.\n\nSchemas are PostgreSQL's organisational tool: separate namespaces inside one database. But the public schema has a hidden default that lets any connected role create tables in it — a privilege that should almost always be revoked.

What Is a Schema?

A schema is a named namespace inside a database. Tables, views, functions, sequences, and types all live inside a schema. The default schema is called public.

database
  └── schema1
        └── table_a
  └── schema2
        └── table_a     ← same name, different namespace, no conflict

search_path: How PostgreSQL Resolves Names

When you write SELECT * FROM beers, PostgreSQL looks up beers in each schema listed in search_path in order:

SHOW search_path;
-- "$user", public

-- PostgreSQL checks:
--   1. Schema named after current_user (e.g. postgres)
--   2. public schema
-- The first match wins.

The Public Schema Security Problem

By default (PostgreSQL ≤ 14), any user with CONNECT on a database can CREATE tables in the public schema. This means:

An analytics intern can drop a public table by accident
A compromised application credential can create arbitrary tables
Trojan table attacks (a malicious pg_catalog.tables in public)

Best practice: Revoke CREATE on public from the PUBLIC role:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

PostgreSQL 15+ did this by default, but older versions still grant it.

search_path

A session-level list of schemas that PostgreSQL searches in order when resolving unqualified table names. The special value "$user" resolves to a schema with the same name as the current user (if it exists). Setting search_path lets you write beers instead of public.beers.

PUBLIC role

The word PUBLIC in GRANT/REVOKE statements refers to every role in the database — current and future. GRANT CONNECT ON DATABASE beer_db TO PUBLIC means every role can connect. REVOKE CREATE ON SCHEMA public FROM PUBLIC means no role (unless explicitly granted) can create objects in public.

🔌 Connect to the Beer Database

Before exploring schemas, connect to beer_db. Run psql -U beer_db -d beer_db to open a psql session.

psql -U beer_db -d beer_db

beer_db=>

🔍 See the Current search_path

Run SHOW search_path; to see which schemas are currently searched and in which order.

SHOW search_path;

search_path ----------------- "$user", public (1 row)

📁 Create an analytics Schema

Create a schema called analytics inside beer_db.

CREATE SCHEMA analytics;

CREATE SCHEMA

📊 Create a Table Inside the analytics Schema

Create a analytics.monthly_scores table with beer_id uuid, month date, avg_score numeric(4,2). Use the schema-qualified name so it lands in analytics, not public.

CREATE TABLE analytics.monthly_scores (beer_id uuid NOT NULL, month date NOT NULL, avg_score numeric(4,2));

CREATE TABLE

🗺️ Set search_path and Query Without Qualification

Set search_path TO analytics, public and then run SELECT * FROM monthly_scores; without the schema prefix. PostgreSQL resolves it to analytics.monthly_scores.

SET search_path TO analytics, public;

SELECT * FROM monthly_scores;

SET

🔒 Revoke CREATE on public from PUBLIC

Revoke the CREATE privilege on the public schema from the PUBLIC (all roles) pseudo-role. This closes the door to arbitrary table creation by any connected role.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE

Lab complete! Schema organisation is in place and public is locked:\n\n\n analytics schema : created ✅\n monthly_scores : in analytics ✅\n search_path : analytics, public ✅\n public CREATE : revoked from PUBLIC ✅\n

Enable JavaScript to run the live terminal and track your progress.