Support Setting PostgreSQL (SET) Variables

Within the Advanced tab of a Resource Query, add the ability to SET sql variables before a sql command is executed. For example:

SET my.user.activeUserId = {{localStorage.values.activeUserId}};

For example: This can be very useful for things like row security policies where we can use a session variable to only show rows that belong to the active user.

Row Security Policies are a great feature of PostgreSQL and it would be great if we could leverage them in Retool.

1 Like

Thanks for the feedback! I'll post here when this is something we're able to support

Hi @erichosick This feature should be shipping soon! It's set to go out in Retool v2.125, which will be available next week

Thanks again for your feedback!

Is there any documentation on this feature? I can't see anything under the query advanced tab.

Hi @Tam,

Thanks for checking in!

I'd defer to the Postgres docs since it doesn't look like we have any Retool specific docs on this. The feature isn't listed in the advanced tab. Rather, you'd use the Postgres integration & write your queries in 'SQL mode,' where you can write multiple Postgres queries

Is it possible to add it by default to all queries of a given resource?

Answering my own question: session variables can be set from the connection string. Which can be set for each space. Proper RLS.

Hi.
Just for record there is another way to do it. Set the resource options like this:

Then you can write SQL like this:

SELECT current_setting('se_retool.user_email')

For more information look here: PostgreSQL: Documentation: 17: 32.1. Database Connection Control Functions