db-init scripts not compliant with RDS hosting

[Erin-Boehmer]

Bug report

Supabase's self-hosting documentation suggests decoupling the db from the middleware, listing AWS RDS as a viable option. But running init-db and migration scripts against a fresh RDS instance causes errors. Supabase provides no documentation for a workaround.

Describe the bug

The init-db / migration scripts try to execute commands reserved for a superuser. This is ok in postgres, but because RDS is a managed service, it restricts the default permission set for the postgres user (AWS documentation here). Specifically it disallows superuser and replication permissions for the default postgres user.

This leads to the error:

helicone=> alter user  supabase_admin with superuser createdb createrole replication bypassrls;
ERROR:  must be superuser to alter superuser roles or change superuser attribute

It's unclear how those using RDS with supabase should handle the discrepancy.

To Reproduce

1. Create new RDS instance with default postgres main user
2. Attempt to run init-db scripts in database

Expected behavior

I expect the init-db and migration scripts to complete successfully when I follow the "Self-Hosting" instructions (here).

System information

• RDS running postgres 15.2

Additional context

Encountered while mod'ing Helicone's docker-compose.yml based on supabase (here)

Related - also would have appreciated you linking to AWS's docs describing how to install the pgjwt extension (took me some searching to find & set up).

[fvaldes33]

Today I tried to do the same with the same issues you mentioned. I was able to get pgjwt installed, probably found the same documentation/guides you found.

Right now, at a total stand still and cannot move forward with this set up.

[Erin-Boehmer]

@fvaldes33 the best I've found are these RDS / Aurora migration scripts in the community-built supabase-on-aws repo. They'll get you closer, but still require some modifications. The supabase-on-aws migrations also do not include the additional migrations from supabase's docker repo here. You'll need to add those separately.

Even getting these running, I'm still blocked because RDS does not support several important supabase extensions, namely:

• pg_net
• pg_graphql
• pg-safeupdate
• supabase_vault (less important for me, but FYI)

[fvaldes33]

I need to get a POC running asap so I might just fall back to running postgres inside an EC2 for now. Hope the supabase folks give this ticket some attention, would love to get this running the "aws" way for the long term.

[koryonik]

@fvaldes33 any news on your side?