postgres-core-schema-design

Quick Reference :

PostgreSQL schema design fixes four classes of mistake on day one : identifier-casing chaos ("camelCase" forces every consumer to quote every reference), the serial foot-gun (an implicit sequence with broken privilege semantics, deprecated in favor of GENERATED ... AS IDENTITY since v10), a writable public schema that lets any logged-in role create objects in the trust-by-default namespace, and SECURITY DEFINER functions whose unqualified references can be hijacked by a caller-controlled search_path. These four decisions are made once and ALWAYS at table-creation time : retrofitting them later means a coordinated migration and a recompile of every dependent function. This skill encodes the decisions you make BEFORE the first CREATE TABLE.

A PostgreSQL database is one logical container with N schemas inside it. A schema is a namespace for tables, indexes, views, functions, types, sequences, and operators. Connection-level access is per-database (one CONNECT privilege per database, no cross-database queries except via FDW or dblink). Inside a database, name resolution walks the search_path GUC left-to-right and resolves the first match. The default search_path is "$user", public : a per-user schema (created on demand, never auto-created), then the shared public schema. PostgreSQL 15 changed the security default : on a freshly initialized cluster, public no longer grants CREATE to role PUBLIC : pre-existing databases upgraded to v15 keep their pre-v15 permissive grant. Therefore : on any database that PRE-DATES v15 you MUST REVOKE CREATE ON SCHEMA public FROM PUBLIC explicitly. Every SECURITY DEFINER function MUST pin search_path via SET search_path = ... in its definition, with pg_temp last, otherwise a malicious caller can substitute objects via their own temporary schema.

When To Use This Skill :

ALWAYS use this skill when :

Starting a new database : naming conventions, schema layout, identity-column choice are decided once
Designing a multi-tenant database : schema-per-tenant vs RLS vs database-per-tenant tradeoff
Writing or auditing any SECURITY DEFINER function : search_path must be pinned
Onboarding an existing database whose public schema is wide-open and you suspect v14-or-earlier defaults
Choosing between serial / bigserial and GENERATED ... AS IDENTITY for a new primary key column
Picking singular vs plural table names, FK column naming, primary-key column naming