Permission denied for schema public on AWS RDS Retool DB

Self Hosted Retool
1

I'm trying to setup Retool DB with my RDS database and I'm very stuck.

I go to /resources?setupRetoolDB=1 and enter details, test connection works. I look in the Postgres instance and I see the 2 new randomly named databases (what was the point of entering the DB name to connect to?) and the similarly named roles. But I can't create any tables. When I try to create my first table, I get the permission denied for schema public error. I've tried granting privileges to the retool user (the user used to connect to the RDS instance, setup by AWS, with rds_superuser) on the database and nothing, I've tried changing the owner of the new random databases (just get different errors). I cleared everything out, added the pgcrypto and uuid-ossp extensions on my production_retool_retooldb database and then did the setup again. I've tried adding those extensions to the newly created databases.

It's not clear what exactly it's trying to run against the DB. I've looked at debug logs and I see the create table query, but it doesn't say what the connection string it's using is, the user it's connecting as, etc. I'm not sure where else to look to debug, or why it would be doing this in the first place.

Retool: 3.6.5
RDS Postgres: 15.3
Deployed on ECS Fargate

2

I enabled some more query logging on the RDS instance and can see it's running the query as 6b1146bdf117466895cd36c151f9adf1@6b1146bdf117466895cd36c151f9adf1, so as the role it created against the database it created.

The database is owned by that user/role. I've also tried GRANT ALL ON SCHEMA public to "6b1146bdf117466895cd36c151f9adf1" and still nothing.

3

Hey dude.
try to select db first and then execute grant statement.

like \c production_retool_retooldb and then GRANT.

4

I think I may have tried that before, but tried it again, and nothing.

I've granted the random user to the production_retool_retooldb DB, the retool user to the random DB, and both to the public schemas on each.

5

Try it with RDS user, known as "Master username". rds_superuser usually reflects to internal RDS user, accessible only by AWS.
Also you can directly connect to RDS within your local client (psql ?) and try to create table etc. That helps me a lot during debug postgres rds permission for another tool.

You'll be able to determine is the problem really in permission.

6

Hi @darkhelmetlive ,

I've ran into the same issue. The db created is owned by the associated user that has been created. You won't be able to acces that db unless you can grant access to that db to another user. This however requires superuser permissions which you usually don't have on managed instances on AWS or Azure.
The only way to access that db is by using the internal retool db editor.

7

That's interesting, because I was able to create DB on the same RDS for temporal and changed owner successfully.
Could you please elaborate on this?

You won't be able to acces that db unless you can grant access to that db to another user. This however requires superuser permissions which you usually don't have on managed instances on AWS or Azure.

My workflow was:

  1. Create db as RDS's master user in default tablespace;
  2. Make application's user owner of the table (not sure if needed tbh);
  3. Grant all needed privileges to application's user.

However pay attention to tablespace, pg_default and pg_global will really produce the error about lack of permission.