Data Protection

QueryDesk implements a robust data protection system designed to ensure sensitive data never leaves your database while maintaining query functionality. This system is built with security-first principles and provides granular control over data access.

Security Architecture Overview

The data protection system operates at the query processing layer, where all queries are validated and potentially modified before execution. This ensures that employees cannot access sensitive data they shouldn't have permission to view, preventing PII leaks and maintaining data privacy.

Because the rewriting happens before the query runs, the database is never asked for the protected values in the first place. That is a stronger guarantee than masking a result set: tools that fetch the rows and then hide fields have already pulled the sensitive values across the network and through the application, where they can end up in memory, logs, and caches. Here they never leave the database at all.

Core Security Principles

  1. Data Never Leaves the Database: Protected values are never returned by the database, rather than being fetched and hidden afterwards
  2. Query Rewriting: Queries are dynamically modified to enforce protection policies
  3. Allow-List Validation: Only pre-approved SQL syntax and operations are permitted
  4. Fail Closed: Anything the rewriter cannot fully model is rejected rather than passed through
  5. Default Protection: By default, all columns are hidden unless explicitly allowed if a policy is assigned

How It Works

Each query is analyzed and then rewritten to enforce the data protection policy.

-- Original query
SELECT id, name, email FROM users;

-- Rewritten query with data protection
SELECT id, '***' AS name, '***' AS email FROM users;

In cases where a user selects with *, the * is replaced with all columns explicitly referenced.

-- Original query
SELECT * FROM users;

-- Rewritten query with data protection
SELECT id, '***' AS name, '***' AS email FROM users;

Protected fields are not allowed to be referenced anywhere in the query, including WHERE clauses as that would allow to infer sensitive data.

-- Original query
SELECT id FROM users WHERE email = 'test@example.com';

-- Rewritten query with data protection
SELECT id FROM users WHERE '***' = 'test@example.com';

Views

Views and materialized views are listed alongside tables (on Postgres, those in the public schema) and are governed by their own column actions. An action on a base table column does not carry through to a view that reads it, so a view column with no action is hidden until a policy or rule shows it.

-- users.email is shown, but the view's email column has no action
CREATE VIEW active_users AS SELECT id, email FROM users WHERE active;

-- Rewritten query with data protection
SELECT '***' AS id, '***' AS email FROM active_users;

Statements That Are Rejected

Protection can only be enforced on statements the rewriter fully understands: SELECT, INSERT, UPDATE, DELETE, and the unions and subqueries built from them. Anything else is rejected with an error rather than forwarded, because a statement that can't be rewritten can't be masked.

In practice this means the following are refused while a policy is in effect:

PREPARE p AS SELECT ssn FROM users;  -- and the matching EXECUTE
DECLARE c CURSOR FOR SELECT ssn FROM users;  -- and FETCH / MOVE
COPY users TO STDOUT;
DO $$ ... $$;
CALL some_procedure();

Functions that run SQL held in a string, or read a table named in one or on another server, are refused as well, because what they return never passes through the rewriter. The functions refused are the known ones on each engine, along with any function whose name starts with one of them:

  • Postgres: query_to_xml, table_to_xml, schema_to_xml, database_to_xml, cursor_to_xml, dblink, ts_stat, ts_rewrite, crosstab, connectby, xpath_table
  • Oracle: DBMS_XMLGEN.getxml, getxmltype and newcontext
  • SQL Server: OPENQUERY, OPENROWSET, OPENDATASOURCE
  • ClickHouse: remote, remoteSecure, cluster, clusterAllReplicas, merge, view, mysql, postgresql, url, file, s3, jdbc, odbc

A function added by an extension that is not on this list is not refused, so review the extensions installed on a protected database.

SELECT query_to_xml('select ssn from users', true, false, '');
-- Function 'query_to_xml' runs SQL or reads a table by name, so it can't be masked

A redacted column also can't decide which rows come back or what they add up to. Masked inside a WHERE, HAVING or JOIN ... ON, any comparison (CASE WHEN, IN, BETWEEN, IS NULL), a GROUP BY, ORDER BY or DISTINCT, an aggregate such as count or max, or a UNION that removes duplicates, it would make the result silently wrong: '***' = '***' matches every pair of rows, '***' = 'x' matches none, and count(DISTINCT '***') is always 1. So the statement is refused with an error naming the column:

SELECT id FROM users WHERE ssn = '123-45-6789';
-- Column 'users.ssn' is redacted, so it can't be used to filter, join or compare rows

A column keeps its redaction when a CTE or a subquery in FROM returns it under another name, so filtering on that output is refused too. The error names the table column behind the output, since that is the one a policy lists, and the output it reached the filter through:

WITH recent AS (SELECT id, ssn AS tax_id FROM users)
SELECT id FROM recent WHERE tax_id = '123-45-6789';
-- Column 'users.ssn' is redacted, and 'recent.tax_id' is computed from it, so 'recent.tax_id' can't be used to filter, join or compare rows

The column named in the error is the one to show in the policy. Otherwise, filter on a column that is shown.

In the QueryDesk web UI the Data protection panel opens on a query refused this way, as it does on one that ran. It shows the query as it would have been rewritten and every column it referenced, and the redacted column can be requested there. The query itself is not run.

Session and transaction statements carry no row data, so they still pass through unchanged and drivers keep working: SET, SHOW, RESET, BEGIN, COMMIT, and ROLLBACK.

This applies to every path that runs a protected query — the QueryDesk web UI, the Postgres proxy, and the MCP run_query tool.

System Tables

System catalogs (pg_stats, pg_class, information_schema.columns, MySQL's mysql.user, and so on) can expose real column values — pg_stats, for example, publishes sampled values from every table. They are therefore blocked by default: with a policy assigned, a query against a system table fails closed.

When a tool genuinely needs catalog access, list the tables it needs under Allowed system tables in the policy editor. Each entry is either a table name or a schema/database name that allows everything inside it:

pg_class
information_schema

Allowlisted tables bypass masking entirely, so only add catalogs you have confirmed do not surface protected data. The allowlist is per policy, so one role can be granted catalog access without widening it for everyone.

Inherited Policies

A policy can be put in effect on other databases with Add inherited database, which is how a replica is kept redacted exactly like its primary. The inherited policy has no actions of its own. Each time it is used, a column takes the action the source policy gives the column with the same table and column name (on Postgres, the same schema too), and the source policy's allowed system tables apply.

A change to the source policy, by hand or through an auto apply rule on the source database, is in effect on every inheriting database on the next query. Nothing has to be synced or edited on the inheriting side.

On an inheriting database a column is hidden when:

  • the source database has no column with that table and column name, or
  • the source policy has not given the column an action.

A column that is added to an inheriting database later takes the source policy's action once the database's schema is synced.

Auto apply rules created on the inheriting database do not change its inherited policies. Create the rule on the source database instead.

Access requests from people governed by an inherited policy are reviewed on the source policy's page, where each request shows the database it was made on. Approving one shows the column on the source database and on every inheriting database that has it. A column the source database does not have cannot be requested.

Policy Assignment and Evaluation

Data protection policies can be assigned to individual users or roles. The system evaluates policies in the following order:

  1. User-Specific Policy: First looks for a policy explicitly assigned to the user
  2. Role-Based Policy: If no user policy exists, applies the policy assigned to the any of the user's roles
  3. Default Protection: If no policy is assigned, no data protection is applied

Only a single policy is evaluated per query, ensuring consistent and predictable data protection behavior.

Was this page helpful?