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?

[wolfgangwalther]

A couple of thoughts:

• PostgREST is the wrong layer to solve this at, imho. Access control should really be handled inside the database, not here.
• We'd really need a proper combination of row level security and column level grants in upstream PostgreSQL. Column-specific Row Level Security policies. That would be great - but we don't have it.
• You could possibly use https://labs.dalibo.com/postgresql_anonymizer or https://github.com/rjuju/pg_anonymize to do the masking you want to do via SECURITY LABELs.

All of that being said, PostgREST does have a way to influence the output, too: You could implement a custom aggregate to override the built-in media type handler for json: https://docs.postgrest.org/en/v12/references/api/media_type_handlers.html#overriding-a-builtin-handler. This would allow you to do anything you want to the update.

However, I strongly advise against this. Look into the various anonymization extensions, which are much better for security.

[jonra1993]

Hello @wolfgangwalther thanks for your reply :)