Access policies
Learn how access policies define table, column, and row-level data access for groups on a Retool resource.
An access policy defines which data a group of users can read or write on a resource, down to the table, column, and row level. You configure policies on the Access Enforcement tab of the resource, and the policy is enforced on every query that runs against that resource.
Use access policies to control what a user can see or edit on a resource. A policy grants access; whatever it does not grant stays inaccessible. Access policies move those decisions out of individual apps or queries and into the resource. You define them once, and they govern every query Retool runs against the resource, including queries from apps, the query library, raw SQL, and agents.
Access policies and data access enforcement
Controlling data access on a resource takes two things, and they do different jobs.
| What it is | What it controls | |
|---|---|---|
| Data access enforcement | A single setting on the resource. | Whether Retool checks queries against policies at all. With it on, the resource is closed, and no query runs unless a policy allows it. |
| Access policies | The rules and group assignments you write. | What each group reaches once a query runs, down to the table, column, and row. |
Enforcement is the switch on the resource. Access policies are the rules that decide who reaches what.
You can only create policies on a resource once data access enforcement is on. Turning it on with no policy closes the resource to everyone, including admins. Turning it back off leaves any policies on the resource in place but stops applying them, so the data they restricted becomes accessible again.
Refer to the Data access enforcement conceptual guide for a more comprehensive review of how it works.
Access policies and access levels
The two do not depend on each other, but you can use both to control data access for users.
- Access levels (Use, Edit, and Own) decide whether someone can run a query against the resource at all. Refer to Permission levels for what each level allows.
- Access policies decide what data that query is allowed to touch.
A user with Use access reaches only the tables a policy grants their group, which can be none of them. A policy can only narrow what data someone reaches, so it cannot grant a user the ability to query a resource if their access level doesn't already allow it. Refer to Manage permissions for resources for more information.
What is in an access policy
An access policy is made up of:
- The Policy assignment: the groups it applies to or excludes.
- The environments: the resource environments it covers, such as Production or Staging.
- The Policy rules: the access it grants to the resource, either unrestricted (full access to the resource) or granular. A granular policy can grant specific tables, limit the columns within them, and filter the rows those columns expose.
Refer to Access policy reference for every field and value, and Configure access policies to create one.
Access policy authorship
Any PostgreSQL resource owner can create an access policy for their resources. Access policies are not limited to administrators. An owner can change a resource's connection configuration, or turn enforcement off entirely for a resource. A Controls on badge next to a resource's name marks access policy enforcement as turned on. Policy changes, blocked queries, and queries narrowed by a row filter are written to audit logs, which record the policies that applied.
Limitations
Access policies are a part of data access control, but there are some limitations to be aware of.
- Access policies apply only to PostgreSQL resources.
- Workflow queries against an enforced resource fail. Evaluating a policy requires a user to evaluate it against, and a workflow run triggered by a schedule, a webhook, a query, an email, or another agent has none. Runs started manually or from an app are also blocked unless your organization attributes them to the user who started them. Refer to Use a separate resource for workflow queries for a way to keep workflows running.
- Row filters control which existing rows a user can read, update, or delete. Retool checks them against the values already in the table, never against the values a user writes, so a user can create a row that falls outside a filter, or change a row they can already reach so that it no longer matches. Refer to Row filters do not validate writes for an example.
- Database constraints, such as a
UNIQUEconstraint, are checked against every row in the table, including rows a policy hides. A user who inserts a value that collides with a hidden row gets a constraint error, which tells them the row exists even though they cannot read it. - Anyone who connects to the database directly with a resource's credentials is not subject to access policies.