How to send current_user information to the database

💬 Queries and Resources
,
1

Hello!
We are trying to implement a scalable multi-tenant approach in Retool, without passing the current_user to the db in a query.

We want to avoid this approach to prevent developers from accidentally forgetting to add the filter section in, allowing all data to be visible.

What we are trying to achieve is to run a simple
select *
in the app, and have it select only the rows that your retool user is allowed to see.

This could be possible by passing the current user object to the database at the resource level, but it does not seem to be possible with the simple SQL resource that Retool provides.

We found a possible solution by using postgREST, which allows you to connect to the database via REST API, and so we could pass the current_user object as a header there.
You can then run queries by calling the rest endpoints, but unfortunately, complex queries are impossible without using a library that Retool does not support.

Is there any other approach we could consider?

Note that we are on the business plan.

1 Like
2

Following as I would be curious on the solution that would save me from double checking all my queries have the appropriate filters.

3

Hello @Backofficely!

Very interesting use case. I completely understand the desire to read the data of the current_user and then use this information with some logic to apply default filters to queries which the user has access to.

As far as I am currently aware, there is not a true work around that would allow you to implement this :sweat: but I can definitely make a feature request to see what might be possible.

If you can give me examples of filtering you would like to do and what values you want to base the filtering on that would help me to best explain the feature request to our engineering team :slightly_smiling_face:

Currently, as per our docs on prepared statements for queries, you can use {{}} for embedded expressions to reference a value.

Such as:

SELECT id, name, subscription
FROM sample_users
WHERE expiration < {{ date1.value }}

However, you cannot use {{ }} embedded expression to dynamically specify names or actions (e.g., a column name or SELECT ).

The docs mention that PostgreSQL does not support dynamic table names in prepared statements, I am not sure if the same is true for SQL but I believe it needs some tricky workarounds.

I am imagining a tool using inputs to create a JSON object, to match a key from the current_user with a value that would be either a fully prepared SQL statement or specific values of a partially prepared statement, the trickiest part is going to be related to table names and SQL actions (like SELECT).

4

Also if you could share details on how you are applying the logic for query filters when users do fill in the filter section, that would be great!

5

Need to test this but:

  • The database resource for Postgres supports connection options that allow you to use the current_user variable.
  • If you use the -c option to set a global variable, this should enable you to use standard RLS for the user's session.
  • So if this is properly enforced in your resource, and properly enforced in all the tables you use, you would be able to omit all relevant security filters (though you may retain them for paranoia's sake).

If I end up doing this I'll report back.

1 Like
6

Great idea @Phil_Dakin! Thanks for chiming in. I am definitely curious to hear back from you on if that does work if you try that out.

Looking at this thread again, a possible work around for @Backofficely would be to enforce table, row, or column level security would likely need to either use permission controls that are built into Retool, as this is the best option for scalability.

Or use some Javascript queries to run and validate that the current_user has attributes that would give them permission to run queries that have specific security requirements.