Multi-tenant RLS via Resource Connection Options and Pool Isolation

💬 Queries and Resources
1

Hi Retool Team,

I am implementing a multi-tenant architecture using Row-Level Security (RLS) on a third-party Postgres instance (Supabase). To keep our app queries clean, I want to apply the tenant_id at the Resource level rather than in every individual SQL statement.

My Plan: In the Connection options of my Postgres Resource, I intend to use the following:

  • Key: options
  • Value: -c app.tenant_id={{ current_user.metadata.tenant_id }}

On the database side, our RLS policies use current_setting('app.tenant_id', true) to filter rows.

The Security/Pooling Question: I’ve seen community discussions suggesting that when {{ }} dynamic variables are used in Resource settings, Retool creates isolated connection pools for each unique value. I want to verify this to avoid "session leakage" where User B might inherit a connection from a pooler that still has User A’s session variable set.

  1. Pool Isolation: Does Retool guarantee that a connection with -c app.tenant_id=123 will never be served to a user whose metadata evaluates to tenant_id=456?
  2. Connection Limits: Since we have a small user base (~100 users), are there any known limits regarding the number of separate pools Retool will maintain before we see performance degradation?

Our goal is a "zero-leak" environment, so any technical deep-dive on how Retool manages these dynamic connections would be incredibly helpful!

Thanks!

2 Likes
3

Hi @MiguelOrtiz,

Great question! Let me ask our resources team if using {{}} in resource settings will result in isolated connection pools for each value.

I will let you know what I hear from them!

4

Just heard back from the team!

Retool does create isolated connection pools.

Standard is for a hash of the evaluated resource config to be included in the connection pool cache key.

There is a hardcoded limit of 600 pools stored across all resources, so there would be a performance degradation of they exceed that limit.

For pool isolation that is correct, the data will never be served if the metadata does not match that of the resource. Maybe as an edge case if a user is somehow able to spoof their tenant_id in the app. But as long as the resource is called by the correct tenant_id the data will always be correctly paritioned.

5

Amazing, thanks! I need to try it and this may be sometime in the next 2 months, but once I get to it I'll revert back to this ticket.

1 Like