Schemas group tables, views, and functions within a database—like folders. public is default; multi-tenant apps sometimes use one schema per tenant.
Creating and using schemas
CREATE SCHEMA IF NOT EXISTS billing;
CREATE TABLE billing.invoices (
id BIGSERIAL PRIMARY KEY,
amount NUMERIC(12,2) NOT NULL
);
SET search_path TO billing, public;
SELECT * FROM invoices;Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.
search_path security
Always qualify object names in migrations and security definer functions—unqualified names resolve via search_path, which attackers can manipulate if careless.
Permissions per schema
GRANT USAGE ON SCHEMA billing TO app_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA billing TO app_role;Important interview questions and answers
- Q: Schema vs database?
A: Database is isolated connection boundary; schema is namespace inside a database. - Q: Why SET search_path?
A: Controls default schema resolution for unqualified table names.
Self-check
- What is the default schema?
- Why qualify table names in security-sensitive SQL?
Pitfall: Unqualified names in security definer functions—set search_path explicitly.
Interview prep
- Schema vs database?
Database is connection boundary; schema is namespace inside it.
- search_path risk?
Unqualified names resolve via search_path—security concern in functions.