Data access enforcement
Learn what turning on data access enforcement changes, and how Retool evaluates access policies against each query.
Data access enforcement is a per-resource setting that determines whether Retool applies access policies to the queries that run against a resource. Data access enforcement is off by default.
- Enforcement is the switch on the resource, and it controls whether Retool checks queries against policies at all.
- Access policies are the rules you write, and they control what each group can access once a query runs.
This page covers what turning enforcement on changes and the rules Retool applies to each query. For information about access policies and how it relates to data access enforcement, refer to Access policies.
Enable data access enforcement
Turning enforcement on does two things:
- Policies become available. You can only create access policies on a resource once enforcement is on.
- The resource is closed by default in every environment. Users can access only what a policy explicitly grants them. Policies grant access, there are no deny rules, and anything not granted is blocked.
With enforcement off, no access policy applies to the resource. Every query against it runs as it would on any other resource.
This setting is available per resource from the Access Enforcement tab. Admins can also turn access policies off for the whole organization, which stops enforcement on every resource at once. Refer to Access policies.
Enforcement covers every surface that queries the resource. Access policies are enforced at the point where Retool connects to the resource, so the roles and grants in PostgreSQL are untouched, and anyone connecting to the database directly with their own credentials is not subject to a Retool access policy. Refer to Configure access policies for how to turn enforcement on without interrupting existing users.
Queries Retool cannot evaluate
Retool blocks any query it cannot fully evaluate rather than running it unrestricted. A query is blocked when:
- Retool cannot parse it.
- It uses constructs Retool cannot reason about.
- It cannot be attributed to a signed-in user. This includes queries from public apps, embedded apps, and workflow runs. Refer to Why did my workflow stop working? for which workflow runs are affected.
No policy can grant access to a query in this last group, because enforcement has no user to evaluate policies against. Account for these queries before you turn enforcement on. Refer to Configure access policies.
How queries are evaluated
Retool checks each query before it runs against the policies that apply to the user in three stages:
- Table access: whether any policy grants each table the query references.
- Row filters: which rows within those tables the user is allowed to reach.
- Column access: whether every column the query references is granted.
Grants and their results
A policy rule grants access, and each kind of grant behaves differently when a query reaches past what it covers. To diagnose a specific failure, refer to Troubleshoot access policies.
| Grant | Applies to | Result when a query goes beyond it | Error message |
|---|---|---|---|
| Table grant | The tables a policy names. | Fails. | Error naming the table. |
| Column grant | Every position a column appears in, including SELECT, JOIN, WHERE, and GROUP BY. | Fails. | Error naming the column. |
| Row filter | The rows within a granted table. | Succeeds, narrowed to permitted rows. | None. |
A column can block a query by appearing in a filter condition, even if it's never returned in the results. For example:
SELECT name, email FROM customers WHERE ssn = '123-45-6789'
Here ssn isn't returned, only used to filter. But if the policy doesn't grant access to the ssn column, this query still fails, because filtering on a column can leak its value even without displaying it.
Blocked columns and blocked rows fail differently, and the difference is deliberate. A blocked column changes the shape of the result, and an app built against a column that quietly went missing breaks in ways that are hard to trace, so Retool returns an error naming the column. A blocked row only changes how many results come back, and apps already handle result sets of varying size, so row filters narrow results silently.
Whether SELECT * runs depends on the grant it meets.
| Grant | SELECT * |
|---|---|
| Full access | Allowed. Rules aren't evaluated, so no column list constrains the query. |
| All present + future columns | Allowed. Every column the table has, and any column added later is granted. |
| All present columns and individually named columns | Blocked. The grant covers a fixed list, so a column added later isn't granted. |
Where SELECT * is blocked, name the columns the query needs instead. Row filters apply to SELECT * the same way they apply to any other query.
Access levels
Visibility into access policies is scoped by access level. This table shows what each type of user, agent, or admin can see.
| Access level | Visibility |
|---|---|
| Users with Own access to the resource | Every policy on the resource, its rules, and its assignment. |
| Users with Use or Edit access to the resource | Neither the Access Enforcement tab nor any access policy. Errors name the table or column involved, but not the access policy. |
| Agents acting for a user | The actions that user is allowed, the tables and columns available and unavailable to them, and the columns row filters reference. Agents cannot see access policies, or the values their filters compare against. |
| Organization admins reviewing audit logs | Which access policies applied to a query, including access policy identifiers and names. |
Schema changes made outside Retool
A granular access rule names the tables and columns it grants, so changing the schema in the database can block queries that worked before. A rule does not pick up a renamed or newly added object on its own.
| Change in the database | Result |
|---|---|
| A table is added or renamed | Queries against it fail until a policy grants it. |
| A column is added or renamed | Queries referencing it fail until a rule grants it. |
Two kinds of grant survive a schema change. A policy granting Full access is not evaluated rule by rule, so it covers whatever the schema contains. A granular rule granting All present + future columns covers columns added to that table later.
Other grants do not. All present columns selects the columns the table has at the time you choose it, so columns added later are not covered, and a rule naming columns individually grants only those columns. A rule listing no columns grants nothing. Refer to Column grants.
Review the policies on a resource after any schema migration, and refer to Configure access policies to add the new tables and columns.
Multiple access policies
Access policies are additive. Adding multiple access policies on a resource combines to give users more access.
| Scope | Rule |
|---|---|
| Within one column | A user reaches a row if any policy granting that column permits it. Filters from more than one column-level policy are combined with OR. |
| Across the columns a query references | A row is returned only if every referenced column permits it. Per-column results are combined with AND. |
| A column granted with no row filter | That column is unfiltered for every user the policy covers, regardless of narrower filters on the same column in other policies. |
| A policy granting Full access | Rules are not evaluated. The user can access all data on the resource. |
A blanket whole-table or Full access policy left active alongside new, narrower policies means the narrower policies have no effect. Use the broad policy's Except list to remove a group from it, which is what makes a narrower policy the only one applying to them. Refer to Grant broad access and restrict with exceptions.
How overlapping grants combine
Retool resolves overlapping policies one column at a time, then combines the results for the columns a query references.
For example, a teams table with two active policies covering the same user. Both grant headcount, so that column overlaps, and each policy carries a different row filter.
Two policies grant headcount, and their filters combine with OR. The headcount column can access both EMEA and AMER rows. Both region and budget are each granted by one policy.
Combining those per-column results with AND is what makes the second query empty. The region column permits only EMEA rows, and the budget column permits only AMER rows, and no row is both.
The rows returned depend on the columns selected
Because grants are combined across the columns a query actually references, changing the list of selected columns changes which policies are involved, and therefore which rows come back.
Using the same teams table and policies:
| Policy | Grants columns | Where |
|---|---|---|
| Regional leads | region, headcount | region is EMEA |
| Finance | headcount, budget | region is AMER |
The results differ by query:
| Query | Result | Why |
|---|---|---|
SELECT region, headcount | EMEA rows. | region is granted only for EMEA, which narrows the result. |
SELECT headcount, budget | AMER rows. | budget is granted only for AMER. |
SELECT headcount | EMEA and AMER rows. | Both policies grant this column, and their filters combine with OR. |
SELECT region, budget | No rows. | One column permits only EMEA, the other only AMER, and no row satisfies both. |
SELECT region, headcount, id | Error. | No policy grants id. |
Two columns a user can query individually can therefore return zero rows together.
Row filters do not validate writes
A row filter controls which existing rows a user can access. It does not validate the rows a user creates, or the values a user writes.
A user can:
- Only read rows the filter permits.
- Only update or delete rows the filter permits.
- Create a row that falls outside the filter.
- Update a row that is visible to them so that it no longer matches the filter.
For example, a files table and a policy granting read and write access where owner is Equal to {{ current_user.firstName }}:
| Filename | Owner |
|---|---|
README.md | Jane |
homework.txt | Jane |
passwords.zip | John |
recipes.html | John |
Jane can read and delete only her two files, and she cannot update the contents of John's files. She can, however, create a new file owned by John, and she can change README.md to be owned by John, because she owns it at the time she makes the change.
To prevent users from writing values outside a filter, enforce it in your database with a constraint or trigger.