Self-Hosted Retool - Unable to Create Local Retool DB

💬 Self Hosted Retool
, ,
1

Hey all,

Recently, we upgraded from the free version to the Team version of the self-hosted Retool to add additional users to our ecosystem. As an FYI, we have a Linux VM spun up in Azure behind our virtual network. We also have an externally hosted PostgreSQL where I've pointed in our Docker.env file to using the DATABASE_URL Parameter.

To get the server to grab our new license, I restarted the VM. The application came back up along with all of our applications/users/resources/etc. However, the local Retool DB was completely gone/wiped. All of our applications are Resource '728f3835-51c7-48dd-b3aa-58dcca680861' has either been renamed, deleted, or does not exist in production. Please select a different resource.'

I have attempted to look at the docker volumes, attached a variety of different volumes to the data volume in an attempt to see if it was looking at the local DB spun up by the docker compose to no avail. After a few days of troubleshooting and unable to find the database, we reluctantly decided to just start from scratch on the local Retool DB.

I went to the "/resources?setupRetoolDB=1" and entered all of the host/user/password/port information into the form that populates, and click the 'Test Connection' button and receive 'Successful'. I save the changes and it states that it updates the configuration of the Retool.

I then navigate to the Retool DB, and since it sees it as empty, it asks me to create a new table. This is where the problem now lies (outside of losing all of our local DB due to a server reboot). Whenever I try to create anything in the local Retool DB after configuring the DB, I get the error message: 'permission denied for schema public'

I have logged in as admin to the PostgreSQL server inside of Azure and ensured the account I gave to Retool has owner access and has access to every command, same issue persists.

I cannot get the application to create/see anything on the Retool Local DB which is breaking every application we've created.

At this point, we are hard-down. Any help would be greatly appreciated!
(Please let me know if you'd like me to supply any additional data, I'd be happy to grab and hand it over)

2

My understanding is that Retool will create separate login user(s) for the RetoolDB operation. So even you

it just means the 'superuser ' you entered in the form is a legit one but the user(s) it created might not have the neccessary permission by default.

Can you go directly to your PostgreSQL and run

select * from pg_user

to see if the Retool created user (looks like some random string) has appropriate permission on read/write? If not, you gotta grand them proper access first.

I'm not sure how comes restarting the VM can wipe your RetoolDB especially you exernally host your PostgreSQL. My guess is your data is still there just not visible on your current config. Are you able to see your previous tables when you logged in as admin to the PostgreSQL server?

3

Thank you for your reply!

I was able to login and run the requested command on the external database, and was returned the following:

postgres=> select * from pg_user;
             usename              | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil |        useconfig         
----------------------------------+----------+-------------+----------+---------+--------------+----------+----------+--------------------------
 azuresu                          |       10 | t           | t        | t       | t            | ******** |          | {search_path=pg_catalog}
 replication                      |    16395 | f           | f        | t       | f            | ******** |          | 
 retooladmin                      |    24741 | t           | f        | f       | t            | ******** |          | 
 6a3ea062c6d14b52a2e135d4b2a4a929 |    28320 | f           | f        | f       | f            | ******** |          | 
 40524794df88460da6b1d4b4c705814e |    28324 | f           | f        | f       | f            | ******** |          | 
 5747f20a66064da3bb270846ca2b4496 |    34773 | f           | f        | f       | f            | ******** |          | 
 5840896f2368463d94c6056ec230be1b |    34777 | f           | f        | f       | f            | ******** |          | 
 a8838bd09fc64c118b908136f146345e |    34789 | f           | f        | f       | f            | ******** |          | 
 729f506bf971480bb30533ce58026297 |    34793 | f           | f        | f       | f            | ******** |          | 
 7b32bc3d36eb426589003c7d80e67f87 |    35000 | f           | f        | f       | f            | ******** |          | 
 c6795ceaef944ce89fe3d86f43f632ab |    35004 | f           | f        | f       | f            | ******** |          |

I do see the users that have the 'service accounts' for the Retool users in there. After seeing those roles, I ran the following script on the database in hopes of getting the proper access to all of the 'service accounts':

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT usename
    FROM pg_user
    WHERE usename IN (
      '6a3ea062c6d14b52a2e135d4b2a4a929',
      '40524794df88460da6b1d4b4c705814e',
      '5747f20a66064da3bb270846ca2b4496',
      '5840896f2368463d94c6056ec230be1b',
      'a8838bd09fc64c118b908136f146345e',
      '729f506bf971480bb30533ce58026297',
      '7b32bc3d36eb426589003c7d80e67f87',
      'c6795ceaef944ce89fe3d86f43f632ab'
    )
  LOOP
    EXECUTE 'GRANT CONNECT ON DATABASE postgres TO ' || quote_ident(r.usename);
    EXECUTE 'GRANT USAGE ON SCHEMA public TO ' || quote_ident(r.usename);
    EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ' || quote_ident(r.usename);
    EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ' || quote_ident(r.usename);
  END LOOP;
