Update Neo4j from 4.4 to 5.x latest release

[gnmahanth]

Currently we run v4.4 release of neo4j upgrade it to latest available v5.x release

neo4j deprecation reference:

1. https://neo4j.com/docs/cypher-manual/current/deprecations-additions-removals-compatibility/
2. https://neo4j.com/docs/apoc/current/deprecations-and-additions/

dependent issues:

• https://github.com/deepfence/ThreatMapper/issues/1685

along with neo4j database also update the neo4j-go-driver to v5 from v4

Steps to migrate database:

• neo4j database server cannot be updated from v4 to v5 directly
• https://neo4j.com/docs/upgrade-migration-guide/current/version-5/migration/

1. take the database dump of neo4j db when it is running v4, save the dump file
2. Clean up the database remove all dbs
3. upgrade to latest TM with neo4j v5 support
4. let TM create all new users and indexes
5. exec into neo4j container, stop neo4j and load the previously saved dump file
6. now migrate the loaded db to v5 compatible and start the database

List of Tasks:

• [x] go driver update in branch https://github.com/deepfence/ThreatMapper/tree/neo4j-v5
• [x] check and fix all cypher queries which are not compatible in v5
• [ ] try and automate saving and restoring database
• [x] document upgrade process for docker and kubernetes consoles
• [x] verify neo4j plugins can be bundled in neo4j docker image
• [ ] Display clear message in UI if console is started with out completing proper migrations from v4 to v5

[gnmahanth]

Steps to be performed while migration data for v4 to v5

Step to be performed before upgrading inside neo4j container

1. stop the neo4j

neo4j stop

2. create a directory to store the database dump

mkdir -p /backups/migrations

3. create database dump (dumping system database is optional)

neo4j-admin dump --expand-commands --database=system --to /backups/migrations/system.dump 
neo4j-admin dump --expand-commands --database=neo4j --to /backups/migrations/neo4j.dump

4. remove existing databases

