User Email Audit and Per-user Caching

👤 This documentation is intended for Team Admins. Check with your Team Admin for additional access.

Overview

For each dashboard and chart load, and all SQL Lab queries, a user is executing a query to the backend connected datasource.

For some administrators, as an additional security precaution, it's important to track which user initiated any dashboard, chart, or SQL query within the database logs. Some teams may further utilize information on the user who initiated the query to in-flight filter the data the user has permission to access.

User Email Audit

Every query generated by Preset -- whether it's through SQL lab, a dashboard loading all charts, or a chart update -- has a comment prefixing the query with querying information. For example:

Preset offers a user_email_audit feature flag to add additional details in this comment prefix for the user email that initiated the query.

-- 6dcd92a04feb50f14bbcf07c661680ba
-- user: username@preset.io

This query's comment shows up in your database's query logs.

Use Case for User Email Audit

Most teams considering user_email_audit are tracking who has been running which queries across Preset.

Some teams may use the user email in the query comment to manage data access. For instance, customers using a security platform to manage all application's data access to databases can use the user's email in-flight of the query execution to determine what data a user should be filtered to view. This serves as an alternative to Row Level Security (RLS), which already provides data access management within Preset.

Caching and Data Access

As part of Preset's performance settings, Preset caches all metadata generated for each chart, so that dashboard viewers don't need to wait for on-the-fly queries to complete. To do so, Preset stores the data generated on dashboard load when a user first views that dashboard and keeps the data available for 24 hours for any other users viewing the same dashboard with the same permissions.

If Row Level Security (RLS) is already set up, then additional filters are applied in the SQL query when a chart or dashboard is loaded. In order for subsequent users to view the data cached by the dashboard load from the first user, the underlying queries generated by Preset have to be identical for both users, including the Row Level Security rules applied.

Per-user Caching

Preset's default cache setting assumes that some users can have access to the same information on dashboard as the user who first viewed the dashboard. Some teams with more sensitive data policy or have external platforms that manage data access can consider per-user caching to protect data access policies set up outside of Preset.

Preset offers a per_user_caching feature flag that caches the data that is generated on a user-by-user basis. Each user would have their own set of cached metadata, so Preset will only show the cached version of a dashboard or a chart if the user had previously visited the same dashboard or chart within the last 24 hours (default settings).

One consideration for Per-user Caching is the impact on the database performance. Caching saves query execution on the connected database when Preset recognizes an identical query is being requested by the user viewing a chart or dashboard. Once Per-user Caching is enabled, one user's dashboard or chart access will not save the query executions for another user's dashboard or chart access.