Secure SQL Views: Generate statistics without breaking the RLS

In designing modern multi-tenant SaaS applications (such as a SOC orchestrator managing multiple clients), database architecture is the crux of the matter. To ensure that no customer can access another customer's incidents, the industry has adopted an absolute standard:Row-Level Security (RLS)from PostgreSQL.

The RLS locks each row in the database based on the identity of the analyst. But what happens when you need to generate aggregate statistics (like calculating MTTR or total tickets) via SQL Views (VIEWS) or aggregation functions?

If the SQL view is poorly designed, it can bypass RLS rules and leak statistical data between clients. Learn how Akuity SOC architecture solves this puzzle with the modifierSECURITY INVOKER.

The problem: Data aggregation often bypasses RLS

Imagine that you had to build the KPIs dashboard of your SOC. You need to query the tableTickets(which contains thousands of Microsoft Defender logs) to make aCOUNT()or calculate an average (AVG()) resolution time.

The classic solution for a DBA (Database Administrator) is to create an SQL view (CREATE VIEW viewworkspacekpis AS SELECT ...).

By default, in many historical databases or frameworks, views or functions are executed with the rights of the person whocreatedthe view (the owner of the database). This is called modeSECURITY DEFINER.

The danger is critical: If a view is running as super-administrator of the database,it completely circumvents the RLS rules. Customer A's SOC analyst queries the view for its MTTR, but the view, running without an RLS filter, will aggregate and return the overall system MTTR, including Customer B's data. Even if the web interface tries to hide the error, the metadata leak is proven and you lose your SOC 2 compliance.

The PostgreSQL solution: TheSECURITY INVOKER

For a SaaS KPIs dashboard to be both efficient and perfectly compartmentalized, the SQL engine must be forced to evaluate the view or function with the exact permissions of the end user (the connected analyst), and not those of the database creator.

This is where the modifier comes in.SECURITY INVOKER.

In the architecture of the Akuity SOC platform (powered by Supabase/PostgreSQL), our statistical dashboards (such as the Area Chart for the last 30 days or the calculation of the Resolution Rate) never rely on heavy application queries. They query pre-compiled SQL views that strictly follow this rule.

Implementation in Akuity SOC

In our database migration script, the view responsible for statistics is declared as follows:

CREATE VIEW view_workspace_kpis 
WITH (security_invoker = on) AS 
SELECT 
    t.workspace_id,
    COUNT(tk.id) as total_incidents,
    AVG(tk.resolution_time) as mttr_minutes
FROM tenants t 
LEFT JOIN tickets tk ON tk.tenant_id = t.id 
GROUP BY t.workspace_id;

Similarly, queries to display the workload time graph use a stored functiongetdailyincident_countwhich is defined withSECURITY INVOKER.

Operational impact and SOC 2 compliance

Using this line of codeWITH (security_invoker = on), the magic of the native cloud happens:

1. The analyst (authenticated via a JWT token containing hisauth.uid()) requests the display of its Dashboard on the Akuity interface.
2. API request calls viewviewworkspacekpis.
3. PostgreSQL engine evaluates viewas this analyst.
4. The native RLS rules of the underlying tables (tenantsAndtickets) are applied suddenly before aggregation. Customer lines to which the analyst does not have access are invisible. AggregationCOUNT()OrAVG()is only done on permitted data.

Why auditors demand this architecture

During a type conformity auditSOC 2 Type IIOrNIS 2, the auditor seeks to prove that the system is not based solely on software promises (the React/Next.js frontend code) but on systemic security controls ("Security by Design"). The joint use of RLS and viewsSECURITY INVOKERoffers cryptographic and mathematical proof of absolute data isolation from your tenants.

Conclusion: SaaS analytics without compromise

Building dashboards for a multi-tenant security operations center should never be a backdoor to metadata. By requiring an impeccable database architecture based on PostgreSQL, you guarantee to your MSSP clients or your Comex that the statistics displayed are not only accurate, but watertight by design.

Manage your KPIs with enterprise-grade security.> Discover the sovereign architecture of ourSOC RLS and KPIs Dashboardsdeveloped in Bavaria.