Protecting reserved roles with PostgreSQL Hooks

PostgreSQL manages permissions through roles, and to create these roles a database user needs the CREATEROLE privilege. However, granting this privilege can lead to potential security risks as it allows role modification or dropping of important roles. To address this issue, Supabase uses dedicated roles for each customer's backend services and employs PostgreSQL Hooks in their SupaUtils extension to protect these reserved roles. The SupaUtils extension sets up a hook that intercepts SQL statements like ALTER ROLE and DROP ROLE, checks if the role being altered or dropped is a reserved one, and aborts the operation if it is. This way, customers can manage roles as they do in on-premises databases while ensuring the protection of important roles.