pgrest

A simple RESTful service written in Go to proxy queries to PostgreSQL servers that are not connected to the internet.

Features

• Supports multiple postgres sources to query
• Hash-Based Message Authentication
• Brotli and GZIP compression support
• Streaming response to keep memory footprint low
• Server binary size < 10MB, Docker image only 16MB
• Output formats
  • JSON
  • JSONDataArray
  • CSV
  • Apache Arrow (Experimental)
  • Parquet (Experimental)

Security notice

You're opening up a way to directly query the database so make sure you handle things correctly.

• Make sure the user set in the connection string has the appropriate operation and access rights for configured clients.
• Ensure to keep your clientSecret confidential to prevent unauthorized access to a database.

Usage

Local development

Download dependencies and start PGRest.

cd src
go mod download
go run ./cmd/app/main.go

Docker

Example running PGRest using docker and mount/use a config file.

docker run --network host -v ./config/pgrest.conf:/config/pgrest.conf -e PGREST_CONFIG_PATH="/config/pgrest.conf" ghcr.io/sogelink-research/pgrest:latest

Docker compose

To start PGRest with a database with some mock data and readonly user run the following command.

docker compose up --build

Examples

Under ./examples some examples on how to use PGRest can be found for curl, node and html.

PGRest JS client

A basic Javascript Client can be installed from NPM or loaded from jsDelivr, information on usage can be found in the pgrest-client readme

Endpoints

Query

Run a query on a connection trough PGRest.

(POST) /api/{connection}/query

{
  "query": "SELECT station_id, temperature, humidity, wind_speed FROM weather_station_measurement WHERE station_id = 1",
  "format": "json"
}

property	description	default
query	The query to run	-
format	The response format, one of these options ['json', 'jsonDataArray', 'csv', 'arrow', 'parquet']	json

Authorization

Authorization on the server side utilizes a custom authentication scheme based on the Authorization header with a Bearer token. The token is structured as a base64-encoded string clientId.token, where the token is a SHA-256 HMAC (encoded in base64) generated from the POST body + UNIX timestamp using the clientSecret as the key. When a connection is configured with "auth": "public" authorization is skipped, use with cause!.

Authorization: Bearer <base64(clientId.token)>
X-Request-Time: <UNIX Timestamp (seconds)>

See examples/curl_example.sh for an example how to request using curl.

Status

Check the status of the server, can be used as health check.

(GET) /api/status

Returns 200/ok with JSON content

{
  "status": "ok",
  "started": "2024-07-18 14:56:24.474895508 +0000 UTC",
  "uptime": "1d 02h 12m 31s"
}

PGRest Configuration Guide

This document provides an overview of the configuration settings for PGRest as defined in the ./config/pgrest.conf file. PGRest tries to load the config file from ../config/pgrest.conf by default and /root/config/pgrest.conf for docker. The path to the config file can be set using the environment variable PGREST_CONFIG_PATH

Example

{
  "pgrest": {
    "port": 8080,
    "debug": true,
    "cors": {
      "allowOrigins": ["*"],
      "allowHeaders": ["*"],
      "allowMethods": ["POST", "OPTIONS"]
    },
    "maxConcurrentRequests": 15,
    "timeoutSeconds": 30
  },
  "connections": [
    {
      "name": "default",
      "connectionString": "postgres://readonly_user:readonly_password@pgrest-test-db:5432/postgres",
      "auth": "private",
    },
    ...
  ],
  "users": [
    {
      "clientId": "pgrest",
      "clientSecret": "98265691-8b9e-44dc-acf9-94610c392c00",
      "connections": [
        "default"
      ]
    },
    ...
  ]
}

Configuration Overview

The configuration for PGRest is structured into two main sections: pgrest and connections.

PGRest Settings

• port: The port on which PGRest will listen for incoming requests. Defaults to 8080.
• debug: This flag controls the log level, if set to false log level defaults to info. Default false.
• cors: Cross-Origin Resource Sharing settings.
  • allowOrigins: Specifies the origins that are allowed to access. Default ["*"]
  • allowHeaders: Specifies the allowed headers. Default ["*"]
  • allowMethods: Specifies the allowed methods. Default ["OPTIONS", "POST"]
• maxConcurrentRequests: Limits number of currently processed requests at a time across all users. Default 15.
• timeoutSeconds: The amount of seconds before a request times out.

Connections

This section defines the database connections that PGRest can use.

Connection

• name: Identifier for the connection.
• connectionString: The connection string used to connect to the PostgreSQL database.
• auth: (Do not use!, leave out or set to private) Can be set to public to ignore Authorization: Authorization header/user access will not be checked. Default private.

Users

Defines the list of users.

• clientId: Identifier for the client.
• clientSecret: A secret key for the client, will not be send between client/server.
• connections: An array of connection names where a user has access to.