# Protecting reserved roles with PostgreSQL Hooks

[Supabase](https://yomu.fyi/company/supabase) · Steve Chavez · Jul 2, 2021

**Type:** Problem & solution

## 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.

**Tags:** [Authentication](https://yomu.fyi/topic/authentication), [Open Source](https://yomu.fyi/topic/open-source), [Postgres](https://yomu.fyi/topic/postgres)

[Read original post](https://supabase.com/blog/roles-postgres-hooks)