Realtime Postgres RLS now available on Supabase

Summary

Supabase updated its Realtime server to enforce PostgreSQL Row Level Security (RLS) policies when broadcasting database changes over websockets. Previously, Realtime operated as an opt-in beta feature that sent all replication changes to every client regardless of user authorization. To enforce RLS per subscriber without heavy performance overhead, Supabase introduced WALRUS, a security engine colocated inside PostgreSQL. For each replication change, WALRUS looks up active subscribers, assumes their identities, and evaluates row visibility using prepared statements queried by primary key. This in-database evaluation avoids external network round trips and single-query planning overhead while returning an authorized subscriber list to Realtime.

Context

Supabase Realtime decodes PostgreSQL logical replication changes and broadcasts them to clients over websockets. Previously, Realtime did not enforce Row Level Security, broadcasting all table modifications to all connected clients. Because each subscriber can have distinct visibility rules, evaluating row access individually for every change and subscriber introduced severe performance bottlenecks.

Approach / What changed

Supabase implemented Write Ahead Log Realtime Unified Security (WALRUS), an in-database PostgreSQL function invoked by the Realtime server. WALRUS inspects replication changes, queries an internal subscription table to identify active subscribers, and assumes each subscriber's identity to check row visibility. The visibility checks execute prepared statements queried by primary key, reducing PostgreSQL query planning overhead and eliminating network round-trip latency by colocating the security checks directly within the database.

Takeaways

• Prepared statements bypass PostgreSQL query planner overhead, which frequently accounts for two to three times the execution time of simple primary-key lookups.
• Colocating the WALRUS security engine inside PostgreSQL eliminates network round-trip latency and I/O bottlenecks by processing subscriber authorization in a single connection.
• Realtime RLS processing time scales with the number of subscribers, ranging from 11.2 ms for 1 subscriber to 303.8 ms for 10,000 subscribers per record.

Related reading

• Supabase · Aug 2, 2020

  Continuous PostgreSQL Backups using WAL-G

  Continuous PostgreSQL backups require coordinating physical base snapshots with ongoing write-ahead log archiving to support point-in-time recovery. The open-source WAL-G utility simplifies this workflow by managing backup pushes and fetches to cloud storage services such as Amazon S3. In a typical setup on Ubuntu with PostgreSQL 12, envdir supplies AWS credentials and storage prefixes to WAL-G commands embedded directly in the PostgreSQL archive configuration. Regular base backups can be automated via cron jobs, which minimizes the volume of WAL archives that must be replayed during a restore. When recovering to a new instance, administrators fetch the latest base backup, define a restore command with targeted recovery timestamps in the configuration, and initiate replay using a recovery signal file.

  Angelico de los Reyes

• Supabase · Nov 18, 2020

  Postgres Views

  Postgres views serve as query shortcuts that execute underlying SQL statements upon retrieval without generating new tables or persisting duplicate data. By encapsulating complex multi-table joins, standard views provide query consistency across applications, simplify repetitive calls, improve logical schema organization, and enhance security by restricting sensitive columns. In contrast, materialized views physically store query results on disk, dramatically reducing read latency for heavy queries spanning millions of rows. Because materialized views introduce the trade-off of stale data, administrators must periodically run the refresh command based on workload tolerances for use cases like analytics and internal dashboards. Materialized views should not substitute query optimization, as underlying query efficiency remains essential.