Unique constraint violations on package_rules table

[jonathan-d-zhang]

Background

The new index added in #268 is causing IntegrityErrors to be raised in the PUT /package endpoint.

Example error:

(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "package_rules_pkey"
DETAIL:  Key (scan_id, rule_id)=(c06d685a-1e22-4385-a050-09a314f31786, a002b47e-3e2b-4ab3-8c1a-115558a59e3d) already exists.

[SQL: INSERT INTO package_rules (scan_id, rule_id) VALUES (%(scan_id)s::UUID, %(rule_id)s::UUID)]
[parameters: {'scan_id': UUID('c06d685a-1e22-4385-a050-09a314f31786'), 'rule_id': UUID('a002b47e-3e2b-4ab3-8c1a-115558a59e3d')}]

What to do

The integrity error is intentional, in that we should be preventing duplicate records from being inserted into this table, but we should be able to handle the error and return a 400.

The error is being raised from this block:

dragonfly-mainframe/src/mainframe/endpoints/package.py

Lines 66 to 88 in fd0dd37

	with session, session.begin():
	if isinstance(result, PackageScanResultFail):
	scan.status = Status.FAILED
	scan.fail_reason = result.reason
	session.commit()
	return
	scan.status = Status.FINISHED
	scan.finished_at = dt.datetime.now(dt.timezone.utc)
	scan.inspector_url = result.inspector_url
	scan.score = result.score
	scan.finished_by = auth.subject
	scan.commit_hash = result.commit
	# These are the rules that already have an entry in the database
	rules = session.scalars(select(Rule).where(Rule.name.in_(result.rules_matched))).all()
	rule_names = {rule.name for rule in rules}
	scan.rules.extend(rules)
	# These are the rules that had to be created
	new_rules = [Rule(name=rule_name) for rule_name in result.rules_matched if rule_name not in rule_names]
	scan.rules.extend(new_rules)

.

Suggestions

• Probably want to revisit how we're dealing with already existing rules.

[BruRdgz]

👍👍👍

[jonathan-d-zhang]

I believe this error is caused by giving the same scan out to multiple clients. Perhaps #238 will solve this issue.