Workflow Has No Owner After Migration

[andyjoyous]

Describe the bug
I just migrated from 1.7 to 1.27. There was a commit in the past 7 days where @netroy refactored the columns in the shared_workflow table. This migration seems to improperly change some of the workflow's roles to not have an owner. Because of this, some workflows cannot be enabled due to not having an owner. Note that this is occurring with a Postgres DB

An example: Before the migration, I had a workflow where 3 users have the editor role and 1 user has the owner role. After the migration, all users had the editor role and no one had the owner role.

I believe this is the code that caused the issue: d6decea#diff-3a34042a1d622892ab7fd5ed3f4027bbf7a70fbddf1608d53846db7ca9e5931dR61

Here are my attempts to quickly fix the migration: 454c8d9

To make it easier to read, here is the raw query the original code is making:

UPDATE "shared_workflow"
            SET role = mapping.role
            FROM (
        SELECT 'workflow:' || R.name as role, T."workflowId" as id
        FROM "shared_workflow" T
        LEFT JOIN "role" R
        ON T."roleId" = R.id and R.scope = 'workflow') as mapping
            WHERE "shared_workflow"."workflowId" = mapping.id

The raw query using my changes:

UPDATE "shared_workflow"
            SET role = mapping.role
            FROM (
        SELECT 'workflow:' || R.name as role, T."workflowId" as id, T.userId as uid
        FROM "shared_workflow" T
        LEFT JOIN "role" R
        ON T."roleId" = R.id and R.scope = 'workflow') as mapping
            WHERE "shared_workflow"."workflowId" = mapping.id AND "shared_workflow"."workflowId" = mapping.uid

I believe the fix is to make the WHERE clause also match the user ids. After applying the changes, I reran the migration and I have 1 owner and multiple editors for a workflow as expected.

To Reproduce
Steps to reproduce the behavior:

1. Create a workflow that is shared by multiple people in an old n8n version before this migration. Make sure the workflow is enabled
2. Change to the latest n8n 1.27 version so that the migration is triggered
3. Open up n8n and you should see some red errors. Toggle the workflow off and on and it should error with a workflow having no owner

Expected behavior
There should be no errors regarding lack of ownership for a workflow

Environment (please complete the following information):

• OS: MacOS Sonoma 14.3
• n8n Version 1.7 -> 1.27
• Node.js Version 20.11.0
• Database system Postgresql
• Operation mode: queue

Additional context
Add any other context about the problem here.

[netroy]

Thanks for reporting this, and for including your suggestions.

We discovered this on one of our test instances last night, and have already been working on fixing this. Looks like the additional userId check was there in one of the earlier versions of the migration and got removed at some point.

We plan to rewrite this migration to make it more readable (and hopefully testable), but for now I think your suggestion would be much quicker, so I'm going to create a PR with these fixes for both up and down migrations.