public

public public#

1. What is public in PostgreSQL?#

public is a schema, not a database, not a table.

📦 Database → 📁 Schemas → 📄 Tables

So here:

MyDB
└── Schemas (1)
   └── public

public is the default schema inside every PostgreSQL database.

2. Why does public exist?#

PostgreSQL creates public automatically so beginners can:

  • Create tables immediately

  • Avoid learning schemas at the start

  • Run SQL without qualification

Example:

CREATE TABLE users (...);

Postgres silently does:

CREATE TABLE public.users (...);

3. Default behavior of public#

By default:

Behavior

Status

Exists automatically

✅

In every database

✅

Writable by everyone

⚠️ YES

In search_path

✅

Beginner-friendly

✅

Production-safe

❌

4. Why public is dangerous in real systems#

This is very important.

Problems with public

  1. Everyone can create tables

  2. No ownership boundaries

  3. Name collisions

  4. Security risks

  5. Hard to audit

  6. Bad multi-app support

Example nightmare:

CREATE TABLE users (...);   -- app A
CREATE TABLE users (...);   -- app B

Same schema → conflict.

How PostgreSQL finds public (search_path)

Check:

SHOW search_path;

Default:

"$user", public
Meaning:

  1. Look in schema named after user

  2. Then look in public

That’s why tables “magically” work without schema names.

5. Who owns public?#

SELECT nspname, nspowner::regrole
FROM pg_namespace
WHERE nspname = 'public';

Usually:

public | postgres

But permissions are open unless you change them.

6. Best Practice (what professionals do)#

1️⃣ Keep public EMPTY

Do not put app tables there.

2️⃣ Lock it down

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

  • No accidental tables

  • No unauthorized access

3️⃣ Create real schemas

CREATE SCHEMA app;
CREATE SCHEMA auth;
CREATE SCHEMA audit;

4️⃣ Use explicit schema names

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

7. What public is still useful for#

You can keep public for:

  • Extensions (sometimes)

  • Shared utility views

  • Temporary experiments (dev only)

But never core business tables.

Important rule to remember

public is for learning — not for production.

Summary

  • ✔ public = default schema

  • ✔ Exists in every database

  • ✔ Auto-used by SQL

  • ❌ Open permissions by default

  • ❌ Not production-safe

  • ✅ Lock it down

  • ✅ Create app-specific schemas

Contents