Unique constraint violations on `package_rules` table

[jonathan-d-zhang]

Background

The new index added in https://github.com/vipyrsec/dragonfly-mainframe/pull/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: https://github.com/vipyrsec/dragonfly-mainframe/blob/fd0dd37becc5fc7105f95260efd06696d89f3fc2/src/mainframe/endpoints/package.py#L66-L88.

Suggestions

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

[BruDriguezz]

👍👍👍

[jonathan-d-zhang]

I believe this error is caused by giving the same scan out to multiple clients. Perhaps https://github.com/vipyrsec/dragonfly-mainframe/pull/238 will solve this issue.