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.
- Queries fail on a resource that has Disable converting queries to prepared statements turned on. That setting and access policies cannot be combined. Turn Disable converting queries to prepared statements off before you turn enforcement on. Refer to Disable prepared statements for more information.
- Workflow queries against an enforced resource are blocked when runs are triggered automatically by a schedule, a webhook, a query, an email, or another agent through an A2A call because they cannot be attributed to any user. Workflow runs querying an enforced resource that can be attributed to a user, such as manual runs from the workflow IDE, triggering a run from an app, or calling a run from a parent workflow are not blocked because Retool can evaluate the policy against a user. 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. - The schema browser filters to the tables and columns a user is granted, but schema is not confidential. A user with Edit access on the resource can list every table and column it contains, including the ones no policy grants them. Refer to Schema is not confidential.
- Retool isolates restricted and unrestricted connections to the resource. Using a connection pooling proxy like PgBouncer may break that protection. Refer to Connection pooling proxies can break enforcement for more information.
- Anyone who connects to the database directly with a resource's credentials is not subject to access policies.