Websites · Glossary
What is a Postgres schema?
A Postgres schema is a named namespace inside a database that holds tables, views, functions and other objects. One database can contain many schemas, and two schemas can each have a table called orders without clashing. Every new database starts with a schema called public, and the search_path setting decides which schema an unqualified name refers to.
Think of a database as a filing cabinet and schemas as its drawers. Each drawer holds its own set of tables, and permissions can be set per drawer.
Indunil Asanka · Co-founder
4 min read · Published
| Object | What it is | Example |
|---|---|---|
| Database | A separate collection of schemas; connections pick one | postgres |
| Schema | A namespace of objects inside a database | public, auth, site_kestrel |
| Table | Rows and columns inside a schema | site_kestrel.bookings |
| Role | A login or group that is granted rights on schemas and tables | site_kestrel_login |
What a schema is
In PostgreSQL, the hierarchy runs cluster, database, schema, object. The PostgreSQL documentation describes schemas as similar to directories at the operating system level, except that they cannot be nested. A fully qualified name such as shop.orders names the schema and the table. Without the schema part, Postgres looks through the schemas listed in search_path, which by default is the person's own schema name, then public. Schemas are created with CREATE SCHEMA and can be owned by a specific role.
How permissions work
A role needs the USAGE privilege on a schema to see objects in it, and separate privileges on each table to read or change rows. That makes schemas a clean boundary: a login role granted usage on one schema and none on others cannot query the others' tables at all. Supabase builds on the same idea: its own internal objects live in schemas such as auth and storage, your tables usually live in public, and its API can be told which schemas to expose. Row level security then works inside each table, deciding which rows a given person may see.
When it matters
Schemas matter when several applications or customers share one database. A platform can give each customer a schema of its own, with its own login role, so data stays separated without the cost of one database server per customer. This sits between a shared set of tables with a tenant id column on every row and a separate database for every tenant. Schemas also help a single app organise itself, for example keeping reporting views apart from transactional tables.
Common mistakes
Leaving everything in public and granting broad access to it. Forgetting that new tables in a schema do not inherit grants unless default privileges are set. Relying on search_path in functions, which can be hijacked if a schema earlier in the path holds an object with the same name. Exposing an internal schema through an API by accident. Treating schema separation as a replacement for row level security when people inside one tenant should still see different rows. And creating so many schemas that migrations across them become hard to run.
Where it shows up in OneCraft
When a capability needs data, the OneCraft agent calls enable_backend, which gives the site either a schema of its own in OneCraft's pool project, with a login role that can reach no other site, or a project on the owner's own Supabase account if they connected one. Every table must have row level security, and the agent checks new tables as an anonymous visitor and as a signed in person. Hosted schemas are included on every plan with no cap, or owners can connect their own Supabase account.
Questions people ask
What is the public schema in Postgres?
It is the schema every new database starts with. Unqualified table names land there by default because public is in the default search_path. Recent PostgreSQL versions no longer let every role create objects in public by default, so permissions on it should be set deliberately rather than assumed.
Is a schema the same as a database?
No. A database is a separate container that a connection chooses when it opens; queries cannot easily join tables across databases. A schema is a namespace inside one database, and one query can join tables from several schemas if the role has rights on them. In MySQL, by contrast, schema and database mean the same thing.
How do I list schemas in Postgres?
In psql, type \dn to list schemas, or run SELECT schema_name FROM information_schema.schemata. Supabase's table editor shows a schema selector at the top, and its SQL editor accepts the same queries. Expect to see system schemas such as pg_catalog and information_schema alongside your own.
Should each customer get their own schema?
It depends on the number of customers and how different their data is. A schema per customer gives strong separation and easy per customer backups, but every migration must run in every schema. A shared schema with a tenant column scales to more customers with simpler migrations, relying on row level security for separation.
Can row level security replace schemas?
For many apps, yes: one set of tables with policies that filter rows by tenant or person works well. Schemas add a second wall, because a role without usage on a schema cannot see its tables even if a policy is wrong. Platforms hosting many independent sites often use both.
Make one with websites
The button opens the generator with this use case already described. Change the wording to match your own.
Create a website with OneCraftRelated questions
- What is a connection pooler?What is a connection pooler? A proxy that lets many clients share a few Postgres connections. Session and transaction pooling compared, with examples.
- What is a database migration?What is a database migration? A numbered SQL file that changes a database schema and lives in the code repository, so every copy can be rebuilt the same way.
- What is a Supabase project?What is a Supabase project? One Postgres database with sign in, file storage and an API on top. What a project includes, what it costs, and how keys work.
- What is a storage bucket?What is a storage bucket? A named container for files with its own access rules and size limits. Public and private buckets compared, with examples.
Written and checked by the OneCraft team. Last checked .