`supabase db reset` drops custom role

[msickpaler]

Describe the bug
When running supabase db reset, the custom end_user role is automatically dropped. Subsequent migrations expect this role to exist, leading to errors during the reset and migration process.
This seems to contradict the explanation in the documentation that reset does not initialize custom roles. Is this a bug?

To Reproduce
Steps to reproduce the behavior:

1. setup local development
2. start supabase
3. create custom role with psgl
4. run supabase db reset
5. run SELECT * FROM pg_roles; in DB

Expected behavior
Custom roles are not deleted.

Screenshots
If applicable, add screenshots to help explain your problem.

System information
Rerun the failing command with --create-ticket flag.

• Ticket ID: [e.g. ab1ac733e31e4f928a4d7c8402543712]
• Version of OS: mac sonoma 14.5
• Version of CLI: 1.223.10
• Version of Docker: 27.0.3
• Versions of services: [output from supabase services command]
  SERVICE IMAGE │ LOCAL │ LINKED
  ─────────────────────────┼──────────────────┼─────────────
  supabase/postgres │ 15.6.1.113 │ 15.6.1.134
  supabase/gotrue │ v2.158.1 │ v2.164.0
  postgrest/postgrest │ v12.2.3 │ v12.2.3
  supabase/realtime │ v2.30.34 │ -
  supabase/storage-api │ v1.6.8 │ v1.13.0
  supabase/edge-runtime │ v1.62.2 │ -
  supabase/studio │ 20241106-f29003e │ -
  supabase/postgres-meta │ v0.84.2 │ -
  supabase/logflare │ 1.4.0 │ -
  supabase/supavisor │ 1.1.56 │ -

[sweatybridge]

Thanks for reporting this. The local behaviour is correct as we recreate the db container.

I will update the docs to clarify that roles are not reset when running with --linked flag only.

[sweatybridge]

If you need to seed custom roles locally, it's best to add those statements in supabase/roles.sql