Back End Setup

The back-end setup of Prospector primarily involves installing PostgreSQL and PostgREST. Optionally, for improved performance and security, an NGINX reverse proxy can be setup in front of the database (PostgreSQL) and RESTful API (PostgREST). There are some script and config files in the repository (https://github.com/sfcta/prospector/tree/master/db-backend) if you would like to set this up on a Linux machine. Here is step-by-step setup process on Windows:

1. PostgreSQL
2. PostgREST
3. NGINX

1. PostgreSQL

Download and install PostgreSQL for windows - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. The latest version (v11.5) should work well.

• During the installation process, you would have chosen a password for the superuser postgres.
• An user interface called PgAdmin 4 would also be installed.
• Open PgAdmin 4 post installation (this opens in a browser window) and connect to the PostgreSQL server (which is probably going to be detected automatically).
• Once the connection to server is established, you should see Databases, Login/Group Roles, and Tablespaces within it.
• Right-click on Login/Group Roles and create -> Login/Group Role... called anon. This will be used to access certain view via the RESTful API (PostgREST):

CREATE ROLE anon WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

• Also, create a new login called staff. Set a password and choose options to generate the following definition of the user in SQL. Make sure to add anon role in the Membership tab.

-- User: staff
-- DROP USER staff;

CREATE USER staff WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION;

GRANT anon TO staff;

• You can use the staff login to perform most operations for Prospector.
• Create a new database called prospector and set the owner to staff:

CREATE DATABASE prospector
    WITH 
    OWNER = staff
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

• At this point, it might be best to disconnect and reconnect to the PostgreSQL server as staff.
• Create a new Schema called api within prospector, make staff the owner, and grant usage privilege to anon:

-- DROP SCHEMA api ;

CREATE SCHEMA api
    AUTHORIZATION staff;

GRANT ALL ON SCHEMA api TO staff;

GRANT USAGE ON SCHEMA api TO anon;

• Create a new dummy table for testing purposes called mytable:

-- Table: api.mytable

-- DROP TABLE api.mytable;

CREATE TABLE api.mytable
(
    v1 "char",
    v2 "char"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE api.mytable
    OWNER to staff;

• Create a new view of mytable in api schema called mytable_view. Make sure that anon role has select permission on it.

-- View: api.mytable_view

-- DROP VIEW api.mytable_view;

CREATE OR REPLACE VIEW api.mytable_view AS
 SELECT mytable.v1,
    mytable.v2
   FROM api.mytable;

ALTER TABLE api.mytable_view
    OWNER TO staff;

GRANT ALL ON TABLE api.mytable_view TO staff;
GRANT SELECT ON TABLE api.mytable_view TO anon;

• Important Note: the table mytable has been created in api schema just for testing purposes. In production, all the tables would be in separate schema(s) and only the views of them (required for visualization through Prospector) will be created in Views in api schema.

Now you are ready to move on to the next step of installing PostgREST!

2. PostgREST

First, add C:\Program Files\PostgreSQL\11\bin to the Path environment variable. This is because PostgREST requires some of those libraries. All required documentation can be found here -https://postgrest.org.

• Download PostgREST for Windows - https://github.com/PostgREST/postgrest/releases/latest. Again, the latest release should work. At the time of writing this document, it was v6.0.2 - https://github.com/PostgREST/postgrest/releases/download/v6.0.2/postgrest-v6.0.2-windows-x64.zip
• It is just an exe file postgrest.exe. Put it under C:\postgrest.
• Now, it needs a configuration file as an argument. An example is given here - https://github.com/sfcta/prospector/blob/master/db-backend/api-server/postgrest.conf.
• Create a text file in C:\postgrest called pgapi.conf with contents as shown below. Substitute <mysecretpassword> with the password you chose for staff login.

db-uri = "postgres://staff:<mysecretpassword>@localhost:5432/prospector"
db-schema = "api"
db-anon-role = "anon"

• Run postgrest.exe from command prompt with pgapi.conf as an argument.

c:\postgrest>postgrest.exe pgapi.conf
Listening on port 3000
Attempting to connect to the database...
Connection successful

• Now, this should provide you with access to all tables in api schema (with anon select permission) through your browser! Since the port is 3000, try http://localhost:3000/mytable_view in the browser.

Since there are no records in the table, it should just show you an empty list []. If you are able to see this, then you have successfully set up PostgREST!

You can now upload a bunch of tables, create views with appropriate permissions and access them from Prospector for visualizing data!

3. NGINX

For additional security, you could also setup NGINX reverse proxy. Ideally, it should be setup on a different server from where PostgreSQL has been installed. However, these instructions show how it can be setup on the same machine.

• Download NGINX installation file for Windows - https://nginx.org/en/download.html. The latest version is 1.17.3.
• Unzip the contents of the zip file in C:\nginx-1.17.3.
• Setup the config file - conf\nginx.conf (you may want to make a copy of the as backup before editing it). In the server block of the nginx.conf add a new location /api/ before the root location '/'.

...
    server {
...
        location /api/ {
            default_type  application/json;
            proxy_hide_header Content-Location;
            add_header Content-Location  /api/$upstream_http_content_location;
            proxy_set_header  Connection "";
            proxy_http_version 1.1;
            proxy_pass http://localhost:3000/;
            proxy_redirect default;
        }

        location / {
            root   html;
            index  index.html index.htm;
        }
...

• Instructions for running NGINX on Windows can be found here - http://nginx.org/en/docs/windows.html. Go to command prompt and start nginx.

C:\nginx-1.17.3>start nginx.exe

• This creates log files in C:\nginx-1.17.3\logs.
• If you make changes to the configuration then reload NGINX.

C:\nginx-1.17.3>nginx -s reload

• To stop NGINX, use the followig command.

C:\nginx-1.17.3>nginx -s quit

• Now, test access to api schema view by going to http://localhost/api/mytable_view in the browser. If it runs successfully, you are all set!
• If not, check C:\nginx-1.17.3\logs\error.log for clues to fix the configuration setting.