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

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