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.

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.

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.