rm -rf /data/databases/* /data/transactions/*

steps to be performed after upgrading on neo4j container

1. wait few minutes for neo4j to initialize and create all the necessary users and permissions
2. stop neo4j

neo4j stop

2. restore neo4j database dump

neo4j-admin database load --expand-commands neo4j --from-path=/backups/migrations/ --overwrite-destination=true

3. migrate v4 database to v5 compatible version

neo4j-admin database migrate neo4j --force-btree-indexes-to-range

5. start neo4j

neo4j start

[gnmahanth]

List of the queries need migration for v5 neo4j

1. link_cloud_resource

Mon, 19 Feb 2024 14:01:38 +0000 ERR deepfence_worker/worker.go:123 worker task link_cloud_resource, payload: 1708351298013 error="Neo4jError: Neo.ClientError.Statement.SyntaxError (Unknown function 'apoc.meta.type' (line 10, column 31 (offset: 282))\n\"\t\tWITH n, subgroup, CASE WHEN apoc.meta.type(subgroup) = \"STRING\" THEN subgroup ELSE subgroup.GroupName END as name,\"\n                               ^): skip retry for the task"
asynq: pid=1 2024/02/19 14:01:38.999677 WARN: Retry exhausted for task id=link_cloud_resource

2. posture page err

Tue, 20 Feb 2024 11:21:20 +0000 DBG search/search.go:340 search cloud node query: 
		MATCH (n:Node) WHERE  n.agent_running IN [true] WITH n.node_id AS node_id UNWIND node_id AS x
		OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s:ComplianceScan)-[:DETECTED]->(c:Compliance)
		WITH x , COUNT(c) AS total_compliance_count
		OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s:ComplianceScan)-[:DETECTED]->(c1:Compliance)
		WHERE c1.status IN $pass_status
		WITH x, CASE WHEN total_compliance_count = 0 THEN 0.0 ELSE COUNT(c1.status)*100.0/total_compliance_count END AS compliance_percentage
		CALL {
			WITH x
			OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s1:ComplianceScan)
			RETURN s1.node_id AS last_scan_id, s1.status AS last_scan_status
			ORDER BY s1.updated_at DESC LIMIT 1
		}
		CALL {WITH x MATCH (n:Node{node_id: x}) RETURN n.node_name as node_name, n.active as active, n.version as version}
		WITH x, node_name, version, compliance_percentage, last_scan_id, COALESCE(last_scan_status, '') as last_scan_status, active RETURN x as node_id, node_name, COALESCE(version, 'unknown') as version, compliance_percentage, COALESCE(last_scan_id, '') as last_scan_id, COALESCE(last_scan_status, '') as last_scan_status, active  SKIP 0 LIMIT 10
Tue, 20 Feb 2024 11:21:20 +0000 DBG search/search.go:340 search cloud node query: 
		MATCH (n:Node) WHERE  n.agent_running IN [true] WITH n.node_id AS node_id UNWIND node_id AS x
		OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s:ComplianceScan)-[:DETECTED]->(c:Compliance)
		WITH x , COUNT(c) AS total_compliance_count
		OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s:ComplianceScan)-[:DETECTED]->(c1:Compliance)
		WHERE c1.status IN $pass_status
		WITH x, CASE WHEN total_compliance_count = 0 THEN 0.0 ELSE COUNT(c1.status)*100.0/total_compliance_count END AS compliance_percentage
		CALL {
			WITH x
			OPTIONAL MATCH (n:Node{node_id: x})<-[:SCANNED]-(s1:ComplianceScan)
			RETURN s1.node_id AS last_scan_id, s1.status AS last_scan_status
			ORDER BY s1.updated_at DESC LIMIT 1
		}
		CALL {WITH x MATCH (n:Node{node_id: x}) RETURN n.node_name as node_name, n.active as active, n.version as version}
		WITH x, node_name, version, compliance_percentage, last_scan_id, COALESCE(last_scan_status, '') as last_scan_status, active RETURN x as node_id, node_name, COALESCE(version, 'unknown') as version, compliance_percentage, COALESCE(last_scan_id, '') as last_scan_id, COALESCE(last_scan_status, '') as last_scan_status, active  SKIP 0 LIMIT 100
Tue, 20 Feb 2024 11:21:20 +0000 ERR handler/search_reports.go:104 Neo4jError: Neo.ClientError.Statement.SyntaxError (Aggregation column contains implicit grouping expressions. For example, in 'RETURN n.a, n.a + n.b + count(*)' the aggregation expression 'n.a + n.b + count(*)' includes the implicit grouping key 'n.b'. It may be possible to rewrite the query by extracting these grouping/aggregation expressions into a preceding WITH clause. Illegal expression(s): total_compliance_count (line 7, column 21 (offset: 392))
"		WITH x, CASE WHEN total_compliance_count = 0 THEN 0.0 ELSE COUNT(c1.status)*100.0/total_compliance_count END AS compliance_percentage"
                     ^)
Tue, 20 Feb 2024 11:21:20 +0000 ERR handler/search_reports.go:78 Neo4jError: Neo.ClientError.Statement.SyntaxError (Aggregation column contains implicit grouping expressions. For example, in 'RETURN n.a, n.a + n.b + count(*)' the aggregation expression 'n.a + n.b + count(*)' includes the implicit grouping key 'n.b'. It may be possible to rewrite the query by extracting these grouping/aggregation expressions into a preceding WITH clause. Illegal expression(s): total_compliance_count (line 7, column 21 (offset: 392))
"		WITH x, CASE WHEN total_compliance_count = 0 THEN 0.0 ELSE COUNT(c1.status)*100.0/total_compliance_count END AS compliance_percentage"
                     ^)

3. Reports generation failure

Tue, 20 Feb 2024 10:01:06 +0000 INF reports/data.go:162 vulnerability scan info: [{ScanID:mahanth-ubuntu-agent-2-1708000444 Status:COMPLETE StatusMessage: UpdatedAt:1708001182183 CreatedAt:0 NodeID:mahanth-ubuntu-agent-2 NodeType:host SeverityCounts:map[critical:74 high:753 low:60 medium:933] NodeName:mahanth-ubuntu-agent-2}]
Tue, 20 Feb 2024 10:01:06 +0000 DBG scan/scan_reporters.go:759 query: 
		OPTIONAL MATCH (n:VulnerabilityScan{node_id:$node_id})
		RETURN n IS NOT NULL AS Exists
Tue, 20 Feb 2024 10:01:06 +0000 DBG scan/scan_reporters.go:803 query: 
		MATCH (s:VulnerabilityScan{node_id: $scan_id}) -[r:DETECTED]-> (d)
		OPTIONAL MATCH (d) -[:IS]-> (e)
		OPTIONAL MATCH (s) -[:SCANNED]-> (n)
		OPTIONAL MATCH (ca:ContainerImage{node_id: n.docker_image_id}) -[:ALIAS] ->(t) -[m:MASKED]-> (d)
		WITH d, n, e, r, m
		OPTIONAL MATCH (cb:ContainerImage{node_id: n.docker_image_id}) -[:IS] ->(is) -[mis:MASKED]-> (d)
		WITH apoc.map.merge( e{.*},
		d{.*, masked: coalesce(d.masked or r.masked or e.masked 
			or head(collect(m.masked)) or head(collect(mis.masked)), false),
		name: coalesce(e.name, d.name, '')}) as d RETURN d 
Tue, 20 Feb 2024 10:01:06 +0000 ERR reports/data.go:176 failed to get results for mahanth-ubuntu-agent-2-1708000444 error="Neo4jError: Neo.ClientError.Statement.SyntaxError (Aggregation column contains implicit grouping expressions. For example, in 'RETURN n.a, n.a + n.b + count(*)' the aggregation expression 'n.a + n.b + count(*)' includes the implicit grouping key 'n.b'. It may be possible to rewrite the query by extracting these grouping/aggregation expressions into a preceding WITH clause. Illegal expression(s): e, e.masked, e.name, r.masked, d.masked, d.name, d (line 8, column 24 (offset: 385))\n\"\t\tWITH apoc.map.merge( e{.*},\"\n                        ^)"

4. load scan results failure

{
    "message": "Neo4jError: Neo.ClientError.Statement.SyntaxError (Aggregation column contains implicit grouping expressions. For example, in 'RETURN n.a, n.a + n.b + count(*)' the aggregation expression 'n.a + n.b + count(*)' includes the implicit grouping key 'n.b'. It may be possible to rewrite the query by extracting these grouping/aggregation expressions into a preceding WITH clause. Illegal expression(s): e, e.masked, e.name, r.masked, d.masked, d.name, d (line 8, column 24 (offset: 385))\n\"\t\tWITH apoc.map.merge( e{.*},\"\n                        ^)",
    "error_fields": null,
    "error_index": null
}

5. Integration error

[gnmahanth]

New version of neo4j have apoc bundled within docker container, when enabled it copies the plugin to /plugins folder

reference: https://neo4j.com/docs/apoc/current/installation/#apoc

[gnmahanth]

Upgrade steps by console deployment type

prerequisite:

1. download pre-upgrade-to-v5.sh script to current directory
2. make pre-upgrade-to-v5.sh executable

chmod +x pre-upgrade-to-v5.sh

for docker:

1. execute below command before upgrading to new release

docker cp pre-upgrade-to-v5.sh deepfence-neo4j:/startup
docker exec deepfence-neo4j /startup/pre-upgrade-to-v5.sh

4. upgrade to new release, wait for scheduler to complete initial neo4j setup, then execute below command

docker exec deepfence-neo4j /startup/post-upgrade-to-v5.sh

for kubernetes:

1. set variable the below variables

export NAMESPACE=default
export PODNAME=`kubectl get pods -n $NAMESPACE --no-headers -o custom-columns=":metadata.name" | grep neo4j`

2. execute below command before upgrading to new release

kubectl cp -n $NAMESPACE pre-upgrade-to-v5.sh $PODNAME:/startup 
kubectl exec -it -n $NAMESPACE $PODNAME -- /startup/pre-upgrade-to-v5.sh

5. upgrade to new release, wait for scheduler to complete initial neo4j setup, then execute below command

kubectl exec -it -n $NAMESPACE $PODNAME -- /startup/post-upgrade-to-v5.sh