make "qualifiers" not null in "vulnerabilities_package"

[sify21]

Currently vulnerabilities_package is defined as

                                      Table "public.vulnerabilities_package"
   Column   |          Type          | Collation | Nullable |                       Default                       
------------+------------------------+-----------+----------+-----------------------------------------------------
 id         | integer                |           | not null | nextval('vulnerabilities_package_id_seq'::regclass)
 type       | character varying(16)  |           | not null | 
 namespace  | character varying(255) |           | not null | 
 name       | character varying(100) |           | not null | 
 version    | character varying(100) |           | not null | 
 subpath    | character varying(200) |           | not null | 
 qualifiers | jsonb                  |           |          | 
Indexes:
    "vulnerabilities_package_pkey" PRIMARY KEY, btree (id)
    "vulnerabilities_package_name_namespace_type_vers_4f1568e6_uniq" UNIQUE CONSTRAINT, btree (name, namespace, type, version, qualifiers, subpath)
Referenced by:
    TABLE "vulnerabilities_packagerelatedvulnerability" CONSTRAINT "vulnerabilities_pack_package_id_65b7b522_fk_vulnerabi" FOREIGN KEY (package_id) REFERENCES vulnerabilities_package(id) DEFERRABLE INITIALLY DEFERRED

qualifiers can be null, so the unique constraint vulnerabilities_package_name_namespace_type_vers_4f1568e6_uniq doesn't stop from inserting multiple rows with qualifiers being null and the other columns being identical, which allows duplicate packages in this table. See PostgreSQL multi-column unique constraint and NULL values for reference.

Another possible solution is to make qualifiers not null, and use '{}'::jsonb as empty value.

[sbs2001]

@sify21 at the application level we have default value of {} for the qualfiers. See https://github.com/nexB/vulnerablecode/blob/7c60c0256c5f29abf8d0b09a40a3c3e697b0455a/vulnerabilities/models.py#L112

Eg.

In [1]: from vulnerabilities.models import Package                                                                                                                                                                   

In [2]: p1 = Package.objects.create(name="a",version="b",type="c")                                                                                                                                                   

In [3]: p1                                                                                                                                                                                                           
Out[3]: <Package: pkg:c/a@b>

In [4]: p1.qualifiers                                                                                                                                                                                                
Out[4]: {}

So this is unlikely to be a problem.

FWIW we also have additional safety guards at https://github.com/nexB/vulnerablecode/blob/7c60c0256c5f29abf8d0b09a40a3c3e697b0455a/vulnerabilities/import_runner.py#L281

So the application in practice never writes null values to the DB

[sify21]

Oh thanks for the info, I didn't check out the code.

[sify21]

wait, then why not make this field not null? In case someone use other methods to insert into this table, e.g. an async importer with asyncpg

[sbs2001]

@sify21, right , it makes sense to add the contraint at the db level. I just patched the thing via #313 . That should fix the thing