org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.

in the Status column

These scripts need to be cleaned up, consolidated, and have meaningful names.

The server cert will need a subjectAltName
https://wiki.strongswan.org/projects/strongswan/wiki/AndroidVPNClient

Hopefully we just need the sql equivalent of what rightsubnet would be in ipsec.conf.
We will likely need to edit peer_configs to not use the pool. We may edit the traffic_selectors so that the right side matches the client's subnet (if dynamic/32 doesn't work).

An example: https://strongswan.org/testing/testresults/sql/net2net-cert/

The strongswan db schema is "experimental" and "in development" and has been that way (presumably) since before foreign key constraints were a thing in mysql.

Per a ScheduledTask (Java), certs are deleted when they've used too much bandwidth.

However, active connections will remain active (until the next reauth?). So, we need a way to drop the connection(s). One option would be to ipsec down with the connection name & instance.

This occurs when there is no address with identity "0".
When a connection is created and given an address from the pool, that address is FOREVER associated with that identity. This has been reproduced by establishing connections with X different certificates, given a pool size of X-1. (You can create cert, establish connection, delete cert, repeat.)

https://hub.docker.com/r/owasp/modsecurity-crs/~/dockerfile/

crs-setup.conf

SecDefaultAction "phase:1,log,auditlog,deny"
SecDefaultAction "phase:2,log,auditlog,deny"
SecAction
"id:900000,
phase:1,
nolog,
pass,
t:none,
setvar:tx.paranoia_level=4"

/etc/modsecurity/modsecurity.conf

SecRuleEngine on

rules/REQUEST-900-EXCLUSION-RULES-BEFORE-CRS.conf

SecRule REQUEST_URI "@beginswith /addCert" "id:9999,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942440;ARGS:csr"
SecRule REQUEST_URI "@beginswith /addCert" "id:9998,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942430;ARGS:csr"
SecRule ARGS:csr "((?:[.~!@#$%^&*()={}[]|:;"'\´\’\‘`<>][^\.\~\!\@\#\$\%\^\&\*\(\)\=\{\}\[\]\|\:\;\"\'\´\’\‘\`\<\>]?){1})"
"phase:request,
t:none,t:urlDecodeUni,
block,
id:9997,
severity:'CRITICAL',
rev:'2',
ver:'OWASP_CRS/3.0.0',
maturity:'9',
accuracy:'8',
msg:'Restricted SQL Character Anomaly Detection (args): # of special characters exceeded (0)',
capture,
logdata:'Matched Data: %{TX.1} found within %{MATCHED_VAR_NAME}: %{MATCHED_VAR}',
setvar:tx.anomaly_score=+%{tx.warning_anomaly_score},
setvar:tx.sql_injection_score=+%{tx.warning_anomaly_score},
setvar:'tx.msg=%{rule.msg}',
setvar:tx.%{rule.id}-OWASP_CRS/WEB_ATTACK/RESTRICTED_SQLI_CHARS-%{matched_var_name}=%{tx.0}"
SecRule REQUEST_URI "@beginswith /" "id:9996,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942432;ARGS:_csrf"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9995,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;ARGS:password"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9994,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;ARGS:confirmPassword"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9993,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9992,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;ARGS:password"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9991,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;ARGS:confirmPassword"
SecRule REQUEST_URI "@beginswith /createaccount" "id:9990,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /login" "id:9989,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;ARGS:password"
SecRule REQUEST_URI "@beginswith /login" "id:9988,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;ARGS:password"
SecRule REQUEST_URI "@beginswith /login" "id:9987,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /login" "id:9986,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /addCert" "id:9985,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;ARGS:csr"
SecRule REQUEST_URI "@beginswith /addCert" "id:9984,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920272;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /addCert" "id:9983,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;ARGS:csr"
SecRule REQUEST_URI "@beginswith /addCert" "id:9982,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;REQUEST_BODY"
SecRule REQUEST_URI "@beginswith /addCert" "id:9981,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942431;ARGS:csr"
SecRule ARGS:csr "((?:[~!@#$%^&*()={}[]|:;"'\´\’\‘`<>][^\~\!\@\#\$\%\^\&\*\(\)\=\{\}\[\]\|\:\;\"\'\´\’\‘\`\<\>]?){1})"
"phase:request,
t:none,t:urlDecodeUni,
block,
id:9980,
severity:'WARNING',
rev:'2',
ver:'OWASP_CRS/3.0.0',
maturity:'9',
accuracy:'8',
msg:'Restricted SQL Character Anomaly Detection (args): # of special characters exceeded (6)',
capture,
logdata:'Matched Data: %{TX.1} found within %{MATCHED_VAR_NAME}: %{MATCHED_VAR}',
tag:'application-multi',
tag:'language-multi',
tag:'platform-multi',
tag:'attack-sqli',
tag:'OWASP_CRS/WEB_ATTACK/SQL_INJECTION',
tag:'WASCTC/WASC-19',
tag:'OWASP_TOP_10/A1',
tag:'OWASP_AppSensor/CIE1',
tag:'PCI/6.5.2',
tag:'paranoia-level/3',
setvar:tx.anomaly_score=+%{tx.warning_anomaly_score},
setvar:tx.sql_injection_score=+%{tx.warning_anomaly_score},
setvar:'tx.msg=%{rule.msg}',
setvar:tx.%{rule.id}-OWASP_CRS/WEB_ATTACK/RESTRICTED_SQLI_CHARS-%{matched_var_name}=%{tx.0}"
SecRule REQUEST_URI "@beginswith /addCert" "id:9979,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942460;ARGS:csr"
SecRule REQUEST_URI "@beginswith /addCert" "id:9978,phase:1,pass,nolog,ctl:ruleRemoveTargetById=942432;ARGS:csr"
SecRule REQUEST_URI "@beginswith /deleteCert" "id:9977,phase:1,pass,nolog,ctl:ruleRemoveTargetById=920273;REQUEST_BODY"

rules/RESPONSE-999-EXCLUSION-RULES-AFTER-CRS.conf

SecRuleUpdateTargetByTag "application-multi" !ARGS:password
SecRuleUpdateTargetByTag "application-multi" !ARGS:confirmPassword

that information should be available from the connections table of the ipsec db

Thus far, we've only used regtest.