Update DB schema to store target domains under domains column

[jvehent]

I propose that we change the DB schema to store all domains from the subject and subjectaltname x509 fields under a single domains columns, thus simplifying queries that want to look for both values (eg. WHERE subject ~ 'mozilla' OR subjectaltname ~ 'mozilla').

The subject column which now contains the CN value only should be modified to contain the whole X509 subject line: CN, O, OU, C, ...

[0xdiba]

I totally agree with both.
Probably the same thing must be done to the issuer column.

Should we make these fields jsonb fields for consistency or
go with different fields for all of them ? ( subject_cn, subject_o etc... )

[jvehent]

A different column for each seems overkill, but better in line with how databases are supposed to work.
jsonb would allow us to query inside without require multiple columns, I like that options (select subject->>'CN').
Or we can just store the string: C=US, ST=California, L=Mountain View, O=Google Inc, CN=*.google.com

Whichever you think is best.

[jvehent]

Another side effect of the current code is that a number of CA certs don't have a CN, and their subject columns are empty in the database. For example:

observatory=> select count(id) from certificates where is_ca='t' and subject='';
 count 
-------
    68

[0xdiba]

Yes a lot of CAs have an empty CN.
This will be solved by transforming the subject and issuer columns to type jsonb and storing all the fields that identify the subject or issuer ( CN, C , O ...).

[0xdiba]

Isn't this solved with the merging of #77 ?

[jvehent]

Yep. 👍