custom_advisories

27 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: package_pattern, package_manager, version_match_strategy, advisory_type, description

CREATE TABLE custom_advisories (
  -- package_pattern defines an exact package name, or a pattern that should
  -- match a package name, indicating which package the advisory is for. this
  -- can either be an exact match, such as:
  --   `dmd.tanna.dev`
  -- or it can include a `*` character to indicate a wildcard such as:
  --   `*/oapi-codegen`
  --   `@my-org/*'
  --   `*tanna*`
  --   `*tan*na*`
  --
  -- Foreign keys:
  -- - `renovate.package_name`
  -- - `sboms.package_name`
  package_pattern TEXT NOT NULL,
  -- package_manager indicates the package manager that the given `package_pattern` should match.
  --
  -- Based on which datasource(s) (https://dmd.tanna.dev/concepts/datasource/)
  -- you are using, this will be a different value:
  -- - for Renovate data, must exactly match `renovate.package_manager`.
  --   Note that there may be multiple `package_managers`, for instance `maven`
  --   and `gradle`, which would require two rows.
  -- - for Software Bill of Materials (SBOM) data, must exactly match
  -- `sboms.package_type`
  --
  -- If you are using multiple datasources, you will need to have one row per `package_manager`.
  --
  -- Foreign keys:
  -- - `renovate.package_manager`
  -- - `sboms.package_type`
  package_manager TEXT NOT NULL,
  -- version defines version(s) that this advisory relates to.
  -- If NULL, any instances of this package (at any version) will be flagged.
  -- If non-NULL, the `version_match_strategy` will be taken into account.
  --
  -- Foreign keys:
  -- - `renovate.current_version`
  -- - `sboms.current_version`
  version TEXT,
  -- version_match_strategy defines how the advisory's `version` column gets
  -- matched against the given dependency.
  -- If NULL, `version_match_strategy` will be treated as if it were set to `ANY`
  -- If non-NULL, will perform the corresponding match type, which are documented below.
  --
  -- NOTE: that this is performed with a lexicographical match, which is NOT
  -- likely to be what you are expecting to perform version constraint matching
  -- For example:
  --   Performing a `GREATER_THAN` v1.10 would result in:
  --     v1.2.3
  --     v1.20.3
  --   Which does NOT match the expectation that you would only see `v1.20.3`.
  --
  -- If you would like more control over advisory data, and to perform true
  -- version number calculations, it's worth writing Policies using Open Policy
  -- Agent (https://dmd.tanna.dev/cookbooks/custom-advisories-opa/)
  --
  -- If `version` is NULL, this column is ignored.
  version_match_strategy TEXT
    CHECK (
      version_match_strategy IN (
        -- any packages that match `package_pattern` and `package_manager` will
        -- be classed as an Advisory
        'ANY',
        -- any packages that match `package_pattern` and `package_manager`, and
        -- has a `current_version` which is exactly equal to `version` will be
        -- classed as an Advisory
        'EQUALS',
        -- any packages that match `package_pattern` and `package_manager`, and
        -- has a `version < current_version` (lexicographically compared) will
        -- be classed as an Advisory
        'LESS_THAN',
        -- any packages that match `package_pattern` and `package_manager`, and
        -- has a `version <= current_version` (lexicographically compared) will
        -- be classed as an Advisory
        'LESS_EQUAL',
        -- any packages that match `package_pattern` and `package_manager`, and
        -- has a `version > current_version` (lexicographically compared) will
        -- be classed as an Advisory
        'GREATER_THAN',
        -- any packages that match `package_pattern` and `package_manager`, and
        -- has a `version >= current_version` (lexicographically compared) will
        -- be classed as an Advisory
        'GREATER_EQUAL'
      )
    ),
  -- level defines the severity of the Advisory. This will be
  -- organisation-specific in terms of what you deem most critical, but an
  -- example of what this could look like is:
  --
  --   ERROR: "Use of AGPL-3.0 licensed dependencies anywhere is a high-severity"
  --   WARN:  "Using a dependency that hasn't been updated in 1 year should be avoided"
  level TEXT NOT NULL
    DEFAULT 'ERROR'
    CHECK (
      level IN (
        'ERROR',
        'WARN'
      )
    ),
  -- advisory_type defines the type of Advisory
  -- (https://dmd.tanna.dev/concepts/advisory/) that this dependency will
  -- flagged as.
  --
  -- NOTE that this field is an exact match for the
  -- `custom_advisories.advisory_type` column
  advisory_type TEXT NOT NULL
    CHECK (
      advisory_type IN (
        -- the dependency is deprecated, and should ideally be replaced
        'DEPRECATED',
        -- the dependency is no longer maintained
        'UNMAINTAINED',
        -- there is a security issue with this dependency
        'SECURITY',
        -- there is organisational policy that recommends awareness of the use
        -- of this dependency
        'POLICY',
        -- there is no other `advisory_type` that makes sense for this type. If
        -- you feel there should be, please raise an issue on the issue tracker
        -- (https://gitlab.com/tanna.dev/dependency-management-data/-/issues)
        'OTHER'
      )
    ),
  -- description is a human-readable explanation of why this advisory is
  -- being flagged. The contents will be shown verbatim to a user, and will
  -- not be interpreted as markup. This can be as long and detailed as you
  -- wish, and is recommended to include links to (internal) documentation
  -- around the finding, any known remediation actions, and communication
  -- channels to reach out to for information.
  description TEXT NOT NULL,

  UNIQUE (package_pattern, package_manager, version, version_match_strategy, advisory_type, level, description) ON CONFLICT REPLACE
);