Postgres All-in-One

13 Mar 20259 min read

Building a Self-Hosted Supabase-Like Environment on PostgreSQL

This document details how to use PostgreSQL as a multi-purpose database backend by consolidating the functionality of several systems (MongoDB, Redis, Elasticsearch, etc.) into a single instance. We cover:

  • Using PostgreSQL as a document store with jsonb
  • Fast, in-memory caching using unlogged tables
  • Full-text search with tsvector
  • Installing and configuring useful PostgreSQL extensions
  • Authentication using pgcrypto and pgjwt
  • Implementing Row-Level Security (RLS) with Role-Based Access Control (RBAC)
  • Expiring JWT tokens, supporting refresh tokens, and rate limiting
  • Exposing your schema via GraphQL with pg_graphql
  • Middleware considerations

1. PostgreSQL as a Multi-Purpose Database

1.1 Document Store with jsonb

You can store JSON documents and query them efficiently:

CREATE TABLE users_data (
    id SERIAL PRIMARY KEY,
    data JSONB
);
 
INSERT INTO users_data (data)
VALUES ('{"name": "Alice", "age": 30, "skills": ["Go", "Elixir"]}');
 
SELECT * FROM users_data WHERE data @> '{"name": "Alice"}';

1.2 Caching with Unlogged Tables (Poor Man’s Redis)

Unlogged tables avoid the WAL for faster performance (at the cost of durability):

CREATE UNLOGGED TABLE cache (
    key TEXT PRIMARY KEY,
    value TEXT,
    expiry TIMESTAMP
);
 
INSERT INTO cache (key, value, expiry)
VALUES ('session:123', 'some_data', now() + interval '10 minutes');
 
SELECT * FROM cache WHERE expiry > now();

1.3 Full-Text Search with tsvector (Elasticsearch-like)

PostgreSQL’s full-text search can replace Elasticsearch for many use cases:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT
);
 
CREATE INDEX documents_search_idx ON documents USING GIN (to_tsvector('english', content));
 
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('fast & search');

2. PostgreSQL Extensions and Consolidated Setup

2.1 Useful Extensions

  • pg_cron: For scheduling tasks.
  • pgvector: For vector similarity search.
  • pgai: For AI-based search/integration.
  • pg_graphql: To expose a GraphQL API directly.
  • pgcrypto & pgjwt: For cryptography and JWT support.
  • pg_mooncake: For real-time change capture.
  • PostgREST: For creating RESTful endpoints.

2.2 Installation Options

You can install extensions via:

  1. A Bash Script to upgrade an existing PostgreSQL instance.
  2. A Dockerfile to build a preconfigured image.

Example Bash Script (works on Ubuntu/Debian & Fedora/RHEL)

#!/bin/bash
set -e  # Exit on error
PG_VERSION="16"
echo "Installing PostgreSQL $PG_VERSION extensions..."
 
if [[ -f /etc/debian_version ]]; then
    echo "Detected Ubuntu/Debian..."
    sudo apt update
    sudo apt install -y \
        postgresql-$PG_VERSION \
        postgresql-contrib \
        postgresql-$PG_VERSION-pgvector \
        postgresql-$PG_VERSION-pgcrypto \
        postgresql-$PG_VERSION-pgjwt \
        postgresql-$PG_VERSION-pg_cron \
        postgresql-$PG_VERSION-pg_graphql
fi
 
if [[ -f /etc/redhat-release ]]; then
    echo "Detected Fedora/RHEL..."
    sudo dnf install -y \
        postgresql$PG_VERSION-server \
        postgresql$PG_VERSION-contrib \
        postgresql$PG_VERSION-pgvector \
        postgresql$PG_VERSION-pgcrypto \
        postgresql$PG_VERSION-pgjwt \
        postgresql$PG_VERSION-pg_cron \
        postgresql$PG_VERSION-pg_graphql
fi
 
echo "Enabling extensions in PostgreSQL..."
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pgvector;"
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;"
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pgjwt;"
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_cron;"
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_graphql;"
 
echo "PostgreSQL extensions installed successfully!"

Example Dockerfile

FROM postgres:16
 
# Install necessary tools and build dependencies
RUN apt-get update && apt-get install -y wget gnupg2 build-essential git curl
 
# Download and store the PGDG key in the keyring directory
RUN curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgres.gpg
 
