REST API -- Clearing 'AllowedIPs' reset after service restart

Openfire Plugins
1

Openfire 3.10.0 on Windows, using external Oracle database and REST API plugin.

If a list of IP Addresses is added to the REST API’s AllowedIPs and then saved, a new property ‘plugin.restapi.allowedIPs’ is created in the database. Later, through the Admin Console, if the same field is cleared it appears as though all IP addresses are allowed. However, after restart of the Openfire service, the original list of IPs will return.

Root cause:

It seems the Admin Console tries to set the ‘plugin.restapi.allowedIPs’ to ‘null’. However, the database schema prevents a null value for this column. So the value is left unchanged. Once the service restarts the original non-null value is used.

java.sql.SQLException: ORA-01407: cannot update (“OPENFIRE”.“OFPROPERTY”.“PROPVALUE”) to NULL

Workaround:

Delete the ‘plugin.restapi.allowedIPs’ property via the Admin Console’s System Properties page.

2

I can not reproduce it with Openfire 3.10. Which version of Openfire and REST API do you use?

What I did:

  • Set some IPs to allowed IP field in REST API section. -> SAVE

  • Delete IPs from the field. -> Save

  • Restart Openfire.

Result: IPs are deleted and not recovered.

3

Are you using the embedded database or an Oracle database? I believe Oracle treats an empty string ("") as null. And the PROPVALUE column of the OFPROPERTY table is marked as non-nullable.

FWIW, this happens in 3.9.3 as well as 3.10.0.

4

I use MySQL

5

With MySQL after you clear the allowedIPs field, does the plugin.restapi.allowedIPs attribute still exists, but contain an empty string or no obvious value?

6

Property is there and contains an empty string.

7

That, more or less, confirms what I see. If I clear the allowedIPs field and press SAVE the exception appears in the error log and the value in the database remains unchanged. I have to guess an “in-memory” or “cached” value must be used until the service restarts. As the plugin will function as if the value were empty until service restart occurs.

Frankly, this might an issue for any number of plugins/properties when an Oracle database is used. Let me run a quick test.

8

As I thought, this is a larger issue than just with the REST API.

Any time a property’s value is set to an empty string and an Oracle database is used, the value will not be updated in the database. As such, the original value will be used after a service restart (or whatever else causes Openfire to re-read its property values from the database). This same issue may affect other tables in Openfire in which an empty string is placed into a non-nullable column and an Oracle database is used.

Repro Steps:

  1. Install Openfire and configure it to use an external Oracle database
  2. Goto the Admin Console’s ‘System Properties’ page
  3. Add a property ‘aaa.test.property’ with some value ‘sample value goes here’ and press ‘Save Property’
  4. The OFPROPERTY table’s entry for ‘aaa.test.property’ will show the above value
  5. In the Admin Console, edit the same property and clear the entire value field and press “Save Property”
  6. The Admin Console will now show an “empty” value for the ‘aaa.test.property’
  7. An exception will appear in the error.log at the time Save Property is pressed in Step 5
  8. “java.sql.SQLException: ORA-01407: cannot update (“OPENFIRE”.“OFPROPERTY”.“PROPVALUE”) to NULL”
  9. The OFPROPERTY table’s entry for ‘aaa.test.property’ will show the value entered in Step 3
  10. Restart the Openfire service
  11. Open the Admin Console’s System Properties page, note the value for ‘aaa.test.property’ is now the value entered in Step 3.
  12. Cry because you chose to use Orcale as a backing store for Openfire.