SonarQube Server / Developer Edition 9.9.4.87374 installed from zip on a Linux host.
We’ve been using LDAP authentication and authorization for years. Recently, our users received a mass update of email addresses on the LDAP server side (which are Active Directory servers BTW).
What I would expect in this case is SonarQube launching an LDAP query for the user on logon and updating the locally stored email address.
What happens instead:
A new user is created alongside the existing one, with an account name john_doe1234 and the new email address.
The login also fails with the following debug message in web.log:
Since the affected users are controlled by an external identity provider, I cannot fix their email addresses via the user administration GUI (/admin/users URL path). Email field is disabled and a “You cannot update the name and email of this user, as it is controlled by an external identity provider.” message is displayed.
Is this a genuine bug or did I miss something important here?
How do I make SonarQube refresh the email addresses of my users? What am I supposed to do with the duplicate john_doe1234 users?
Your version is past EOL. You should upgrade to either the latest version or the current LTA (long-term active version) at your earliest convenience. Your upgrade path is:
If you have questions about upgrading, feel free to open a new thread for that here.
Regarding your question, we simply never accounted for the possibility of a mass email change. We generally say to keep your hands out of the database, but direct manipulation may be your only option.
Also, a change in the data fetched from an external authentication provider seems like a very basic use case to me. I would not consider an email address change an arcane act that happens once in a decade and just for a few select users among millions…
We generally say to keep your hands out of the database, but direct manipulation may be your only option.
Can you be a bit more specific? Which table(s) and field(s) are we talking?
You’re right; that’s stale. Things have changed a bit since then. IIRC, there was a recurring problem with conflicting email addresses, so we started adding the random number to the login to get past it.
IMHO this is a design failure. You had a handful of other, more sane options to solve this, such as:
Allow setting the same email address for multiple users. Why on Earth should emails be unique in a users table?
(I did encounter the conflicting email addresses case a few years ago, too. I had two users to the same SQ instance in parallel: one with admin privileges and one with regular privileges for simulation purposes. Both synchronized from an LDAP source, both having the same email address. I was wondering back then: what good comes from forcing unique email addresses on users?)
Introduce a property for enabling/disabling user data updates from external sources.
No more email refresh from external sources? OK, but then allow modifying name and email from the GUI. The warning message SonarQube shows now (“You cannot update the name and email of this user, as it is controlled by an external identity provider.”) is a straight-faced lie. The external identity provider does not control the name and email of users anymore.
As for direct database manipulation: I did that and it does not seem to work.
I removed the duplicate john_doe1234 user created by SonarQube. I updated the original john_doe’s email address to john.doe@newdomain.com. I restarted both my DB and SonarQube to force clearing any potential cached user data. The login still fails and the following web.log entry is created:
The full web.log currently weighs 562 MB (and counting).
Why:
When I started debugging this problem, I set the sonar.log.level.web property to DEBUG, so that I can see debug messages such as the one in my first and last comment.
The version upgrade happened today and these logs contain a lot of entries related to the upgrade process itself.
Previously (before the upgrade) SQ produced ~170-190 MBs of web.log per day. This is still too much volume to redact or share IMHO.
I can capture and share parts of the log that may refect details of specific events, such as a service startup or a login attempt.
We have 5 LDAP servers, all 5 of them are successfully tested on startup. Later on, LDAPS queries throw several “No subject alternative DNS name matching addomain.local found.” exceptions, because 4 out of 5 LDAP servers have incorrect TLS certificate SANs. I consider this to be noise and not related to the root cause. Eventually, SonarQube finds and succesfully queries the single LDAP server that has a correct TLS certificate (mmm.addomain.local), see the last 4 log entries.
Argh. OK, now I get it, thanks for the clarification.
I still think that:
Enforcing unique emails in the users table is bad design.
Not refreshing email addresses from an external provider and also restricting the user from modifing the same email addresses from the GUI is bad design.
On startup, SQ does check all the LDAP servers configured, and successfully validates all of them. Minutes later, the actual LDAP queries fail with "No subject alternative DNS name matching addomain.local found.”. It looks like the actual LDAP queries do a different sort of SSL handshake than the tests than run on startup - this did not help in my case, neither.
But at least now I know how and why it fails, so at least I can work around this bad design.
OK, let’s see how I finally managed to fix things.
Fair warning to the reader: my case may differ from your case significantly. Do NOT blindly apply the below steps without understanding what is done and why. Always start with a backup you can easily roll back to if anything goes south.
Stopped the sonarqube service.
First I had to fix the multi-server LDAP setup. (As it turned out, this sort of setup is not meant to work as a failover cluster, see above.) I practically reverted back to a single LDAP server setup.
2.1 First off, I edited the sonar.properties file. I started with multiple entries for most ldap properties such as:
I had to leave only one property for every such group of properties and I had to remove the middle part of the property key labeling the server. As in:
ldap.url=ldaps://server-one.foo.bar:636
2.2 I had to fix the external_identity field values of the users table. It looks like when you use a multi-server LDAP configuration, these fields designate the LDAP server used for the user’s first logon, as in: ‘LDAP_server1’ or ‘LDAP_server2’. I also noticed that if you use a single-server LDAP setup, then the external_identity_field is always ‘sonarqube’. (Local and external users can be told apart based on the user_local boolean field.)
UPDATE users SET external_identity_provider = 'sonarqube' WHERE external_identity_provider LIKE 'LDAP_%';
The second problem I had to fix was the email addresses, which are only modifiable via SQL UPDATE statements.
3.1 I removed the jon_doe1234 user duplicates SQ created earlier. Since we do not have a lot of users, I handpicked them, one user at a time.
3.2 Then I updated the old email domain to the new email domain:
UPDATE users SET email = split_part(email, '@', 1) || '@newdomain.hu' WHERE email LIKE '%@olddomain.hu';
You may want to write a more specific WHERE clause to update a smaller subset of users.