Simple template to deploy a PostgREST service.

Set the PGRST_DB_URI variables to your Postgres connection string or reference, for example ${{Postgres.DATABASE_PRIVATE_URL}}.

Example on how to get a basic working service running (copied from the PostgREST documentation):

Create a schema for database objects that will be exposed in our API:

create schema api;

Create a todos table:

create table api.todos (
  id int primary key generated by default as identity,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

Make a role to use for anonymous web requests:

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

Set the PGRST_DB_SCHEMAS variable to api (the schema you created).

Set the PGRST_DB_ANON_ROLE variable to web_anon (the role we created).

Deploy the service and visit your service's HTTP endpoint at /todos to view your todos.

Because the role we created only grants select access on the api.todos table, anonymous users will not be able to modify the table.

Authentication

To authenticate users with the API generate a JWT secret and set the PGRST_JWT_SECRET variable.

For example with OpenSSL:

openssl rand -base64 32

Store the output in the PGRST_JWT_SECRET variable and set the PGRST_JWT_SECRET_IS_BASE64 to true.

Follow the PostgREST tutorial on how to use this secret to enable authentication for a user.