OIDC Issue: New users unable to login after switching to "Instance and project roles"

Questions
1

Describe the problem/error/question

Hi,

I’m setting up an OIDC server to work with n8n. Everything was working fine, but after I changed the User role provisioning setting to “Instance and project roles”, new users are no longer able to log in.

Interestingly, the login still works perfectly for users who already exist in the system. It seems like the automatic provisioning for new accounts is failing under this specific role setting.

Could you help me identify what could cause the error?

Thanks!

What is the error message (if any)?

{"code":0,"message":"insert or update on table \"project_relation\" violates foreign key constraint \"FK_5f0643f6717905a05164090dde7\"","stacktrace":"QueryFailedError: insert or update on table \"project_relation\" violates foreign key constraint \"FK_5f0643f6717905a05164090dde7\"\n    at PostgresQueryRunner.query (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/driver/postgres/PostgresQueryRunner.ts:331:19)\n    at processTicksAndRejections (node:internal/process/task_queues:105:5)\n    at InsertQueryBuilder.execute (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/query-builder/InsertQueryBuilder.ts:142:33)\n    at SubjectExecutor.executeInsertOperations (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/persistence/SubjectExecutor.ts:385:38)\n    at SubjectExecutor.execute (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/persistence/SubjectExecutor.ts:134:9)\n    at EntityPersistExecutor.execute (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/persistence/EntityPersistExecutor.ts:182:21)\n    at ProjectService.addUser (/usr/local/lib/node_modules/n8n/src/services/project.service.ee.ts:584:10)\n    at /usr/local/lib/node_modules/n8n/src/modules/provisioning.ee/provisioning.service.ee.ts:251:5\n    at EntityManager.transaction (/usr/local/lib/node_modules/n8n/node_modules/.pnpm/@[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/entity-manager/EntityManager.ts:152:28)\n    at ProvisioningService.provisionProjectRolesForUser (/usr/local/lib/node_modules/n8n/src/modules/provisioning.ee/provisioning.service.ee.ts:245:3)"}

docker logs:
n8n-main | 15:25:38.366 error insert or update on table “project_relation” violates foreign key constraint “FK_5f0643f6717905a05164090dde7” { “file”: “error-reporter.js”, “function”: “defaultReport” }

Information on your n8n setup

  • n8n version: 2.4.4
  • Database (default: SQLite): PostgreSQL
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: MacOS 26.3
2

Welcome to the community @Ania_Czanasz when you set role provisioning to “Instance and project roles”, n8n tries to assign new users to projects automatically. The foreign key constraint FK_5f0643f6717905a05164090dde7 is failing because it’s trying to link a user to a project ID that doesn’t exist in your database.

Create at least one project in n8n’s UI before enabling “Instance and project roles”. New users need a valid project to be assigned to during provisioning.

Can you check if you have any projects created in your n8n instance? If not, that’s likely the issue.

Let me if this helps!

1 Like
3

Thank you for you reply @Miliaga! Unfortunately, that’s not the case. As I mentioned, the flow works perfectly fine with users who already exist in the database, and roles are being provisioned correctly. I double-checked the project IDs that we use, and they are correct as well. This situation only happens when a user doesn’t exist in the database.

4

Looking at the stacktrace more closely, the error is happening in `provisionProjectRolesForUser` which calls `ProjectService.addUser` — it’s failing when trying to create the project_relation record for a brand new user. The foreign key constraint suggests the user record itself might not be fully committed to the database yet when n8n tries to create the project assignment, which would explain why existing users work fine but new ones fail. What version of PostgreSQL are you running? And are you using any custom transaction isolation settings? This feels like a race condition in the provisioning flow where the user insert and the project_relation insert aren’t happening in the right order or within the same transaction.

5

I’m using postgreSQL 14 and I don’t have any custom transaction isolation settings

6

This is interesting, PostgreSQL 14 with default isolation settings should handle this fine. The fact that it’s specifically failing on the foreign key for project_relation during new user creation makes me think there might be a race condition in n8n’s provisioning code where it’s trying to create the project assignment before the user insert is fully committed within the same transaction. Could you try temporarily switching back to just “Instance roles” to confirm that works, and also check if there’s anything unusual in your OIDC claims mapping for the project roles? Specifically what claim are you using to pass the project IDs and roles to n8n?

7

Switching back to “Instances roles” works as a charm. I’m passing following claims:

{
“sub”: “lime:1”,
“email”: “[email protected]”,
“family_name”: “”,
“given_name”: “Administrator”,
“n8n_instance_role”: “global:member”,
“n8n_projects”: [“y9xXb7L7LF8nKgpT:editor”, “d9jwn77R22oxgjOA:editor”]
}

where y9xXb7L7LF8nKgpT and d9jwn77R22oxgjOA are valid projects IDs.

8

I still need help with this