PostgreSQL + PostgREST

docker-compose.yml

version: '3'
services:
  postgrest:
    image: postgrest/postgrest
    environment:
      PGRST_DB_URI: postgres://authenticator:passwrod@db:5432/app_db
      PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
      PGRST_JWT_SECRET: xxxxxxxxxx
      PGRST_DB_MAX_ROWS: 500
      PGRST_DB_POOL: 50
    depends_on:
      - db
  db:
    image: supabase/postgres
    ports:
      - "5444:5432"
    environment:
      POSTGRES_PASSWORD: xxxx
    volumes:
      - "./pgdata:/var/lib/postgresql/data"

  nginx:
    image: nginx:stable-alpine
    ports:
      - 8888:80
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf
    restart: always

nginx.conf

error_log                       /var/log/nginx/error.log warn;

events {
    worker_connections          1024;
}

http {

  limit_req_zone $binary_remote_addr zone=api:10m rate=1r/s;

  allow 172.19.0.0/24;
  deny all;

  upstream postgrest_server {
    server postgrest:3000;
  }

  keepalive_timeout           300000;
  types_hash_max_size         300000;
  proxy_connect_timeout       300000;
  proxy_send_timeout          300000;
  proxy_read_timeout          300000;
  send_timeout                300000;

  server {
    listen 80;
    client_max_body_size 1m;

    location /api/ {
      limit_req zone=api burst=5;
      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://postgrest_server/;
    }
  }
}

Grant user

create role userdb nologin;
grant all on schema public to userdb;

create role authenticator noinherit login password 'password';
grant userdb to authenticator;

API Endpoint

http://localhost:8888/api

Create JWT with role in payload

{
  "role": "userdb"
}

Use SDK

test.js

const postgrest = require('@supabase/postgrest-js')

const REST_URL = 'http://localhost:8888/api'
const accessToken = 'xxxxxxx'
const supabase = new postgrest.PostgrestClient(REST_URL, {
  headers: {
    'Authorization': 'Bearer ' + accessToken
  }
})

async function main() {
  const { data, error } = await supabase
    .from('todos')
    .select()

  if (error) {
    console.error(error)
  }

  console.log(data)
}

main();

Run:

node test.js