Using Boundary 0.2.0, I had built a fresh PostgreSQL database before I started to experiment with OIDC Auth Methods for Auth0, AzureAD, and Okta as IdPs.
On their turn, those OIDC IdPs are federating several other IdPs, notably GitHub, Twitter, or Google.
Now, while trying to migrate the database to 0.2.1, I get the error below. Eventually, the root cause is that I had used the same OIDC Auth Method for Auth0 to have auto-magically create users & accounts not only for a user local to Auth0, but two additional users federated by Auth0 from GitHub and Twitter, for example.
What would be the recommended ways to proceed?
I do have backups of the PostgreSQL DB from before I attempted the migration. Thus I am tempted to try and follow the procedure outlined in the error message. Even if I could not find more detailed information about it yet, and I am not a (Postgre)SQL specialist…
Maybe another, “safer” procedure could be to roll-back the Boundary binaries to 0.2.0, start the Controller again, then use the CLI and/or WebUI as admin to assert that each user has not more than one account from the same Auth Method.
root@cerberus:/var/db/postgres # uname -a FreeBSD cerberus.xxx.ch 12.1-RELEASE-p11-HBSD FreeBSD 12.1-RELEASE-p11-HBSD #0 74f1f081a1e(stable/20.7)-dirty: Fri Dec 4 13:40:15 CET 2020 root@sensey64:/usr/obj/usr/src/amd64.amd64/sys/SMP amd64 root@cerberus:/var/db/postgres # boundary version Version information: Git Revision: 3c994f66f877224fd1d75e6ce3ff4efb3aa9a0ad Version Number: 0.2.1 root@cerberus:/var/db/postgres # sudo -u boundary boundary database migrate -config /usr/local/etc/boundary.hcl Error running database migrations: schema.(Manager).RollForward: schema.(Manager).runMigrations: postgres.(Postgres).Run: migration failed, Key (auth_method_id, iam_user_id)=(amoidc_tbfMvR6iMg, u_R54WMeDyN7) is duplicated., on line 0: -- this constraint is intended to ensure that a user cannot have more than one -- account per auth_method. -- -- If this constraint causes the migrations to fail the operartor can run the -- following query to get a list of user ids which have more that one account -- within the same auth method. At that point, the operator will need to pick -- which account to keep. ---------------------------------------------------------------------------- -- with too_many_accounts(iam_user_id, acct_count) as ( -- select -- iam_user_id, -- count(*) as acct_count -- from -- auth_account -- group by auth_method_id, iam_user_id -- ) -- select -- * -- from -- too_many_accounts -- where -- acct_count > 1; alter table auth_account add constraint auth_account_auth_method_id_public_id_uq unique(auth_method_id, iam_user_id); : could not create unique index "auth_account_auth_method_id_public_id_uq": unique constraint violation: integrity violation: error #1002 root@cerberus:/var/db/postgres #