Migrate from 0.2.0 to 0.2.1: "unique constraint violation: integrity violation: error #1002"

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 #

Can you confirm – in 0.2.0 did you add accounts manually to users, then change the issuer in the same auth method, have people log in again, creating new accounts in that auth method, then add those to the same users as before?

We’re trying to figure out the best way forward and want to make sure that we’re correct in terms of how you got into this situation.


@hb9cwp Let me know when you can – it would help us to figure out the next steps if we can confirm how you got this error.


@jeff Sorry, got “distracted” by giving a first briefing internally with a short preview of Boundary (after rollback to 0.2.0 temporarily). But attacking this again now, and will follow-up asap.

@jeff Using the auth_method_id and iam_user_id from the error message above, as well as the admin WebUI while still running 0.2.0, I concluded that I have to pick either one of the two accounts which I had moved manually in the CLi to my user u_R54w… after AzureAD had auto-magically created them, one for my user rs@xxx local to AzureAD, and the second by AzureAD federating my login from GitHub as IdP, see screenshot.

So, I decided to remove the latter from the user. After having made my pick, the migrations to 0.2.1 ran successfully.

Still, I do not fully grasp this restriction, as both account appeared to co-exist happily before I had attempted to upgrade (as the other two OIDC accounts from Auth0 and Okta do). But I am still learning to walk while experimenting with Boundary and its new features…