Schemas and roles

How the schemas and database roles of a project fit together.

#Schemas

Schema	Contains
`public`	Your tables, views and functions. Exposed through the REST API.
`auth`	Project users (`auth.users`) and their sessions.
`storage`	Buckets and file metadata (`storage.objects`).
`extensions`	Installed extensions such as `vector`.

#API roles

Requests through the REST API, Realtime and Storage run as one of three roles, depending on the key and token they carry:

Role	Used when
`anon`	A request uses the anon key without a signed-in user
`authenticated`	A request carries a signed-in user's access token
`service_role`	A request uses the service_role key. It bypasses Row Level Security, so keep it on the server.

#Owner role

Direct connections use the project's owner role, p_<ref>_owner. It owns the public schema and is the user in your connection strings.

#Your own roles

For a reporting tool, a background worker or a teammate who should only read some schemas, create a role in Studio under Database › Roles. Each role:

is named p_<ref>_r_<name> and can only connect to this project's database;
can log in with its own password (shown once; make a new one any time) through the pooler on ports 5432 and 6543, TLS required;
gets Read only or Read and write rights on the schemas you pick, for existing and future tables, views, sequences and functions, or no grants so you grant exactly what it needs in SQL as the owner;
has its own connection limit (up to your plan's direct connections) and an optional expiry date, after which Postgres refuses its logins;
is limited by Row Level Security. On tables with RLS on, add a policy for it, for example:

SQL

create policy "reporting reads orders" on public.orders
  for select to p_<ref>_r_reporting using (true);

A project has up to 20 roles of its own. Roles move with the project to another region and are deleted with it. Deleting a role closes its connections; tables or functions it created belong to the owner afterwards.