Row-level security
Learn about row-level security of database tables.
Row-level security in Retool lets you manage access to specific rows within database tables. It ensures that users can only see and interact with data that they are authorized to access. In Retool, you can implement row-level security by referencing properties of the current user accessing the app in your Resource queries.
On PostgreSQL resources, you can enforce row-level restrictions on the resource itself with access policies rather than writing the filter into each query. Refer to Choose an approach.
Implement row-level security
Below is an example for how you can implement row-level security by referencing the Retool user's email in queries against your resources. This example assumes you maintain a mapping table from user emails to groups.
The orders table below contains order information for different clients (Retool users). The group_id designates the client assigned to each row.
| id | group_id | description |
|---|---|---|
| 1 | 1 | order 1 |
| 2 | 1 | order 2 |
| 3 | 2 | order 3 |
| 4 | 2 | order 4 |
| 5 | 3 | order 5 |
| 6 | 3 | order 6 |
The user_groups table maps Retool users to groups. Within a Retool app, when Alice selects data from the orders table, Alice should only see the first two rows.
| user_email | group_id | name |
|---|---|---|
| alice@example.com | 1 | Alice |
| bob@example.com | 2 | Bob |
| jane@example.com | 3 | Jane |
To achieve this, you can apply a filter using an inner join.
SELECT *
FROM orders as a
INNER JOIN (SELECT group_id from user_groups where user_email = {{ current_user.email }}) as b
ON a.group_id = b.group_id
The request passes the request from the client to Retool's server, and the query is evaluated with prepared statements on Retool's backend before being executed on the database. The result is returned along the same path.
To ensure that the client cannot intercept the initial request and insert a different user’s email, prevent query variable spoofing is on by default. This ensures that the parameterized statement that arrives at the server contains the same current_user.email as the client that's logged in.
Use user attributes
Rather than maintaining a mapping table, you can use user attributes to store foreign keys directly on the current user. This prevents the need for additional JOINs, which improves query performance and maintainability while keeping your data secure.
Choose an approach
Retool offers two ways to restrict rows, and they differ in where the rule lives.
| Filtering in queries | Access policies | |
|---|---|---|
| Where the rule lives | In each query | On the resource |
| Applies to | Only queries written to include it | Every query against the resource |
| Resources | Any resource | PostgreSQL |
| Plan | Any plan | Enterprise |
| Who configures it | Anyone who can edit the query | Users with Own access to the resource |
Filtering in queries gives you full control over the shape of each query, and works on every resource type. Its limitation is that the restriction only holds where it was written. A new query that omits the filter returns unfiltered data.
Access policies move the restriction to the resource, so it applies to every query Retool runs against it, including queries in the query library and those generated by AI. Policies can reference the same current_user properties in their row filters. Workflow queries against an enforced resource are blocked.
The two can be combined. A policy sets the outer boundary of what a user can reach, and query-level filtering shapes what a particular app shows within that boundary.
Additional security
To ensure the security of the request passing from the server to the database, you can take several approaches. Deploying the server and the database within the same virtual private cloud (VPC) avoids exposing requests to the public internet. You can also enable encryption communication between the server and the database using SSL certs.
This architecture has two advantages:
- Because the database filters the result, unfiltered data isn't sent over the network. This minimizes the amount of data exposed over the network, so if there is a security breach, less data is at risk.
- Latency is also reduced because the data transmitted over the network is minimized.