Setting environment (db connection) based on user roles

We are on cloud retool.com and on the Business plan.

Goal: select database-connection (environment) based upon user-role.

When Bob logs in, he has role XYZ and the app, via environment setting, uses database-connection XYZ. When Carol logs in, she's got role ABC and the app switches environments to use database-connection ABC. Neither can see data from the other's database.

The table structure for each database is the same, behind the scenes, of course.

The "home" app shows buttons for each user that depend on their roles, and each button launches a separate app. It's in the secondary app where the role-based database-connection is critical. If we could switch environments in the "home" app before the secondary app launches, that would be ideal.

This is similar to this post, but while they are using one connection to many databases, we are using distinct connections to each database. (External requirement for keeping the security-conscious folks from worrying.)

Note: we do NOT want to give users a popup menu where they can select their environment, no bueno! We want the environment chosen automatically and transparently, based upon their user-role.

Otherwise it's looking like we need to make a separate copy of the app for each database. :frowning:

Attempts:

We tried the idea mentioned here, appending ?_environment=ABC to the url, and that is functional... but it exposes the environment to the end user. (We can use javascript to check if the user's role matches the environment and abort if not, of course—but we're hoping for an actual environment-switch under the hood).

We can see that {{retoolContext.environment}} reflects the currently-chosen environment, but unfortunately we can't change the environment that way.

When Googling this snag, we ran across some Google AI suggestions about using a Javascript POST to switch environments, but that ran into authentication hassles that would drive our users mad.

Is it possible to switch environments? Any suggestions welcome... :crossed_fingers:

Live long and prosper! :vulcan_salute:

Hi @trillich, Welcome to Retool Community

Have you tried using a dynamic resource?

Screenshot 2024-10-04 at 3.12.27 PM

3 Likes

Well batten my hatches, hadn't noticed that feature before! That will get us partway there.

So instead of switching actual environments, we'd just choose a specific resource connection to get to the appropriate database. We will pursue that for certain, thanks for the nudge!

Along with a database-connection-switch, we also want to switch themes. Bob from XYZ gets the XYZ theme/logo/colors and Carol from ABC gets the ABC theme/logo/colors. We've got the themes defined within Retool, and colors throughout rely on the theme object ({{theme.highlight}} for example). Is that possible?

Thanks, @ZeroCodez !

Dynamically changing theme colors is currently not supported.

1 Like