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.
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...
Live long and prosper!