PostgREST 9

Summary

PostgREST 9 introduces several core updates to the tool that turns PostgreSQL databases into RESTful APIs. Developers can now perform inner joins when embedding tables using the !inner keyword, allowing top-level table rows to be filtered directly by embedded table attributes across multiple operators. The release also adds support for POST requests to functions containing a single unnamed parameter, which simplifies handling raw JSON payloads sent by external webhooks. To ensure compatibility with PostgreSQL 14, custom authentication functions accessing HTTP context headers and JWT claims must be updated to parse JSON properties from consolidated settings. Additional enhancements included in this version provide support for partitioned tables alongside documentation improvements and bug fixes.

Context

PostgREST converts PostgreSQL databases automatically into RESTful APIs, but prior versions lacked native support for inner joins on embedded tables and required named parameters for PostgreSQL functions invoked via RPC.

Approach / What changed

PostgREST 9 introduces the !inner keyword for embedding tables to filter top-level rows by embedded table filters, enables POST requests to PostgreSQL functions with a single unnamed parameter, and updates HTTP context settings for PostgreSQL 14 compatibility.

Takeaways

• The !inner keyword allows filtering top-level table queries based on conditions applied to embedded tables across all Supabase client libraries.
• PostgREST 9 supports POST requests to PostgreSQL functions with a single unnamed parameter, enabling direct ingestion of raw JSON webhook payloads.
• PostgreSQL 14 compatibility requires custom authentication functions to read request.jwt.claims and request.headers as JSON objects rather than individual claim or header settings.

Related reading

• Supabase · Jul 2, 2021

  Protecting reserved roles with PostgreSQL Hooks

  Supabase required granting database customers the CREATEROLE privilege to manage custom roles while preventing them from dropping or altering backend service roles such as supabase_storage_admin. Because stock PostgreSQL lacks a native mechanism to define custom reserved roles, the team created the SupaUtils extension using PostgreSQL hooks. Loaded via shared_preload_libraries, SupaUtils intercepts utility statements by overriding the global ProcessUtility_hook function pointer. The extension inspects incoming statements like ALTER ROLE and DROP ROLE against a configurable list defined in postgresql.conf using DefineCustomStringVariable. If a targeted role matches the configured reserved roles, the hook raises an error, preserving backend infrastructure roles while permitting broader role administration.

  Steve Chavez

• Supabase · Jul 7, 2020

  Physical vs Logical Backups in PostgreSQL