Complex authorization methods implementation - Create a db_post_request Hook for Dynamic Data Masking in Supabase

[jonra1993]

Problem

Currently, there is no mechanism in Supabase to dynamically mask or manipulate output data before it is sent to the client. While Row-Level Security (RLS) and Column-Level Security (CLS) provide robust access control, certain complex authorization scenarios remain challenging to implement securely without resorting to external middleware or duplicating logic.

For example, when fetching a list of users, the desired behavior might be:

• For users with the role customer:
  • Display the email field only if the user_id matches the authenticated user.
  • For other users, display NULL in the email field.
• For users with the role admin:
  • Display all email fields without restriction.

Achieving this securely is challenging due to:

1. Views: While it's possible to create a masking view, an attacker with access token could potentially bypass this by querying the base table directly.
2. Custom Edge Functions: Implementing complex rules via Edge Functions requires duplicating logic already defined in the database, leading to maintenance challenges and increased complexity.

A feature that allows for dynamic data masking or manipulation at the SQL level—just before the response is returned to the client—would enable more powerful authorization models, such as Attribute-Based Access Control (ABAC), without relying on external systems.

Solution

Introduce a db_post_request hook function that executes after a query is processed but before the response is sent to the client. This function could be utilized to:

• Dynamically mask sensitive data based on roles, attributes, or session variables.
• Apply complex transformations using custom SQL or a scripting engine like V8.

Benefits

1. Dynamic Data Masking: Allows for real-time column masking or row adjustments without the need for views or duplicated logic.
2. Centralized Authorization: Enables the consolidation of complex ABAC logic within the database, reducing redundancy across application layers.
3. Enhanced Security: Prevents direct access to unmasked data in base tables by handling output manipulation in a secure, centralized manner.

This function would allow for dynamic masking and transformation of output data directly within the database, seamlessly integrating with Supabase's existing RLS and CLS mechanisms.

I have opened a related feature request in the PostgREST repository: [Add db_post_request Hook for Dynamic Data Masking in PostgREST](PostgREST/postgrest#3830).

If there are other approaches to implementing complex authorization methods like ABAC in Supabase without relying on dedicated middleware or Edge Functions, I would appreciate any guidance or suggestions.

[GaryAustin1]

You probably can also use rpc postgres security definer function calls to a table without RLS enabled. Then you provide back the exact data you want for each scenario while also looking at the user role in the function. A single return function seems like it might be a mess if it has to deal with individual tables and roles to sort what data to return. Using a table returning function can still allow filters on the client side while limiting data based a formula you desire.

The global db_post_request also might have merit but just seems dealing with individual tables and their return values would be better handled on each table (or view) with rpc being the gateway. IMO.

[jonra1993]

Hello @GaryAustin1 Thanks for your reply! I think the approach using RPC functions with SECURITY DEFINER is solid and can be faster than using edge functions. However, I noticed that disabling RLS in this setup could unintentionally affect current tables and views relying on it for additional security especially in C_UD operations but also it can be a long term more complex manage db functions for each table also API then no longer has a unified structure. https://stackoverflow.com/questions/72756376/supabase-solutions-for-column-level-security

A db_post_request function could simplify things by providing consistent masking rules across all tables and columns that runs just once. Something akin to the PostgreSQL Anonymizer extension, but with more granular control, leveraging tools like V8 for dynamic logic, would be incredibly powerful. This way, masking could be centralized while still flexible enough for complex scenarios. I was thinking on run a function that mask cells, columns or remove rows (based on roles) without changing its original schema to not affect clients schemas. I think it could work similar as currently custom auth hooks works but as a way to mask output data.

A similar approach is currently used in existing authorization implementations, such as Permit.io's guide on implementing RBAC in Supabase, where roles are primarily used to determine whether a user is allowed to make a specific request. However, introducing a mechanism to mask or control data before it is returned to the client could significantly enhance the authorization module, enabling more flexible and granular control over what users can see or access.

Thanks again for the insights—it’s given me a lot to think about and check another option using db functions!