search

mitreid-connect / OpenID-Connect-Java-Spring-Server

An OpenID Connect reference implementation in Java on the Spring platform.
Other
1.48k stars 765 forks source link

Missing UNIQUE Constraints on USER_INFO #1275

Open bodewig opened 7 years ago

bodewig commented 7 years ago

I stumbled upon this when creating test data for Postgres. Repeated deployments led to the user_info table growing as no conflicts were raised for duplicated data.

JpaUserInfoRepository expects both the preferred_username and email columns to be unique, but this is not enforced by the database schema. Locally I've added a unique constrained for preferred_username as this was enough as a quick fix.

We probably want preferred_username to be unique, but I'm not sure about the email address (otherwise I would have created a PR instead). Is it really supposed to be unique or is JpaUserInfoRepository wrong?

jricher commented 7 years ago

email_address is used for lookup as part of the discovery services, so it's assumed to be unique in the system, which it has been on all deployments I've been involved with so far. Having constraints on both of those across all the database flavors is probably a good idea.