PostgREST is a standalone web server that allows you to access your pgEdge Postgres database cluster with RESTful API calls. It is very simple to use because the API is built on the existing structure and inherits permissions you've already defined in your PostgreSQL database. You use a simple configuration file to specify the schema objects that will be exposed through the API. While the PostgreSQL server monitors the default listener port, for libpq-styled calls to the server, the PostgREST server monitors port 3000 for API-styled calls.

In this blog, we'll cover: 

  • the easy installation of PostgREST with pgEdge

  • easy configuration steps that add security

  • using the API endpoints with CURL commands. 

The examples in this post are intended for use with the pgEdge platform; if you're using the pgEdge Cloud interface, pgEdge Cloud will manage some configuration details for you. To keep our examples simple, we'll use a single table created by pgbench. pgbench is a benchmarking tool distributed with PostgreSQL, and installed by the pgEdge platform.

Please note that in this post we will set up PostgREST without a reverse proxy in front of it. According to the PostgREST documentation, a reverse proxy is needed to add HTTPS capability and secure communication with PostgREST. If you are deploying a solution like this, you must also run a reverse proxy like NGINX or Caddy with an SSL certificate configured. pgEdge Cloud can take care of this for you, if you go that route.

Creating PostgreSQL roles for secure access with PostgREST

PostgREST uses three types of roles to manage access to the database via the API/http:

  • an authenticator role

  • a user role with a Json Web Token (JWT) - used for POST and PATCH

  • an anonymous role (for guest users without a JWT) - used for GET

If you are connecting with an API request to port 3000, the authenticator role determines if a role has sufficient permissions to use the API by checking incoming requests for a JWT. If a request includes a JWT that includes a role claim, PostgREST will use the privileges associated with the role and allow that user to UPDATE and INSERT (via POST and PATCH calls).

If a request does not include a JWT with a role claim, PostgREST will switch to the anonymous role, and allow that user to query the database with a GET call.  The privileges associated with this role are defined in the PostgREST configuration file. The server evaluates these privileges before any object access is granted.

The following code snippet demonstrates the best practices to create Postgres roles that allow anonymous and trusted users to query the database with PostgREST: 

/* Role for GET commands */
CREATE ROLE get_role NOLOGIN;

GRANT USAGE ON SCHEMA public TO get_role;
GRANT SELECT ON  public.pgbench_branches TO get_role;
CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'mysecretpassword';
GRANT get_role TO authenticator;

/* Role for PUT commands */
CREATE ROLE put_role NOLOGIN;
GRANT put_role TO authenticator;

GRANT USAGE ON schema public TO put_role;
GRANT ALL ON public.pgbench_branches TO put_role;

Installing and starting PostgREST

The nodectl CLI makes it easy to install PostgREST on your cluster. nodeCtl will check the system you’re on and download and install the correct version of PostgREST and create the configuration and service files for you. 

Use the following command to install 

./nodectl um install postgrest


Modify the PostgREST configuration file (located in /opt/pgedge/postgrest/postgrest.conf) to match your database name and update the anonymous web role to get_role:

  db-uri = "postgres://localhost:5432/demo"
  db-schemas = "public"
  db-anon-role = "get_role"

Restart PostgREST to pick up these new configuration settings:

./nodectl restart postgrest

Testing a SELECT statement via GET 

You can use a curl command to test the read only calls with the get_role user. The URL in the following example is comparable to executing the following SELECT statement:

SELECT * FROM pgbench_branches;

Invoke:
curl http://localhost:3000/pgbench_branches

Curl returns:
 [{"bid":1,"bbalance":0,"filler":null}]

The following command includes a WHERE statement to filter the result set; to execute the following query: 

SELECT * FROM pgbench_branches WHERE bid=5;

Invoke:
curl http://localhost:3000/pgbench_branches?bid=eq.5

Curl returns:
[{"tid":5,"bid":1,"tbalance":0,"filler":null}]

Check out the PostgREST documentation for more select query options. 

Setting up authentication for a trusted user

To update a table in the PostgreSQL database with the PostgREST API, you have to create a trusted user. This involves creating a secret and using that secret to sign a Json Web Token (JWT). 

On Linux you can create a randomly generated secret on the command line:

export LC_CTYPE=C
< /dev/urandom tr -dc A-Za-z0-9 | head -c32

For our example, our secret is: TK9UrGc530bKHtwLDcmOS7Znv11F29SK

Modify postgres.conf in /opt/pgedge/postgrest/postgrest.conf by appending:

jwt-secret = "TK9UrGc530bKHtwLDcmOS7Znv11F29SK"

Restart postgREST again to pick up these changes:

./nodectl restart postgrest

You need to use the secret to create a token; use the interface at the following site to encode the information into the token: 

https://jwt.io/#debugger-io

At the site:

Replace the payload data with “role”: ”put_role”

Replace your-256-bit-secret with the secret created above, 

TK9UrGc530bKHtwLDcmOS7Znv11F29SK

Copy the token generated for use in the example to insert into a table below. 

Testing an INSERT statement 

Now that you have this token saved, use it in the curl command to authenticate with PostgREST. Executing a POST command like the one below will add a single row to pgbench_branches. The following curl command is comparable to executing the following SQL statement:

INSERT INTO pgbench_branches (bid, bbalance, filler) VALUES (2,0,null);

curl http://localhost:3000/pgbench_branches -X POST \
     -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidGVzdF9hdXRoIn0.WDFZRwba3v_0-Q9ixRxmNBNewOYFOaOPCXPLxcGTtmg"   \
     -H "Content-Type: application/json" \
     -d '{"bid": 2,"bbalance":0,"filler":null}'

Selecting again from that table will now show two records:

curl http://localhost:3000/pgbench_branches
Curl returns:
[{"bid":1,"bbalance":0,"filler":null},
 {"bid":2,"bbalance":0,"filler":null}]

Wrapping Up

In this post we explored how to configure and test PostgREST with pgEdge Platform. 

PostgREST serves a fully RESTful API from any existing PostgreSQL database. It provides a clean, compliant, fast API; visit the PostgREST project in Github at: https://github.com/PostgREST/postgrest.  The official PostgREST documentation is available at: https://postgrest.org/en/stable/.

If you are new to pgEdge distributed PostgreSQL, you can download the components via Github or access the Platform with extensions at: https://www.pgedge.com/download. Stay tuned for more exciting developments from pgEdge, like how pgEdge Cloud can automate set up and increase security around using PostgREST.