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.

Takeaways

1. 1

   The PostgreSQL CREATEROLE privilege allows modifying or dropping any role except superusers, creating operational risks when shared with customers on managed platforms.

2. 2

   PostgreSQL hooks are global function pointers that extensions loaded in shared_preload_libraries can override during _PG_init while preserving previously assigned hooks.

3. 3

   Utility statements such as ALTER ROLE and DROP ROLE can be intercepted by overriding ProcessUtility_hook and inspecting their internal node structures.