Publications

Supabase Under the Hood: PostgreSQL Architecture, Row Level Security, Self-Hosting and Production Deployment

Published: May 24, 2026 · Author: Marcin Szewczyk-Wilgan

Supabase is gaining popularity as an open-source Firebase alternative, but most online resources stop at "how to build a todo app" tutorials. This analysis goes deeper – it shows what Supabase does under the hood with PostgreSQL, how Row Level Security works in the context of APIs and WebSockets, what Supavisor is and why it replaced PgBouncer, and how to deploy Supabase in production on your own server. This article is based on the current Supabase architectural documentation (2025–2026).

Introduction – what is Supabase and why it matters

Supabase is a Backend-as-a-Service (BaaS) platform that places PostgreSQL at the center of its entire architecture. Unlike Firebase – where the database (Firestore) is a proprietary Google product with a limited query model – Supabase gives developers full, unrestricted access to a relational database. SQL, indexes, ACID transactions, materialized views, PL/pgSQL functions, extensions (e.g., PostGIS, pgvector) – everything is available because standard PostgreSQL runs underneath.

The Supabase philosophy rests on three principles: openness (every component is an independent open-source project), composability (services can run independently), and no abstraction (developers see the database, not a hidden middleware layer). This makes Supabase interesting not just for startups looking for a quick backend, but also for infrastructure engineers who want to understand how to build a modern application stack around PostgreSQL.

PostgreSQL as the central engine of Supabase

In a typical web architecture, the database serves as a "storage" layer – data goes in and out, while all business logic, authorization, and routing live in the application layer (e.g., Express.js, Django, Laravel). Supabase inverts this model. PostgreSQL is not a passive storage here – it is the central computational engine responsible for data persistence, authorization (Row Level Security), event generation (WAL logical replication), file metadata storage (Storage), and user identity management (the auth schema).

Supabase grants full administrative privileges to the project owner. You can connect directly via TCP, run EXPLAIN ANALYZE, create custom indexes, install extensions – something impossible with Firebase, DynamoDB, or many managed database services. This transparency has practical consequences: if you ever decide to leave Supabase, you take with you a standard PostgreSQL database that can run on any server.

The component stack: what each service does

Supabase is not a single monolithic program – it is a set of independent open-source services, each performing one specific function. Together they form a complete backend, but they can also run independently.

PostgREST – the database as an API

PostgREST is a server written in Haskell that reads the database schema on startup and automatically generates a REST API for all tables, views, and functions. No endpoint code is needed – an HTTP request like GET /rest/v1/products?price=gte.100&order=name is translated into an optimized SQL query with filters and sorting.

Importantly, PostgREST does not implement its own authorization logic. Instead, it switches the database role to the one specified in the user's JWT token (typically authenticated or anon) and sets session variables (GUCs) with the token data. This means PostgreSQL's Row Level Security policies automatically filter the data – PostgREST is merely a translator, and all security logic lives in the database.

To avoid the N+1 query problem when fetching relational data (e.g., orders with line items), PostgREST leverages PostgreSQL's built-in JSON aggregation functions (json_agg, jsonb_build_object). A single HTTP request returns a nested JSON structure instead of requiring dozens of separate requests.

GoTrue – authentication and JWT tokens