# Add the PGDG apt repository using the signed-by option
RUN echo "deb [signed-by=/usr/share/keyrings/postgres.gpg] http://apt.postgresql.org/pub/repos/apt bullseye-pgdg main" \
    > /etc/apt/sources.list.d/pgdg.list
 
# Update apt and install required packages
RUN apt-get update && \
    apt-get install -y postgresql-16-cron postgresql-contrib-16 && \
    rm -rf /var/lib/apt/lists/*
 
# (Optional) Install additional extensions from source or third-party if needed
# For example, install pgjwt and pg_graphql from source as described previously
 
# Set up extensions on first run
COPY init-extensions.sql /docker-entrypoint-initdb.d/
 
EXPOSE 5432

Contents of init-extensions.sql:

CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgjwt;
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS pg_graphql;

Build and run with:

docker build -t my-postgres .
docker run -d --name postgres -e POSTGRES_PASSWORD=mysecret -p 5432:5432 my-postgres

3. Authentication, RBAC, and RLS

3.1 Enabling Authentication Extensions

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgjwt;

3.2 Creating a Users Table with RBAC

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role TEXT DEFAULT 'user',
    created_at TIMESTAMP DEFAULT now()
);
 
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

3.3 Defining RLS Policies

  • User Policy: Regular users see only their own data. 
    CREATE POLICY user_policy ON users
    FOR SELECT
    USING (username = current_setting('jwt.claims.username', true));
  • Admin Policy: Admins can bypass and see all rows. 
    CREATE POLICY admin_policy ON users
    FOR SELECT
    USING (current_setting('jwt.claims.role', true) = 'admin');
  • Update Policy: Users update only their own rows. 
    CREATE POLICY user_update_policy ON users
    FOR UPDATE
    USING (username = current_setting('jwt.claims.username', true));
  • Insert Policy: Default inserts allow only the ‘user’ role. 
    CREATE POLICY user_insert_policy ON users
    FOR INSERT WITH CHECK (role = 'user');

3.4 Inserting Sample Users

INSERT INTO users (username, password, role)
VALUES
  ('admin', crypt('SuperSecurePassword', gen_salt('bf')), 'admin'),
  ('alice', crypt('alicepassword', gen_salt('bf')), 'user');

3.5 JWT Authentication Functions

Authenticate User & Issue Access Token (Expires in 1 Hour)

CREATE OR REPLACE FUNCTION authenticate_user(_username TEXT, _password TEXT)
RETURNS TEXT AS $$
DECLARE
    user_record RECORD;
    token TEXT;
BEGIN
    SELECT id, username, role INTO user_record
    FROM users
    WHERE username = _username
      AND password = crypt(_password, password);
 
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
 
    token := jwt.sign(
        json_build_object(
            'user_id', user_record.id,
            'username', user_record.username,
            'role', user_record.role,
            'exp', extract(epoch from now() + interval '1 hour')
        ),
        'supersecretkey'
    );
    RETURN token;
END;
$$ LANGUAGE plpgsql;

Verify Access Token

CREATE OR REPLACE FUNCTION verify_token(_token TEXT)
RETURNS JSON AS $$
DECLARE
    payload JSON;
BEGIN
    payload := jwt.verify(_token, 'supersecretkey');
    IF (payload->>'exp')::BIGINT < extract(epoch from now()) THEN
        RETURN NULL;  -- Token expired.
    END IF;
    RETURN payload;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;  -- Token invalid or error.
END;
$$ LANGUAGE plpgsql;

4. Refresh Tokens and Rate Limiting

4.1 Refresh Tokens

Refresh tokens are long-lived (e.g., 7 days) and stored for later exchange.

Create Refresh Tokens Table

CREATE TABLE refresh_tokens (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    token TEXT UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

Generate a Refresh Token (Expires in 7 Days)

CREATE OR REPLACE FUNCTION generate_refresh_token(_user_id INT, _username TEXT, _role TEXT)
RETURNS TEXT AS $$
DECLARE
    rtoken TEXT;
BEGIN
    rtoken := jwt.sign(
      json_build_object(
         'user_id', _user_id,
         'username', _username,
         'role', _role,
         'exp', extract(epoch from now() + interval '7 days')
      ),
      'superrefreshkey'
    );
    INSERT INTO refresh_tokens (user_id, token, expires_at)
      VALUES (_user_id, rtoken, now() + interval '7 days');
    RETURN rtoken;
END;
$$ LANGUAGE plpgsql;

Exchange a Refresh Token for a New Access Token

CREATE OR REPLACE FUNCTION refresh_access_token(_refresh_token TEXT)
RETURNS TEXT AS $$
DECLARE
    payload JSON;
    new_token TEXT;
BEGIN
    payload := jwt.verify(_refresh_token, 'superrefreshkey');
    IF (payload->>'exp')::BIGINT < extract(epoch from now()) THEN
         RETURN NULL; -- Refresh token expired.
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM refresh_tokens WHERE token = _refresh_token) THEN
         RETURN NULL;
    END IF;
 
    new_token := jwt.sign(
         json_build_object(
              'user_id', payload->>'user_id',
              'username', payload->>'username',
              'role', payload->>'role',
              'exp', extract(epoch from now() + interval '1 hour')
         ),
         'supersecretkey'
    );
    RETURN new_token;
END;
$$ LANGUAGE plpgsql;

4.2 Rate Limiting (Fixed Window per Minute)

A simple rate limiter tracks the number of requests per user per minute.

Create Rate Limits Table

CREATE TABLE rate_limits (
    user_id INT,
    period_start TIMESTAMP NOT NULL,
    count INT DEFAULT 0,
    PRIMARY KEY (user_id, period_start)
);

Check and Update Rate Limit (Allow 60 Requests/Minute)

CREATE OR REPLACE FUNCTION check_rate_limit(_user_id INT)
RETURNS BOOLEAN AS $$
DECLARE
    current_period TIMESTAMP := date_trunc('minute', now());
    current_count INT;
    limit CONSTANT INT := 60;
BEGIN
    INSERT INTO rate_limits (user_id, period_start, count)
    VALUES (_user_id, current_period, 1)
    ON CONFLICT (user_id, period_start)
    DO UPDATE SET count = rate_limits.count + 1
    RETURNING count INTO current_count;
 
    IF current_count > limit THEN
         RETURN FALSE;  -- Rate limit exceeded.
    END IF;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Clean Up Old Rate Limits with pg_cron

SELECT cron.schedule(
    'cleanup_rate_limits',
    '0 * * * *',
    $$DELETE FROM rate_limits WHERE period_start < now() - interval '1 hour'$$
);

5. Exposing Your Schema via GraphQL with pg_graphql

5.1 Installing and Enabling pg_graphql

First, ensure the extension is available and enable it:

CREATE EXTENSION IF NOT EXISTS pg_graphql;

5.2 Using pg_graphql

The extension auto-generates a GraphQL schema from your tables. For example, if you have a posts table, you can run:

SELECT graphql('{ posts { id, title, content } }');

The GraphQL query returns data in JSON format.

5.3 Integration with RLS and RBAC

  • Session Variables:
    Before executing GraphQL queries, set session variables with JWT claims: 
    SET LOCAL jwt.claims.username = 'alice';
SET LOCAL jwt.claims.role = 'user';
  • RLS Policies Apply:
    The same RLS policies you defined on your tables (e.g., restricting users to only see their own posts) will automatically filter the data returned by GraphQL.

6. Middleware Considerations

The middleware that:

  • Extracts the JWT from HTTP requests,
  • Verifies the token,
  • Sets session parameters on the database connection

Should reside in your application layer.
While you can write stored procedures in PostgreSQL for some logic, handling HTTP, token extraction, and session management is best done in your app (e.g., Node.js, Python, Go, etc.). This approach ensures your database stays focused on enforcing RLS and RBAC while the app handles request routing and authentication.

7. Summary & Next Steps

This guide demonstrates how to build a self-hosted, Supabase-like environment on PostgreSQL that includes:

  • Multi-purpose data storage: Using JSONB, unlogged tables, and full-text search.
  • Extensions: Installing pg_cron, pgvector, pg_graphql, pgcrypto, pgjwt, etc.
  • Authentication: Secure user authentication with password hashing and JWTs.
  • Authorization: Combining RBAC with Row-Level Security to control data access.
  • Token Management: Issuing short-lived access tokens with refresh tokens for renewal.
  • Rate Limiting: Protecting your API endpoints with a fixed-window rate limiter.
  • GraphQL API: Exposing your database schema securely via pg_graphql.
  • Middleware: Implementing the necessary middleware in your application layer.

Feel free to adjust the expiration times, request limits, and policies to best fit your needs. This setup provides a robust foundation for a unified, secure, and scalable backend that replaces multiple separate systems with a single PostgreSQL instance.

Graph View

Backlinks

  • No backlinks found