PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. Available as an open web service to help you get started in minutes. This tutorial gives you a walk-through on how to get started.

• If you have not already done so, sign up for an Eyevinn OSC account

To create a PostgreSQL database navigate to the PostgreSQL service in the Eyevinn OSC web console. Click on the button "Create psql-db" and enter a name of the instance and a password.

Based on the IP and port the URI to your PostgreSQL database is postgres://postgres:guide@<IP>:<PORT>/postgres.

In our example it is postgres://postgres:[email protected]:10538/postgres.

Use the psql command line tool to access the database. The tool can be installed with for example Homebrew.

% brew install libpq

% psql -h 172.232.131.169 -p 10538 -U postgres
Password for user postgres:

Enter the password guide that you set when you created the database.

The first thing we’ll do is create a named schema for the database objects which will be exposed in the API. We can choose any name we like, so how about “api.” Execute this and the other SQL statements inside the psql prompt you started.

create schema api;

Then we create a table todos as an example.

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');

Next make a role to use for anonymous web requests. When a request comes in, PostgREST will switch into this role in the database to run queries.

create role web_anon nologin;

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

The web_anon role has permission to access things in the api schema, and to read rows in the todos table.

It’s a good practice to create a dedicated role for connecting to the database, instead of using the highly privileged postgres role. So we’ll do that, name the role authenticator and also grant it the ability to switch to the web_anon role :

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

It is a good practice to store the database credentials as a secret and refer to it when creating an instance. Before we setup a PostgREST instance we will create a secret where we will store the URI with credentials to the database. Navigate to the PostgREST service in Eyevinn Open Source Cloud web console. Go to the tab "Service Secrets" and click on "New Secret".

In our example the URI is postgres://authenticator:[email protected]:10538/postgres and this is what we will store in the a secret named dburl.

Now we can create the PostgREST instance by going to the tab "My Postgrests" and click on "Create postgrest".

We give it the name guide and reference the secret we created in step 2 as the URI to the database. Provide the web_anon role and schema api that we created.

When the instance is up and running it is ready to serve web requests at the address shown on the instance card, in our case it is https://eyevinnlab-guide.postgrest-postgrest.auto.prod.osaas.io,

There are many nice graphical API exploration tools you can use, but for this tutorial we’ll use curl because it’s likely to be installed on your system already. Open a new terminal (leaving the one open that PostgREST is running inside). Try doing an HTTP request for the todos.

% curl https://eyevinnlab-guide.postgrest-postgrest.auto.prod.osaas.io/todos
[{"id":1,"done":false,"task":"finish tutorial 0","due":null}, 
 {"id":2,"done":false,"task":"pat self on back","due":null}]

With the current role permissions, anonymous requests have read-only access to the todos table. If we try to add a new todo we are not able.

% curl -X POST -H 'Content-Type: application/json' \
  -d '{"task": "do bad thing"}' \
  https://eyevinnlab-guide.postgrest-postgrest.auto.prod.osaas.io/todos
{"code":"42501","details":null,"hint":null,"message":"permission denied for table todos"}

There we have it, a basic API on top of the database deployed and ready to serve your applications with data.