GoTrue is an authentication service written in Go (originally a fork of Netlify's project). It manages user registration, login (password, OAuth, SSO), sessions, and JWT tokens. User data is stored in an isolated auth schema within PostgreSQL.

After successful login, GoTrue generates a short-lived access token and a single-use refresh token. The access token payload contains the user's identifier (sub), their role, and optional metadata – the same data that RLS policies read via the auth.uid() function.

GoTrue supports Multi-Factor Authentication (MFA) – after TOTP, SMS, or email verification, the token is upgraded to aal2 level, enabling RLS policies that require two-factor verification for sensitive operations. The system protects against token theft through strict refresh token rotation – reuse of a revoked token immediately invalidates the entire session family.

Realtime – database changes via WebSocket

The Realtime server, built in Elixir on the Phoenix framework, maintains thousands of simultaneous WebSocket connections, leveraging the lightweight processes of the BEAM virtual machine (Erlang VM). Its job is to stream database changes to connected clients in real time.

The mechanism relies on PostgreSQL logical replication – Realtime connects to a replication slot and decodes changes from the WAL journal (using the wal2json plugin). Every INSERT, UPDATE, and DELETE operation in the database is converted to a JSON structure and sent to clients subscribed to that table.

Storage API and Edge Functions

The Storage API (Node.js/TypeScript) is an S3-compatible file storage service. Its key feature: file metadata is stored in PostgreSQL, meaning file access is governed by the same RLS policies as table data. Uploading a file to a private bucket requires a JWT token with appropriate permissions – no separate middleware needed.

Edge Functions (Deno runtime) allow running custom TypeScript code on the server side – e.g., payment API integration, email sending, or webhook processing. They operate outside the database request-response cycle, so they don't block PostgreSQL connections.

Kong – the API gateway

Kong (based on Nginx, written in Lua) serves as the API Gateway – the single entry point for all client traffic. It implements path-based routing, validates API keys, enforces rate limiting, and injects authorization headers into internal services.

For unauthenticated requests (the client sends a project API key but has no user session token), Kong generates an internal JWT with the anon role and forwards it to the target service. For authenticated requests, it passes the user's session token through unmodified. This means internal services (PostgREST, GoTrue) never need to validate API keys themselves – that happens exclusively at the gateway level.

Row Level Security – authorization at the database level

How RLS works in Supabase

In a traditional web architecture, authorization lives in application code – the server checks whether a user has the right to view or modify a given resource. The problem is that every new endpoint requires separate authorization logic, and a mistake in one place opens a vulnerability in the entire system.

Supabase moves authorization into the database itself using PostgreSQL's native Row Level Security mechanism. An RLS policy is a SQL expression that the database evaluates on every read or write. If the expression returns false for a given row, the user won't see it – regardless of how they connected to the database (via PostgREST, direct TCP connection, or an admin tool).

A typical policy looks like this: auth.uid() = user_id – the user only sees rows where the user_id column matches their identifier from the JWT token. The auth.uid() function is a Supabase helper that reads the session variable (GUC) set by PostgREST at the beginning of each transaction.

This mechanism has a subtle trap worth knowing. For unauthenticated requests (the anon role), the auth.uid() function returns NULL. In SQL logic, the expression NULL = user_id evaluates to false (not true or NULL), so the policy silently returns no rows – without an error message. For predictable behavior, policies should include an explicit check: auth.uid() IS NOT NULL AND auth.uid() = user_id.

The way PostgREST isolates contexts between users is also important. At the start of each transaction, PostgREST sets GUC variables (request.jwt.claims, database role) to the data from the current client's JWT token. After the transaction completes (COMMIT or ROLLBACK), the variables are wiped to an empty string. This ensures a pooled connection doesn't "remember" a previous user's context – even if Supavisor assigns the same physical connection to another client.

Since 2025, Supabase automatically enables RLS on every newly created table using a trigger on the ddl_command_end event. If a table is created by an external migration tool (e.g., Prisma, Drizzle), the trigger immediately executes ALTER TABLE ... ENABLE ROW LEVEL SECURITY. By default – without defined policies – a table with RLS enabled returns no rows, implementing a "secure by default" approach.

RLS performance optimization

RLS introduces computational overhead – every row must be evaluated by the policy. With large tables and a simple auth.uid() = user_id call, PostgreSQL recalculates the auth.uid() value separately for each row. The optimization is to wrap the call in a subselect: (SELECT auth.uid()) = user_id. This forces the query planner to create an initPlan – the value is computed once and cached for the duration of the entire query.

For complex authorization logic (e.g., role systems, per-organization permissions), Supabase recommends Custom Claims – injecting the user's role directly into the JWT token via an Auth Hook (a PL/pgSQL function called during token generation). This way, the RLS policy checks the role from the token ((auth.jwt() ->> 'user_role') = 'admin') instead of performing expensive JOINs with a roles table on every query.

Since 2025, Supabase also supports native PostgreSQL Column-Level Security. While RLS controls which rows a user can see, column-level privileges determine which fields within those rows are visible. Sensitive columns (e.g., Social Security numbers, financial data, PII) can be excluded from PostgREST API responses even if the RLS policy allows access to the row. Combined with RLS, this provides two-dimensional access control: row filtering and column filtering at the database level, without writing logic in application code.

WALRUS – RLS on the Realtime stream

The greatest architectural challenge of Supabase is enforcing Row Level Security on the Realtime stream. When an administrator updates a row in a table, the change should be visible to User A (the row owner) but not to User B (who lacks permissions). Decoding changes from the WAL is not enough – you also need to check who is allowed to see them.

Supabase solves this with the WALRUS module (Write Ahead Log Realtime Unified Security). For each change received from the WAL stream, the Realtime server calls a PostgreSQL function realtime.apply_rls(jsonb), which:

1. Sets the database context (role and JWT variables) to the identity of each WebSocket client subscribed to the relevant table.
2. Re-queries the database for the changed row (by primary key) – this time in the context of the specific user, so RLS automatically filters the result.
3. Applies subscription filters defined by the client (e.g., eq, gt, in).
4. Strips from the result any columns the user lacks SELECT privileges for (Column-Level Security).

This mechanism has limitations that must be understood. The table must have a primary key – without one, WALRUS cannot re-query the database and returns an HTTP 400 error. For DELETE operations, RLS cannot be applied retrospectively because the row physically no longer exists – the old record is broadcast to all subscribers. Therefore, tables with sensitive data should carefully configure REPLICA IDENTITY to avoid data leakage during deletion events.

Supavisor – next-generation connection pooling

PostgreSQL creates a separate system process for each connection – a model that doesn't scale with thousands of clients, especially in serverless environments (Vercel, AWS Lambda) where each function invocation opens a new connection. Supabase historically used PgBouncer, but its single-threaded architecture became a bottleneck.

Supavisor is a connection pooler written in Elixir (in partnership with Dashbit), designed to handle millions of simultaneous connections. It offers three operating modes, each suited to a different architectural scenario:

The key innovation in Supavisor 1.0 is solving the prepared statements problem in Transaction mode. PgBouncer forced developers to disable prepared statements because after a transaction ended, the client could be assigned a different physical connection that didn't know the compiled plan. Supavisor solves this through broadcasting – when a client sends PREPARE, Supavisor propagates the plan to all connections in the pool. Benchmark: 1,003,200 simultaneous connections at 20,000 queries per second without latency degradation.

Security: asymmetric keys, Splinter and GitHub scanning

In 2025, Supabase underwent a fundamental security model change – from symmetric JWT keys (HS256) to an asymmetric model. Tokens are signed with a private key (isolated from the rest of the infrastructure) and verified with a public key. If a verification node (e.g., the API Gateway) is compromised, the attacker cannot create new tokens – they don't have access to the signing key.

Static anon and service_role keys have been replaced by rotatable keys divided into publishable (low privileges, safe to include in frontend code) and secret (full privileges, server-side only). Integration with GitHub Secret Scanning automatically detects keys committed to public repositories and revokes them within hours.

Splinter is a PostgreSQL security linter built into Supabase. It scans the database schema for common mistakes: tables in the public schema without RLS enabled (open API), unauthorized access to the auth schema (identity data leakage), SECURITY DEFINER functions with a mutable search_path (privilege escalation vector), and inefficient authorization function implementations (performance degradation on large tables).

At the network level, the platform offers AWS VPC PrivateLink for Enterprise customers – database traffic flows through the private AWS backbone, bypassing the public internet. For public-facing endpoints, fail2ban mechanisms (blocking after repeated failed logins) and CIDR restrictions for direct database connections are in place.

Self-hosting: Docker Compose and Kubernetes

Docker Compose – single server

Supabase provides an official Docker Compose configuration that runs the entire stack (Kong, GoTrue, PostgREST, Realtime, Storage, PostgreSQL) on a single machine. Minimum requirements are 2 CPU cores, 4 GB RAM, and 50 GB SSD – for production, 4+ cores and 8+ GB RAM are recommended.

Kong maps internal container ports to a single entry point (default port 8000), simulating the behavior of Supabase Cloud. Self-hosting does not send telemetry to Supabase servers – logs are routed to a local Vector → Logflare → PostgreSQL (or BigQuery) stack.

The critical difference from the cloud: the entire administration burden – PostgreSQL backups, security updates, database tuning, SSL configuration, monitoring – falls on the DevOps team. There is no automatic PITR (Point-in-Time Recovery) or read replicas.

Kubernetes – horizontal scaling

For deployments requiring horizontal scalability, the community maintains a Helm chart repository. Stateless components (PostgREST, Realtime, Kong) scale horizontally via Horizontal Pod Autoscaler. The database layer requires Persistent Volume Claims for data durability and a dedicated Docker image with pre-installed extensions (pgjwt, wal2json).

PostgreSQL replication and failover are not part of the official Helm charts – they must be implemented using external operators (Zalando Postgres Operator, StackGres) or a Patroni deployment.

When to use Supabase vs. a traditional backend

Supabase excels in scenarios where application logic boils down to CRUD operations on relational data with per-user authorization: admin panels, multi-tenant SaaS applications (RLS with tenant_id), MVPs and prototypes requiring a quick backend, mobile apps with offline-first synchronization (Realtime + PostgREST).

For B2B SaaS applications, Supabase supports two multi-tenancy models. The first – RLS-based isolation (pooled tenancy) – shares a single database across all tenants, separating data via a tenant_id column and an RLS policy: tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id'). It's efficient and scales to hundreds of tenants. The second – schema-per-tenant – creates a separate PostgreSQL schema for each customer, providing physical data isolation (important for compliance frameworks like HIPAA). However, it complicates migrations and requires dynamic schema switching in API queries.

A practical issue in the pooled model: GoTrue requires global email uniqueness. If a consultant wants accounts in two organizations, the second registration will fail. The production workaround is internal email mapping – the backend hashes the combination of tenant UUID and email address (e.g., SHA256), registering this hash as the address in GoTrue. Each tenant gets a cryptographically unique account, while the real email is resolved from a separate mapping table.

Limitations appear when business logic is complex and multi-step – orchestrating several external APIs (payments, shipping, CRM), complex workflows with task queues, background file processing. Edge Functions solve some of these problems but cannot replace a full application server with its own lifecycle, middleware, and error handling.

A hybrid approach is the recommended strategy: Supabase as the data layer (PostgreSQL + auth + storage + realtime) with a custom backend (Node.js, Go, Python) handling complex business logic. The backend connects to the PostgreSQL database directly (via Supavisor in Session or Native mode), bypassing PostgREST, and manages transactions itself.

Conclusion

Supabase is not "Firebase on PostgreSQL" – it is a complete microservice architecture where the database is the central engine for authorization, routing, and event generation. Understanding its internal mechanics – from GUC variables in PostgREST, through WALRUS in Realtime, to prepared statement broadcasting in Supavisor – enables informed architectural decisions and helps avoid pitfalls that only appear under production load.

The greatest strength of Supabase is the fact that standard PostgreSQL runs underneath. All knowledge about database tuning, indexes, autovacuum, replication, and connection pooling applies directly. Supabase doesn't replace this knowledge. The packaging is new, but the engine remains the same battle-tested relational powerhouse.

Frequently asked questions

FAQ – Supabase