ERROR: insert or update on table "userGroups" violates foreign key constraint "usergroups_groupid_foreign"; DETAIL: Key (groupId)=(3) is not present in table "groups".

[emanreztuneB]

Unexpectedly occurred after a Debian reboot and is persistent:

2022-05-06 22:10:06.018 UTC [2872] wikijs@wiki ERROR:  insert or update on table "userGroups" violates foreign key constraint "usergroups_groupid_foreign"
2022-05-06 22:10:06.018 UTC [2872] wikijs@wiki DETAIL:  Key (groupId)=(3) is not present in table "groups".
2022-05-06 22:10:06.018 UTC [2872] wikijs@wiki STATEMENT:  insert into "userGroups" ("groupId", "userId") values ($1, $2), ($3, $4), ($5, $6) returning "userId"

Registration does not longer assign users to the specified groups. Altering and recreating those groups, changing the self-registration default group and restarting the application do not solve the problem. Disabling and enabling the self-registration does not work either.

[lealzora]

I have the same problem.

How the settings are made:

1 - Access the Administration environment
2 - Access the "Authentication" module
3 - In the Active Strategies options select "LDAP/Active Directory"
4 - Apply strategy settings
5 - In the Registration options:
5.1 - Enable the "self-registration" option
5.2 - In the "Assign to group" option, inform the group to which the user who self-registers is to be linked
I have the same problem.
The error occurs when using the "self-registration of user" functionality.

What is happening:

The user who self-registers, after informing the login data, an error message is displayed.
"insert into 'userGroups'('groupId','userId') values ​​($1,$2),($3,$4),($5,$6) returning 'groupId' - insert or update on table 'userGroups' violates foreign key constraint 'usergroups_groupid_foreign' "

Questions:
1 - Is there any solution to be delivered for future versions?
2 - Any contour suggestions?

[emanreztuneB]

I was able to circumvent the problem by manually deleting the entry in the wiki database that saves the automatic assignment group ids.

[Zappo-II]

Hi there, although this thread is quite ancient I happened to stumble upon it trying to solve the same problem...

My Case and Solution

In my case I am using GOIDC / OAuth2 but I tend to "believe" that this does not matter since I also happened to have an auto assignment in place that was functioning very well in the first place...

After having some Groups defined and two of them used for some time in that auto assignment I decided that one of those groups was not necessary any more and I deleted it in the groups configuration of admin interface, not remembering that this group is member of the auto assignments in authorization configuration...

After that, each and every auto assignment failed and threw the above mentioned errors in postgres container...

What I had to do to fix it (your mileage may vary) is:

• connect to the postgres db...
• insert a Table Entry with the "lost" id (in my case it was id 5, taking settings from an existing group, in my case id = 4) into the groups Table...
• Like this: insert into groups (id, name, permissions, "pageRules", "isSystem", "createdAt", "updatedAt", "redirectOnLogin") select 5, 'lostgroup', permissions, "pageRules", "isSystem", "createdAt", "updatedAt", "redirectOnLogin" from groups where id = 4;
• After that the group showed up again in the auto assignment configuration input field in administration - authorization...
• I then unchecked the recreated group in the auto assignment configuration and saved the settings...
• After that I delete the group in groups configuration and the error was gone when the next auto assign took place...

TL/DR:

WikiJS: 2.5.305
Postgresdb: 15.10

This is a Software Integrity Issue in wikijs administration, if a group gets deleted that is used for auto assignment it vanishes from display in the auto assign group assignment control but is not deleted from the auto assign configuration.

@ maintainer

Please check the referential integrity when a group gets deleted, obviously there is an entry left in the auto assign configuration...

• I would assume in table authentication field autoEnrollGroups that contains a json like {"v":[4]}...
• Any deleted group id has to be deleted from those jsons also...