Schemas

schemas Schemas#

What is a Schema in PostgreSQL?#

A schema is a namespace inside a database.

Think of it like:

  • 📁 Folder inside a database

  • 🏷️ Logical container for tables, views, functions

  • 🔐 Security boundary

Database → Schemas → Tables / Views / Functions

In MyDB → Schemas (1) you see:

Schemas
└── public
This means:

  • Your database MyDB has only one schema

  • That schema is the default public schema

What is the public schema?

public is:

  • Created automatically

  • Exists in every database

  • Writable by everyone by default

Default behavior:

CREATE TABLE users (...);

Actually becomes:

CREATE TABLE public.users (...);

Why public is dangerous#

In real projects / production:

  • ❌ Anyone can create objects

  • ❌ Name collisions happen

  • ❌ No clear ownership

  • ❌ Hard to manage permissions

  • ❌ Bad for multi-app databases

That’s why professional DBAs do NOT use public directly.

Best Practice: Use multiple schemas#

Instead of putting everything in public, we do this:

Schema

Purpose

app

Application tables

auth

Authentication / users

audit

Logs & audit tables

ref

Reference / lookup data

admin

DBA-only objects

Real-world analogy#

PostgreSQL

Operating System

Database

Hard Drive

Schema

Folder

Table

File

You don’t put everything in one folder, right? Same idea.

Let’s create proper schemas#

Run this in MyDB:

CREATE SCHEMA app AUTHORIZATION sherullahmohtat;
CREATE SCHEMA auth AUTHORIZATION sherullahmohtat;
CREATE SCHEMA audit AUTHORIZATION sherullahmohtat;

Now pgAdmin will show:

Schemas (4)
├── app
├── auth
├── audit
└── public

🔒 Lock down public#

This prevents accidental usage:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
Now:

  • ❌ No one can create tables in public

  • ✅ You control where objects go

Create tables the RIGHT way#

Instead of:

CREATE TABLE users (...);

Do:

CREATE TABLE app.users (...);
CREATE TABLE auth.accounts (...);
CREATE TABLE audit.logs (...);

Clear, safe, professional.

🔍 How PostgreSQL finds tables (search_path)#

Postgres uses:

SHOW search_path;

Default:

"$user", public

You can set it per role:

ALTER ROLE sherullahmohtat SET search_path = app, auth;

Now:

CREATE TABLE users (...);

→ goes into app.users

Summary (DBA mindset)

  • ✔ Schema = namespace

  • ✔ public is default but unsafe

  • ✔ Always create app-specific schemas

  • ✔ Lock down public

  • ✔ Control ownership & permissions

Contents