Schemas

PostgREST can expose a single or multiple schema’s tables, views and functions. The active database role must have the usage privilege on the schemas to access them.

Single schema

To expose a single schema, specify a single value in db-schemas.

db-schemas = "api"

This schema is added to the search_path of every request using Transaction-Scoped Settings.

Multiple schemas

To expose multiple schemas, specify a comma-separated list on db-schemas:

db-schemas = "tenant1, tenant2"

To switch schemas, use the Accept-Profile and Content-Profile headers.

If you don’t specify a Profile header, the first schema in the list(tenant1 here) is selected as the default schema.

Only the selected schema gets added to the search_path of every request.

Note

These headers are based on the “Content Negotiation by Profile” spec: https://www.w3.org/TR/dx-prof-conneg

GET/HEAD

For GET or HEAD, select the schema with Accept-Profile.

HTTPCurl

GET /items HTTP/1.1
Accept-Profile: tenant2

Other methods

For POST, PATCH, PUT and DELETE, select the schema with Content-Profile.

HTTPCurl

POST /items HTTP/1.1
Content-Profile: tenant2

{...}

You can also select the schema for Stored Procedures and OpenAPI.

Restricted schemas

You can only switch to a schema included in db-schemas. Using another schema will result in an error:

HTTPCurl

GET /items HTTP/1.1
Accept-Profile: tenant3

{...}

{
  "code":"PGRST106",
  "details":null,
  "hint":null,
  "message":"The schema must be one of the following: tenant1, tenant2"
}

Dynamic schemas

To add schemas dynamically, you can use In-Database Configuration plus config reloading and schema cache reloading. Here are some options for how to do this:

• If the schemas’ names have a pattern, like a tenant_ prefix, do:

create or replace function postgrest.pre_config()
returns void as $$
  select
    set_config('pgrst.db_schemas', string_agg(nspname, ','), true)
  from pg_namespace
  where nspname like 'tenant_%';
$$ language sql;

• If there’s no name pattern but they’re created with a particular role (CREATE SCHEMA mine AUTHORIZATION joe), do:

create or replace function postgrest.pre_config()
returns void as $$
  select
    set_config('pgrst.db_schemas', string_agg(nspname, ','), true)
  from pg_namespace
  where nspowner = 'joe'::regrole;
$$ language sql;

• Otherwise, you might need to create a table that stores the allowed schemas.

create table postgrest.config (schemas text);

create or replace function postgrest.pre_config()
returns void as $$
  select
    set_config('pgrst.db_schemas', schemas, true)
  from postgrest.config;
$$ language sql;

Then each time you add an schema, do:

NOTIFY pgrst, 'reload config';
NOTIFY pgrst, 'reload schema';