Set Postgres config parameters using set_config (for RLS)

Queries and Resources
1

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.

BEGIN;
set_config('request.user_id','user1',true);
-- Do stuff
COMMIT;

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).

Thanks!

2

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

image
image1110×1124 42.9 KB

3

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: current_user.email) unless you turn off prepared statements on the resource.

set