Protecting reserved roles with PostgreSQL Hooks

Summary

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.

Context

At Supabase, backend services rely on dedicated database roles like supabase_storage_admin, but giving customers the CREATEROLE privilege allows them to drop or modify any non-superuser role. PostgreSQL does not natively support defining custom reserved roles beyond its built-in pg_ roles.

Approach / What changed

Supabase built the SupaUtils extension, which hooks into ProcessUtility_hook via shared_preload_libraries and uses DefineCustomStringVariable to expose a supautils.reserved_roles parameter. The hook inspects ALTER ROLE and DROP ROLE statement nodes (T_AlterRoleStmt and T_DropRoleStmt) and throws an error if any affected role matches the reserved list before delegating unblocked statements to previous hooks or standard_ProcessUtility.

Takeaways

• The PostgreSQL CREATEROLE privilege allows modifying or dropping any role except superusers, creating operational risks when shared with customers on managed platforms.
• PostgreSQL hooks are global function pointers that extensions loaded in shared_preload_libraries can override during _PG_init while preserving previously assigned hooks.
• Utility statements such as ALTER ROLE and DROP ROLE can be intercepted by overriding ProcessUtility_hook and inspecting their internal node structures.

Related reading

• Supabase · Nov 27, 2021

  PostgREST 9

  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.

  Steve Chavez

• Supabase · Jul 7, 2020

  Physical vs Logical Backups in PostgreSQL

  PostgreSQL backups fall into two distinct categories: logical backups, which convert data into SQL command files, and physical backups, which copy the underlying file system storage. Logical backups generated with pg_dump or pg_dumpall allow single-database targeting and provide the only practical route for migrating across major Postgres versions with differing internal storage formats. Conversely, physical backups suit larger databases where long-running logical exports can degrade concurrent query performance and risk failure. Physical backups also integrate with Write Ahead Log files through tools like WAL-G to support Point in Time Recovery and minimize Recovery Point Objectives. Selecting between these two strategies depends on whether administrative simplicity, version portability, cluster size, or precise disaster recovery takes precedence.