PostgREST on RHEL 7.2

May 3, 2016 · 4 min read · PostgreSQL PostgREST ·

PostgREST is an HTTP server written in Haskell that serves a fully RESTful API from a PostgreSQL database. It generates the endpoints directly from the database schema so you do not have to write anything from scratch. You just download the binary, fire it up with a database connection string and you are good to go.

You won't find it as a .deb or .rpm package at the time of writing, so you have to install it manually and create your own startup scripts.

In this post I will describe how to do this on a Redhat Enterprise Linux (RHEL 7.2) server in a managed SELinux context. The PostgreSQL database instance runs on the same server in this example.

Note that RHEL 7.2 ships with PostgreSQL 9.2 which is not supported by PostgREST so you have to upgrade to version 9.3 or later from the PostgreSQL repositories.

Download, install and test the binary

Download the tarball from the github repo. Select the Centos binary for compatibility with RHEL, then untar it and move it to a directory on the load path:

# tar -xvf postgrest-0.3.1.1-centos.tar.xz
# mv postgrest /usr/bin/postgrest

Test the installation of the binary by invoking the --help dialogue:

# postgrest --help
Usage: postgrest DB_URL (-a|--anonymous ROLE) [-s|--schema NAME]
    [-p|--port PORT] [-j|--jwt-secret SECRET] [-o|--pool COUNT]
    [-m|--max-rows COUNT]
  PostgREST 0.3.1.1 / create a REST API to an existing Postgres database

Now that you have confirmed that PostgREST is accessible, create a test database called 'demo1' by following the Getting Started instructions here, or use an existing database, and run PostgREST:

# postgrest postgres://postgres:my_pass@localhost:5432/demo1 -a postgres -p 5438
   WARNING, running in insecure mode, JWT secret is the default value
   Listening on port 5438

where:

postgres:my_pass is the authenticator role (with password 'my_pass') that handles the initial HTTP requests. For the purposes of this example, it is the default PostgreSQL role;
demo1 is the name of the test database that will be exposed by the API;
-a postgres is the name of the role that will handle unauthenticated anonymous requests; and
-p 5438 is the port number that PostgREST will use to listen to HTTP requests. Note that by default PostgREST uses port 3000, but this port is managed by SELinux for other purposes, so use 5438 instead because it is unmanaged, and it is close to the default PostgreSQL port number so it is easy to remember.

Send a simple HTTP GET request to the PostgREST port 5438:

# curl http://localhost:5438

If all is well, PostgREST will return a JSON string with a list of tables and views that are accessible in the database schema:

[{"schema":"public","name":"competition","insertable":true},{"schema":"public","name":"director","insertable":true},{"schema":"public","name":"festival","insertable":true},{"schema":"public","name":"film","insertable":true},{"schema":"public","name":"film_nomination","insertable":true}]

It's that straight forward!

Open the firewall

The RHEL firewall on the server will block access from the network to the server, so we have to add the PostgREST port 5438 to the public zone and make it permanently available:

# firewall-cmd --zone=public --add-port=5438/tcp --permanent

Reload the firewall and check that the port is open:

# firewall-cmd --reload
# firewall-cmd --zone=public --list-ports
   443/tcp 139/tcp 22/tcp 5432/tcp 5438/tcp 80/tcp 445/tcp 137/tcp

Now you should be able to access the API from a client workstation (where my_server_IP is the hostname or IP address of your PostgREST server):

# curl http://my_server_IP:5438

Create a systemd service

RHEL 7.2 uses systemd to manage the initialisation of services (yes, haters gonna hate), so create a system user to run the service and define a custom systemd service to automate the startup of PostgREST.

Create a system user postgrest with no login shell to run the PostgREST daemon:

# useradd -r -s /usr/bin/nologin postgrest

and create a service description /usr/lib/systemd/system/postgrest.service with the following contents:

[Unit]
Description=PostgREST Service
After=postgresql-9.5.service

[Service]
User=postgrest
Group=postgrest
ExecStart=/usr/bin/postgrest postgres://postgres:my_pass@localhost:5432/demo1 -a postgres -p 5438

[Install]
WantedBy=multi-user.target

Note that the entry 'After=postgresql-9.5.service' will cause the service to start after the initialisation of the database instance has been completed.

Enable the service and start it:

# systemctl enable postgrest
# systemctl start postgrest

It should now start automatically when the server starts up.

PostgREST API diagram by Archton.io is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.