Does SurrealDB permission evaluation impact result set counts in aggregate queries?

Permission Filtering in Aggregations

Yes, SurrealDB permission evaluation directly impacts result set counts in aggregate queries. Aggregate functions—including count(), sum(), and avg()—operate only on the records that the authenticated user is permitted to access.

Execution Logic

In SurrealDB, permissions defined via DEFINE TABLE or DEFINE FIELD are not applied as a post-processing step after data retrieval. Instead, they are integrated into the query planning phase as implicit filters. Effectively, the engine treats permission policies as hidden WHERE clauses that are appended to the query before the aggregation occurs.

This leads to the following behaviors:

• Filtered Counts: A SELECT count() operation returns the number of records the user is authorized to see, not the total number of records existing in the table.
• Permission Gaps: If a user has the general permission to execute a query but lacks the specific record-level SELECT permissions for any individual records within that table, the aggregate result will be zero or an empty set.
• Bypass Mechanism: Only users with the SUPERUSER role (or those operating under a NONE permission schema) bypass these filters to see the absolute total of all records.

Verification Steps

To verify this behavior in your environment (assuming SurrealDB v1.x or v2.x), you can use the following scoped test:

1. Create a table and insert five records, where only three meet a specific condition (e.g., status = 'active').
2. Define a table permission that restricts access to only 'active' records:

   DEFINE TABLE test PERMISSIONS FOR SELECT WHERE status = 'active';

3. Authenticate as a standard user and run:

   SELECT count() FROM test;

   (Expected result: 3).
4. Authenticate as a root/superuser and run the same query (Expected result: 5).

Diagnostic Note: To provide a more specific optimization recommendation, please clarify if you are using DEFINE FIELD permissions in addition to DEFINE TABLE permissions, as field-level restrictions can further impact specific aggregate functions like sum().