This got closed recently so I could not continue the discussion as I did the work: Rotate encryption key and JWT secrets. Maybe @joeBumbaca can continue this thread?
I have some questions in regards to my changes, and I follow option A for I dont have too many resources.
How do I setup the new retool DB setup?
I tried on a test environment and retool DB was the only thing I could not change credentials, I had to setup the DB over again. With this resources?setupRetoolDB=1 But when doing that I feel that I lost the existing tables. I might be wrong but I ended up starting with a setup table modal that I believe i went through the first time. Is it possible to change the credentials without this happening?
I can confirm that there was created a new database and the old one was not attached to the UI.
What null value should I use in JSON variables
I experienced that setting the values to "null" inside the JSON did not work optimally. There were still places where the value in the input field was "encrypted on the server". I assume I set the wrong null value?
Workflows migration
I just delete the encrypted API key and reopen the workflows and they should be back in business?
Hey @snorreentur,
How do I setup the new retool DB setup?
Was this when setting up a new instance? Or after changing the encryption key in an existing instance?
Based on your description it sounds like setting up a new instance, but want to confirm the steps you've taken.
What null value should I use in JSON variables
Can you share the query that you used? I'm assuming that there were just some options that were missed in the update.
Workflows migration
Correct, when you reopen the workflow, you should have a new API key assigned. You would then need to change any webhook triggers to use that new key.
Thanks for getting back to me!
How do I setup the new retool DB setup?
So sorry for the bad wording here, but its not really setting up a new instance. I have existing data and change the encryption key. But when I then load the resources, I dont have a DB, and I cant access existing data. When going into the DB resource im asked to create a new one. I assume this is because the table is a unique ID used per environment, and might be connected to the encryption key?
All I need is to be able to change the encryption key, and not loose the data.
I have tried backing up and syncing the data again, but I get some strange errors. Can try some more and add the logs if that is helpful?
What null value should I use in JSON variables
I did not do a query since I dont have that many entries. I just took out the JSON and replaces the values with regex and entered : null, where there where encrypted values.
Hey @snorreentur, sorry I missed this. Pinging our internal team that works on Retool DB to take a peak here. Yes, any logs etc that you have on the errors would be very helpful.
I did try to get any useful logs, but in reality I dont see any logs that makes sense and is related to this.
But I have verified the data is there but I believe the database name is created based on encryption key, and hence its making another one, and also why its not finding the old one.
I got a personal follow up and wanted to post the solution here. The following steps was provided to me.
But in short, its all about changing the password of the existing system created database user for the retool db, it is a hashed value. Then use configuration variables in settings to be able to use the new encryption value to encrypt an updated database connection string, by inputting it into a variable, which stores the value you set as a retool encrypted value. Then one can take this encrypted retool value, and swap it out in the options this particular resource. And the old DB will be back in business!
Here are the concrete steps provided by retool:
- Connect to DB with
psql as superuser (probably the one you originally initialized Retool DB with)
\l to list the databases, there will be one hash-looking name per Retool environment (the DB name is same as user name)
\c to connect, \d to list tables and/or query so you know which environment the DB is for (ie: staging, production etc)
- Change the password for the retool created user
alter user USER_NAME with password 'NEW_PASSWORD';
- Create a new connection string for the user with the new password. The string has a format of:
postgresql://USER_NAME:NEW_PASSWORD@HOST:5432/DB_NAME
- Store that string into a dummy configuration variable
select value from config_vars cv left join config_var_values cvv on cv.uuid = cvv."configVarUuid" where name = 'DUMMY_NAME'; to get encrypted value
- Copy
resources.options JSON for corresponding Retool DB resource in text editor.
- swap
... value inside __RETOOL_ENCRYPTED__(...)
update resources set options '{... options ...}' :: jsonb where id = RESOURCE_ID to update the DB with the new value
This should let Retool connect to the RetoolDB with the same username and new update password encrypted with the new key.
Glad that worked for you @snorreentur! Thanks for beating me to the follow up here 
! We are working internally to simplify this process, but for now this should work. Definitely acknowledge that this isn't the most elegant solution, mainly due to not having UI to reconfigure RetoolDB environments etc. I'll update this topic when we've gotten this process into a different state.