[Bug] Migration fails on a new system

[chevdor]

Bug Description

Deploying on a fresh database.

Error: Failed to initialize application state: Failed to run migrations: while executing migration 20231130140000: error returned from database: relation "auth.users" does not exist

How to Reproduce

• new postgres db
• using postgres://postgres:somepass@postgresql:5432/appflowy

Expected Behavior

The migration works.

Operating System

K8S

AppFlowy Version(s)

appflowyinc/appflowy_cloud:latest

Screenshots

(no worries about the visible password, it is a test system with a temp password already no longer valid...)

Additional Context

No response

[khorshuheng]

Please see: #861

[chevdor]

Here is the mirgation: https://github.com/AppFlowy-IO/AppFlowy-Cloud/blob/main/migrations/20231130140000_user_delete_trigger.sql

[chevdor]

@khorshuheng thx for such a speedy pointer, that seems to be indeed spot on...

[khorshuheng]

There are actually some additional steps needed if you want to set this up on k8s. There's a work in progress here (which is not fully working yet) - https://github.com/khorshuheng/appflowy-self-host-resources/tree/main/charts/appflowy

[khorshuheng]

I am still investigating if we can simplify the setup via: #891

[chevdor]

I did find the script mentioned in the other issue but that was indeed leading me to the next issue you described.
Thanks for laying all of that down ! I did not expect some of the assumption (ie that the database must be named postgres) but that's not a big deal for now.

For now, I am using a manual deploy so I consider I still fall under the docker umbrella :)
I will check your chart.

[khorshuheng]

In my chart, you will see that I am using "appflowy" user and "appflowy" database. But it's still not fully working, because AppFlowy cloud service will fail to create user's data after login, due to row level security (which can be overcome with the draft PR above)

[chevdor]

Yes, that sounds already better than assuming the user can use the postgres DB...
I don't mind that for now though as long as I can get the db to a usage state. You write up is great, I am following your steps....

[chevdor]

From what I have seen so far, there are a few issues with appflowy_cloud but that's manageable.
There are many more issues due to gotrue regarding some migrations that no longer seem to be in the repo: 2021..2023 but I am not through yet.

The issues are mostly (if not only) around auth.users and before does not seem to contain everything that is needed for gotrue to successfully apply all migrations.

Why were the old migrations removed ?

[khorshuheng]

Basically, gotrue service will create the necessary migrations on start up. There are certain operations that cannot be handled automatically by gotrue, such as creating the auth schema (

AppFlowy-Cloud/migrations/before/20230312043000_supabase_auth.sql

Line 33 in ba8a670

CREATE SCHEMA IF NOT EXISTS auth AUTHORIZATION supabase_auth_admin;

), granting access to the auth schema to the supabase_auth_admin user, and creating some additional roles. That's why they are in the before folder.

Without seeing the detail error message, i can only speculate. Most of the users faced the issue due to one or more of the following reasons:

1. The auth schema is not created (CREATE SCHEMA IF NOT EXISTS auth AUTHORIZATION supabase_auth_admin)
2. auth schema is created but the default search path for the gotrue database user isn't set to auth (ALTER USER supabase_auth_admin SET search_path = 'auth';)
3. Using a different username and password for gotrue database user (if you use the default migration script, it should be supabase_auth_admin, and the password is root)

[khorshuheng]

As for your questions on old migrations - as explained, the migration is handled by the gotrue container. If you exec into the gotrue container, you will see all the migration files in that container, similar to https://github.com/supabase/auth/tree/master/migrations

[chevdor]

If only I could....

Seeing the image, it makes sense that exec does not work. But I see where those migrations are.
In my case, one of the issue is likely that I ran the appflowy_cloud migrations before the gotrue ones.

[chevdor]

If anyone else runs into that, here are the gotrue migrations

[chevdor]

I ran into several migrations issue and here is my take-away:

• You must run the gotrue migrations first (ie start gotrue prio ever starting appflowy). Do not start AppFloyw until gotrue starts happily. This should be stressed in the doc IMO as it would have saved me lots of troubles.
• Once gotrue is happy, you may start AppFlowy and it migrations will run

I did run into AppFlowy migrations issues as well.
On of them being that SELECT uuid_generate_v4(); would fail.
Searching for a solution, you will find that CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; may help. It did not fully solve the issue in my case.

I mentioned the issue in #823 (comment) and @speed2exe provided some critical hints.

I found out that neither of the following will make every service (ie gotrue + appflowy) happy:

• ❌ ALTER ROLE postgres SET search_path TO auth;
• ❌ ALTER ROLE postgres SET search_path TO public;

The best is likely to start checking what your search_path is:

show search_path;

If only one value is listed, you will need the following:

However, the following did help unlocking further mighrations:

ALTER ROLE postgres SET search_path TO auth,public;