obrienben
commented
2 years ago A few things to check
- Did you run the db scripts unchanged?
- Can you see the usr_wct user setup in MariaDB?
@hannakoppelaar do you know if v10.3.31 Maria DB should be fine?
Closed mbreemhaar closed 2 years ago
obrienben
commented
2 years ago A few things to check
@hannakoppelaar do you know if v10.3.31 Maria DB should be fine?
mbreemhaar
commented
2 years ago Thank you for your reply.
I did run the scripts unchanged as described in the system administrator guide. The only thing I changed afterwards is the password of the 'usr_wct' user. Which I also changed in 'application.properties' of the webapp. The user is set up properly and I can successfully log in to MariaDB with usr_wct via the CLI.
MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+---------+-----------+-------------+
| User | Host | plugin |
+---------+-----------+-------------+
| root | localhost | unix_socket |
| usr_wct | localhost | |
+---------+-----------+-------------+According to the system administrator guide, MariaDB 10.0.36 is officially supported by the WCT, but since this version of MariaDB is no longer supported, our IT department does not want to run it on their servers. Hence I'm using a newer version.
hannakoppelaar
commented
2 years ago I don't think it's an issue with this particular version of MariaDB, 10.3.31 should be okay. @mbreemhaar Did you change the schema.password attribute in application-local+mysql.properties?
mbreemhaar
commented
2 years ago Yes, I did. That shouldn't be the problem.
hannakoppelaar
commented
2 years ago I did notice that on my system the 'plugin' attribute has the value 'mysql_native_password' for all users, whereas in your case it's empty. This might mean that the database is using a different algorithm to compute the password hash when you set the password.
Maybe you could try:
alter user 'usr_wct'@'localhost' identified with mysql_native_password using password('yourpasswordhere') );
To force MariaDB to use mysql_native_password?
mbreemhaar
commented
2 years ago I tried that, but no luck :(
hannakoppelaar
commented
2 years ago That's really odd :-/ And are you able to log in using mysql -u usr_wct -p -h127.0.0.1?
mbreemhaar
commented
2 years ago Yes, that works just fine.
hannakoppelaar
commented
2 years ago Okay, weird...
I have a few questions, and as you can see, my shots are getting longer ;)
mbreemhaar
commented
2 years ago We're using OpenJDK 1.8.0, so that should not be a problem as it is officially supported according to the WCT documentation.
The grants look fine to me:
+----------------------------------------------------------------------------------------------------------------+
| Grants for usr_wct@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `usr_wct`@`localhost` IDENTIFIED BY PASSWORD '*8679218BCD5D0EE6A38EFEC02BBC59593EE54CCA' |
| GRANT ALL PRIVILEGES ON `DB_WCT`.* TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PO_EXCLUSION_URI` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ARC_HARVEST_FILE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ARC_HARVEST_RESOURCE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PO_H3_BLOCK_URL` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ROLE_PRIVILEGE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`BANDWIDTH_RESTRICTIONS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SITE_AUTH_AGENCY` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SEED` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SEED_PERMISSION` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ANNOTATIONS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `DB_WCT`.`HARVEST_STATUS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PERMISSION_EXCLUSION` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `DB_WCT`.`ID_GENERATOR` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PERMISSION_TEMPLATE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`HEATMAP_CONFIG` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`GROUP_MEMBER` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`URL_PERMISSION_MAPPING` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PROFILE_CREDENTIALS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`TARGET_INSTANCE_ORIG_SEED` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`HR_MODIFICATION_NOTE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`HARVEST_RESOURCE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`AUTHORISING_AGENT` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`FLAG` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PROFILE_BASIC_CREDENTIALS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`DUBLIN_CORE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PO_H3_INCLUDE_URL` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`WCTUSER` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ABSTRACT_TARGET_SCHEDULE_VIEW` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, DELETE ON `DB_WCT`.`NOTIFICATION` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`TARGET` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SIP_PART_ELEMENT` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PROFILE_OVERRIDES` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ABSTRACT_TARGET_GROUPTYPE_VIEW` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT ON `DB_WCT`.`WCTAUDIT` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SITE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`URL_PATTERN` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`INDICATOR` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`WCT_LOGON_DURATION` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ARC_HARVEST_RESULT` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`TARGET_INSTANCE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`ABSTRACT_TARGET` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`URL_PERMISSION_MAPPING_VIEW` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PROFILE_FORM_CREDENTIALS` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`TARGET_GROUP` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`INDICATOR_CRITERIA` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PERMISSION` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PO_INCLUSION_URI` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`HARVEST_RESULT` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`TASK` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PERMISSION_URLPATTERN` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`REJECTION_REASON` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`USER_ROLE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SCHEDULE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`WCTROLE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`SEED_HISTORY` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`PROFILE` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`AGENCY` TO `usr_wct`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `DB_WCT`.`INDICATOR_REPORT_LINE` TO `usr_wct`@`localhost` |
+----------------------------------------------------------------------------------------------------------------+I'm not sure about the additional hardening, but I asked our system administrator and I will let you know as soon as I get his reply.
mbreemhaar
commented
2 years ago We do run App Armor on our server. I disabled it to see if it would make a difference, but no luck :(
obrienben
commented
2 years ago I would setup the db users again from scratch (maybe it's easier to just blow away the entire db and start again), and leave usr_wct with it's default pw and test again. If that works, then we know to focus on troubleshooting the password change
mbreemhaar
commented
2 years ago @obrienben Thank you so much! With the default password everything seems to work fine. I will try to find out what went wrong and let you know as soon as I have a solution. If you have any idea what the problem might be, let me know. :-)
mbreemhaar
commented
2 years ago I tested some things and I found out that only the default password seems to work. Then, I tried changing the password in application.properties but leaving the default in MariaDB. The webapp still works fine after that, so it looks like the WCT is not looking at my the schema.password property in application.properties.
I double checked that other settings in application.properties do work fine by changing the port for the webapp, and after restarting the WCT it was indeed running on a different port.
So to me it looks like the password is somehow hardcoded in the webapp. Could that be the problem?
hannakoppelaar
commented
2 years ago No, the password is not hard-coded. But I think I'm beginning to see what might be going on: you say you have a schema.password in application.properties. I believe this property is being overridden by the one in application-local+mysql.properties, which is where all database properties are located.
You could either add an application-local+mysql.properties (with your own schema.password) in the working directory of the java command that is used to run webapp, or change schema.password in the application-local+mysql.properties inside the war file.
I hope that works.
mbreemhaar
commented
2 years ago That was indeed the problem! The password setting was being overridden by the local+mysql profile because application.properties by default contains this line: spring.profiles.active=local+mysql. I removed that line so it does not use that profile, and now everything works fine. Thanks a lot!
When I try to start the webapp, this is what I see in the log. The database username and password password set correctly in application.properties. I'm using MariaDB 10.3.31 on Ubuntu 20.04.3. As far as I'm aware, this should work. What could be going wrong?