Add db_post_request Hook for Dynamic Data Masking in PostgREST - Complex authorization methods implementation

[jonra1993]

Problem

Currently, there is no way in PostgREST 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 mechanisms for access control, there are scenarios where these mechanisms are insufficient to handle complex authz rules.

For example:

• When fetching a list of users, I want to:
  • Show the email field only if the user_id matches the authenticated user (for role customer).
  • Show all email fields if the authenticated user's role is admin.
  • Otherwise, mask the email field as NULL.

This is difficult to achieve securely without duplication or using external middleware:

1. Views: A masking view can be created, but attackers can bypass it by querying the base table directly if they have token access.
2. Custom Edge Functions (Supabase): These can enforce more complex rules but require duplicating logic already defined in the database and extend functionality but requires add extra code in a custom backend.

A feature to mask or manipulate output dynamically at the SQL level—just before returning the response—would enable more powerful authorization models like Attribute-Based Access Control (ABAC) without relying on external systems.

---

Solution

Introduce a db_post_request hook function that runs after a query is executed but before the response is sent to the client. This function could be used to:

• Mask sensitive data dynamically based on roles, attributes, or session variables.
• Mask data using custom SQL or even V8 for complex transformations and Authz functions.

Benefits:

1. Dynamic Data Masking: Enable column masking or row adjustments without relying on views or duplicating logic.
2. Centralized Authorization: Consolidate complex ABAC logic within the database, reducing duplication across application layers.
3. Enhanced Security: Prevent direct access to unmasked data in base tables by handling output manipulation in a secure, centralized way.

Do you have another solution without using a custom backend between client and PostREST?