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.
Thank you.
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 #