END $$;

After running the above script, still no dice. When I'm attempting to either access the old existing database, it still fails. When trying to setupRetoolDB to the external postgres, same issue. I get 'permission denied for schema public' even after testing the successful connection.

Regarding why the app wiped the database? I have no idea. I attempted disabling the external database configurations and going back to everything housed inside the docker-compose.yml and held locally on the server. This resulted in me losing a lot more than the local Retool DB (as I believe the metadata IS PROPERLY pointed at the external postgres, as it never had any issues with accounts, applications, resources, etc.). The only thing that stopped working was the LOCAL Retool DB. And now I'm at a spot where I can't get anything back up and running :frowning:

4

Can you try granting those random string users SUPERUSER in case Retool doesn't write to just public schema?

Update:

Just find out Retool will create a database with the same name as the username, and store tables you created, though public schema, but under that database. So your user need to have permission on the database that has the same random string name.

You can also check if

  • your previous tables still live in one of the random string database

Here's the check script I use:

Firstly, login as superuser
Then

CREATE EXTENSION IF NOT EXISTS dblink;
select * 
from 
  dblink('dbname=<one of the random string username> user=<superuser name> password=<superuser pwd> host=<your postgre host> port=5432', 
         'SELECT schemaname, tablename, tableowner, tablespace FROM pg_catalog.pg_tables') 
  as x(schemaname text, tablename text, tableowner text, tablespace text) 
where schemaname not in ('pg_catalog', 'information_schema')
5

Unfortunately, I cannot grant either owner nor superuser (or create new superusers for that matter) inside of our Azure Database for PostgreSQL. After trying to simply grant CONNECT/CREATE to the random string username, I received errors that you must be owner of the database to perform those GRANT commands.

Then I attempted to make the admin user (defined by Azure) the owner of that database. I received the error 'Must be owner of the database to change owners.'

Which led me to this Azure forum post: CREATE SUPERUSER in PaaS offering of Azure Database for Postgres - Microsoft Q&A

The Azure Database for PostgreSQL server is created with the 3 default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;

azure_pg_admin
azure_superuser
your server admin user
Your server admin user is a member of the azure_pg_admin role. However, the server admin account is not part of the azure_superuser role. Since this service is a managed PaaS service, only Microsoft is part of the super user role.

This complicates things, as the user was created out of Retool utilizing the retooladmin account I had specified in the connection details form. However, I can't see the password to login as any of these users.

6

If I understand your issue correctly basically two parts:

  • Data loss....which is likely you / Retool established new sql users and new database in connection, and your previous data is still there but under different database
  • Permission....i'm not 100% positive, but my instinct tells me it's could associate with the user permission

you don't necessarily have to be superuser. as long as your account is capable of accessing the database with those random string name.

7

That's correct. The original issue was a reboot of the Linux VM, allowed the app to recover (since it's using docker compose up -d to run as a service). However, when it came back up. The local Retool DB was inaccessible. (Could they be related? Possibly. But I'm not sure why a reboot of the VM would have broken the connection/lost access since the db is external).

And yes, now that we have no retool DB, even when I try to instantiate a BRAND NEW local retool DB, it won't allow me to create tables due to the permission denied for schema public error. So we unfortunately have no way to move forward with a local DB currently.

Regarding the permission, I did attempt to set the owner of those tables to those users as the admin account (retooladmin), however, I received the following error:
ERROR: permission denied for database 6a3ea062c6d14b52a2e135d4b2a4a929 CONTEXT: SQL statement "GRANT CONNECT ON DATABASE "6a3ea062c6d14b52a2e135d4b2a4a929" TO "6a3ea062c6d14b52a2e135d4b2a4a929""

:sos:

8

I think we both see the permission issue here....but sorry i'm not super familiar with the Postgre hosted on Azure. Maybe someone from Retool team can help?

9

Good afternoon.

Wanted to supply a follow-up to this chain in hopes of helping further headaches get resolved.

After contacting Azure support, I was instructed to run the below command for each of the unique IDs/DBs that are created by Retool for the local account:

GRANT azure_pg_admin TO <UniqueIDString> WITH ADMIN OPTION;

After running this for all of the hash DB names in the Postgres table, the retool local DB was able to create new tables\records once again.

(Our original table data is gone-gone, not sure what happened or where it was being stored. Local PostgresDB of the docker container also didn't have the table information in there, so I'm at a loss on that front)