Set Postgres config parameters using set_config (for RLS)

I am trying to connect to a Postgres database that makes heavy use of RLS. The RLS is implemented using transaction-scoped config parameters, e.g.

-- Do stuff

The row-level security policies use a number of parameters (user_id, user_email, tenant_id, etc.) to allow/disallow certain operations.

In other Node.js applications these parameters are automatically set by the connection handler when new transactions are executed.

Is there anyway I could implement this with Retool? Ideally, each query is wrapped in a transaction and prepended with 1-n set_config statements setting config parameters to Retool user information (and metadata?).

Alternatively, I would be thankful for pointers allowing me to use a RLS-secured Postgres database that don't involve bypassing pg-native RLS with manually filtering (WHERE).


FYI: what happens when you try this in a query:

Hey @ostollmann! As far as I know this isn't currently possible in Retool currently. I've had success using the Postgres SET / RESET keywords which may be an option for you. One major caveat to this is that Postgres queries that use transactions cannot contain dynamic values (ie: unless you turn off prepared statements on the resource.