As part of #427 we've added documentation, so we should also reference
it in the SQL.

Jamie Tanna authored 1 year ago and Jamie Tanna committed 1 year ago

This unfortunately requires a lot of plumbing, due to the way that we
have many fields in table, as well as want a hierarchical object.

Closes #289.

Jamie Tanna authored 1 year ago and Jamie Tanna committed 1 year ago

Similar to the `policy_violations` table, this introduces a single view
of all Advisories data that affects the dependencies in the database.

This makes it simpler to query all package advisories by "simply"
querying the `advisories` table, which includes all relevant information
for package advisories.

This allows us to remove a lot of duplication in our SQL queries for
looking up advisories data, making sure that it's a much simpler process
for querying data.

We can still directly query the tables as before - following the example
from `RetrievePackageAdvisoriesFromSeparateTables` - if we need to.

We can also remove the references in `policy_violations` that indicates
that `policy_violations` is better than `advisories`, as it no longer
is!

Because we want to make it easier to surface the `eol_from` and
`supported_until` dates, too, we should add them as nullable fields to
`advisories` - and defaulting it as `NULL` for tables that don't surface
the data.

Closes #414.

Jamie Tanna authored 1 year ago and Jamie Tanna committed 1 year ago

BREAKING CHANGE:

For some time, I've regretted the naming choice, as it can be a little
confusing because the `advisories` table doesn't include all the
advisories present in the dependency trees.

This now makes it more explicit by using the new name
`custom_advisories`.

To allow us to more appropriately target organisational policies around
i.e. whether a repository is public, used for sensitive purposes, or
otherwise, we can now consume the data available in
`repository_metadata` with our Policies.

Instead of this being an untyped map, we can instead construct a
well-formed and explicit type for our input to Policy Evaluations.

At least for package data, which is more important than the AWS
infrastructure.

This hopefully will make it considerably easier to work with DMD's data
model.

This flags the usage of things that may be seen as "foreign keys", even
though there's no official constraint in place.

Closes #17.

As part of #277, we want to introduce the ability to `warn` in policies,
as well as `deny`.

This requires we introduce the new level, and make it possible to
retrieve the `warn` rule out of policies.

We can also refactor the key logic for evaluating policies' evaluation
results into a helper method, simplifying the work that needs to be done
and making it clearer in what cases something happens.

Closes #277.

As part of #277, we want to introduce the ability to `warn` in policies,
as well as `deny`.

This is a good prefactor, as it's a rather sizeable change, and requires
we:

- wire in the level to queries
- require that it's a single value, `ERROR`
- require that it is included in making a violation unique

In queries to report licensing data, we should prioritise the data
inserted into `external_licenses`, whether it's parsed by DMD, or has
provided by the user, as it is assumed that `external_licenses` is more
reliable.

We need to handle the cases where one or both tables have the data,
preferring `external_licenses`, as well as avoid returning any data when
both tables are absent.

This also amends how we retrieve data for policy evaluations, where we
need to make sure we add a per-dependency `group by`s as we're now doing
a `group_concat`, and otherwise we'll get invalid results. We want to
make sure the lack of licenses does not cause any issues with lookups,
as a dependency is still valid if no licensing data is made available.

As part of #282, it'd be useful to be able to provide policy
requirements based on whether a given dependency has a license that
isn't organisationally allowed.

To future-proof this, we can make sure it's an array of `licences`, even
though right now we only have a single entry.

This requires we `left join`, in the case that there's no license found.

Additionally, document how to use it.

Closes #282.

Now we've produced policy violations in #258 and #268, we should also
make them easily queryable.

We can also expose this in a new report, `policy-violations`, which
allows us to view these as-is, which may be important for certain users
interacting with DMD.

As we want to allow filtering on the CLI and the web, we should allow
tweaking the parameters, requiring we have a separate query for
performing `Like`s.

This also provides the wiring for both the CLI's reporting, and for
`dmd-web`.

Closes #275.

As well as performing ad-hoc evaluations on the command-line with
`dmd policy evaluate`, we should also make it possible to store the
state in the DB.

This introduces a new command-line call, `dmd db generate
policy-violations`, which allows us to process each policy file in the
specified directory.

This introduces the new `policy_violations` table, instead of reusing
`advisories`, as the `policy_violations` can have more detail than
`advisories`. This was chosen to avoid trying to make `advisories` too
complicated with lookups, and instead focus on a separate
fit-for-purpose model.

As this introduces a table, we need to provide the plumbing for a new
`Repository`. We'll implement the anonymisation functionality in #283,
as it needs some thinking before implementation.

Closes #268.

Jamie Tanna authored 1 year ago and Jamie Tanna committed 1 year ago

As part of #258, we can introduce the `policy evaluate` command to give
an indication of the impact of a given Policy being introduced.

This requires we:

- list all the repos and packages (so we can perform per-repo tuning of
  policies if needed) from Renovate and SBOM datasources
- evaluate the policy, across many Goroutines due to the expected size
  of the returned list of dependencies

Although the scope of #273 was for this to be a `--dry-run` flag, it
makes more sense to be by default.

Closes #273.