Skip to main content

Use a separate resource for workflow queries

Configure resources so workflows keep running when data access enforcement is turned on.

Workflow queries against a resource with data access enforcement turned on fail, because evaluating an access policy requires a user to evaluate it against, and workflows are run as the first admin user. Refer to Why did my workflow stop working? for troubleshooting information.

To govern apps and users with access policies while workflows keep running, you can create two resources for the same database. One resource has enforcement turned on and serves everything users run. A second resource has enforcement turned off and is used only by workflows.

Caution

The workflow resource is not governed by access policies. Anyone who can query it, or edit a workflow that queries it, can access whatever its database credentials allow. A best practice is to narrow access to the resource on both sides. Limit what its credentials can access in the database, and limit who can use it in Retool.

Requirements​

  • An Enterprise plan.
  • A PostgreSQL resource. Access policies are supported only for PostgreSQL.
  • Own access to the resource you want to enforce, and permission to create resources.
  • Access to the database to create a role.

How the two resources differ​

Both resources connect to the same database. They differ only in whether enforcement is on and who uses them.

ResourceData access enforcementQueried by
Enforced resourceOn.Apps, the query library, raw SQL, and agents.
Workflow resourceOff.Workflows only.

Access policies on the enforced resource have no effect on the workflow resource.

1. Create a database role for the workflow resource​

Because access policies do not apply to the workflow resource, its database credentials are what limits the data it can access. Create a dedicated role granting only what your workflows need, instead of reusing the credentials of the enforced resource.

CREATE ROLE retool_workflows LOGIN PASSWORD 'a-strong-password';
GRANT CONNECT ON DATABASE orders TO retool_workflows;
GRANT USAGE ON SCHEMA public TO retool_workflows;
GRANT SELECT ON public.orders TO retool_workflows;
GRANT INSERT, UPDATE ON public.order_events TO retool_workflows;

A role with broad access undoes the policies on the enforced resource because the data will be accessible through the workflow resource.

2. Create the workflow resource​

Refer to Create a PostgreSQL resource for how to create a resource with data access enforcement turned on.

  1. Create an additional PostgreSQL resource.
  2. Give it a name that states what it is for, such as Orders database (workflows), so builders do not select it by mistake.
  3. Enter the same host, port, and database name as the enforced resource.
  4. Authenticate with the role you created in step 1.
  5. Configure every environment your workflows run in.
  6. Leave Data access enforcement turned off.

3. Restrict who can use the workflow resource​

Access levels are the only Retool control on this resource. Grant Use access to the group that maintains these workflows, and grant nothing to everyone else. Use access allows a group to create and edit queries against a resource from within workflows, so anyone who has it can query whatever the role from step 1 allows.

Refer to Manage permissions for resources for more information on setting environment-specific permissions on the resource.

4. Move workflow queries to the workflow resource​

note

Move the workflow queries before you turn on data access enforcement for the other PostgreSQL resource, so workflow runs don't fail.

For each workflow that queries the database:

  1. Open the workflow.
  2. Select each block that reads from or writes to the database.
  3. Change its resource to the workflow resource.
  4. Run the workflow and confirm every block succeeds.

5. Turn on enforcement for the enforced resource​

Once workflow queries run against the workflow resource, turn on data access enforcement for the original resource and create access policies for your apps and users. Refer to Configure access policies for more information.