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?

[olirice]

Supabase's self-hosting documentation suggests decoupling the db from the middleware

Could you please link me to this?

To my knowledge the first party solutions for self hosting are Docker via the Supabase CLI and BYO Cloud on enterprise

You can read more about those options here https://supabase.com/docs/guides/self-hosting

I'm going to close this for now as not supported but if you link me to the documentation implying that other hosted vendors are supported I'll re-open

[johndpope]

i spent a chunk of time on this - write up is here - https://github.com/johndpope/rds-supabase/tree/main/docker

there's a realtime / auth container both customised -

the gist is - i stand up a worker docker container / postgres - then dump the roles /users / data tables - see rds_schema.sql

i reduce the extensions (a bunch are not available with rds - pg_net - does web internet traffic - aws wont support) but the jwt i think is working.... my problem is the realtime container -

it's so close - i add a bunch of diagnostics -

i can get supabase to incorporate the external tenant database - it has all this stuff turned on.

supabase-realtime  | ✅ Control database connection successful!
supabase-realtime  | Checking PostgreSQL replication settings...
supabase-realtime  | wal_level: logical
supabase-realtime  | max_replication_slots: 10
supabase-realtime  | ✅ wal_level is set to 'logical'
supabase-realtime  | ✅ max_replication_slots is sufficient (10)
supabase-realtime  | Checking replication privileges for supabase_admin

I keep digging.