Protecting reserved roles with PostgreSQL Hooks

PostgreSQL manages permissions through roles. To create these roles, a database user needs the CREATEROLE privilege, which not only allows role creation but also modification of any role (except superusers).

At Supabase, we use dedicated roles for each of our customers' backend services. For instance, our Storage API  uses the supabase_storage_admin role for connecting to the database. Giving the CREATEROLE privilege to our customers would allow them to drop this role and take their own Storage API down.

And yet, we want to give our customers the ability to manage roles the same as they do it in on-premises databases, with the usual CREATE ROLE, ALTER ROLE, and DROP ROLE statements.

So, how do we grant them the CREATEROLE privilege and, at the same time, protect our own roles? In this blog post, we explain how we managed to do this by using PostgreSQL Hooks in our SupaUtils extension.

Reserved Roles#

PostgreSQL has a list of predefined roles — all prefixed with "pg_" — that cannot be dropped or altered. Attempting to do so will throw an error mentioning that the role is "reserved".

This mechanism is an internal implementation detail. Unfortunately Postgres doesn't allow us to define our own reserved roles.

RDS reserved roles#

Amazon RDS has a similar defense mechanism, all of its predefined roles — prefixed with "rds" — cannot be modified.

Again, the error mentions that the role is "reserved".

Also note the rdsutils.c mention. That's not a stock Postgres source file. This means that the logic comes from an RDS extension. We can confirm this is the case by showing the preloaded libraries.

rdsutils can be seen there. Naturally this lead us into thinking we can achieve the same logic with an extension of our own, and thus the SupaUtils idea was born.

Extending PostgreSQL with Hooks#

PostgreSQL hooks allow us to extend internal functionality. Hooks can modify behavior at different places, including when running SQL statements.

For example, if we wanted to enforce our own password restrictions whenever a user changes passwords, we could use the check_password_hook to verify the password. We would write out our own Custom Logic, and raise an error if the password fails the password requirements.

For SupaUtils, we're particularly interested in the ProcessUtility_hook, which allows us to hook into utility statements: every statement except select, insert, delete or update. They include alter role and drop role, which are the statements we want to hook on.

Hooks are global function pointers#

To use hooks, we can override functions pointers that are global. On the Postgres codebase, the ProcessUtility_hook is basically used¹ like this:

As you can see, ProcessUtility_hook is NULL by default, so our extension should set it for the hook to run. Also, the standard_ProcessUtility function is the one that actually does the job of creating or modifying the roles (among other things) so our hook should also call it.

Loading and running the hook#

Each extension set in shared_preload_libraries will get its _PG_init function called. This function will allow us to set our hook onto ProcessUtility_hook.

Since hooks are global function pointers, it might be the case that another extension modifies the hook pointer (on its own _PG_init) before us and sets its own hook. So we need to ensure we also run this previously-set hook, before or after our own hook runs.

It's typically² done like this:

Setting up the SupaUtils extension#

We can use the concepts above to build our extension.

First we'll need a Makefile in order to compile the extension code and include it into our PostgreSQL installation.

For the source file, we'll start with variable definitions and functions declarations.

Up next we'll define each one of these function declarations.

Initializing the extension#

Let's now _PG_init our extension. Besides setting the hook here, we want to define our reserved roles as a configuration parameter, that way they can be modified by editing the postgresql.conf file. For this, we can use the DefineCustomStringVariable function, which inserts the parameter into Postgres "Grand Unified Configuration"(GUC) system.

Running the SupaUtils hook#

Now that our hook is set, we'll define what it will do. As specified in the ProcessUtility_hook_type, the hook's first parameter is a PlannedStmt, this represents the planned statement — the output from the Postgres planner. This is a step before the statement is executed.

We'll look for the presence of a reserved role in the planned statement. If there's one present, we'll report an error and abort the statement execution step.

Looking for the reserved role#

Lastly, we'll define how we look for the reserved role.

At this stage, we already have the utility statement and the reserved role list. All that's left to do is to define if the utility statement is an ALTER ROLE or DROP ROLE statement, and whether if the role it affects is a reserved one.

Testing the extension#

Now that the code is finished, we can test the extension. Since we already have a Makefile, the extension can be installed by doing make && make install. Then, in postgresql.conf:

We'll now try to alter or drop the reserved roles:

Wrapping up#

As you can see, PostgreSQL Hooks allow us to intercept SQL statements. There are many types of hooks, you can see unofficial documentation for these at AmatanHead/psql-hooks.

The full SupaUtils code is in our GitHub repository.

By the way, if you like working on PostgreSQL tooling and extensions: we are hiring PostgreSQL experts!

More Postgres resources#

• Implementing "seen by" functionality with Postgres
• Partial data dumps using Postgres Row Level Security
• Postgres Views
• Postgres Auditing in 150 lines of SQL
• Cracking PostgreSQL Interview Questions
• What are PostgreSQL Templates?
• Realtime Postgres RLS on Supabase

Footnotes#

1. You can see the ProcessUtility_hook declaration here and its usage here. ↩

2. This is also done on pg_stat_statements and sepgsql. ↩