l3montree-dev
organization wide dependency search
Scenario
A Specific Package gets Compromised (such as xz-util), now you want to check if any repository in your organization even uses this package and if so, what version is it on.
Devguard could now offer a search bar in the Organization Tab and you can quickly check whether this even affects you in the first place.
Arising Questions
Should we only search for one Package or multiple? (important for design of API POST) What else could be added to API Endpoint, to increase usefulness? (timestamp, branches, affected version) for more transparency and generally for more documentation.
Brainstorming Ideas
Frontend
What would the results look like? maybe a colored badge, like Yellow = "is in repository x but > vulnerable version", Red = "is in Repository but below vulnerable version.) This could also be useful in the future with vexxing.
Backend
tba mapping:
organizations.db projects.db assets.db
TODO
Front End
- [x] Design Concept
- [ ] Finish Search Bar in Organization Tab
- [x] send request to API Endpoint Backend
Backend
- [x] Create SQL Query
- [x] Create API Endpoint (GET: pkgname, organization_slug, token, ) (POST: repositories affected in Array, Version of each Package in Repository )
SELECT o.id, o.name, p.organization_id, p.name, a.project_id, a.id, cd.asset_id, cd.component_purl, c.purl, c.version
FROM organizations as o
LEFT JOIN projects as p
ON o.id = p.organization_id
LEFT JOIN assets as a
ON p.id = a.project_id
LEFT JOIN component_dependencies as cd
ON a.id = cd.asset_id
LEFT JOIN components as c
ON cd.component_purl = c.purl
WHERE o.name = 'Organization' AND c.purl LIKE '%alpine-base%';
WITH filtered_component_dependencies as (
SELECT * FROM component_dependencies c WHERE c.component_purl LIKE '%alpine-base%'
)
SELECT * from filtered_component_dependencies c LEFT join artifact_component_dependencies ON component_dependency_id = id
{
"pageSize": 10,
"page": 1,
"total": 58,
"data": [
{
"componentDependencyId": "23f04b1b-2344-4db2-b90c-486ffa714d81",
"organizationId": "dd340600-b5c1-4caf-8097-92a6a17121b6",
"organizationName": "Org",
"projectId": "f126a32c-8a1d-4aa9-9d90-dd31b6383897",
"projectName": "Group",
"projectSlug": "group",
"assetId": "f134a872-9437-42eb-91e1-2fb0360791c4",
"assetName": "Group1Repo1",
"assetSlug": "group1repo1",
"assetVersionName": "main",
"componentPurl": "pkg:apk/alpine/[email protected]",
"componentVersion": "3.22.1-r0"
},
{
"componentDependencyId": "beefd68c-3a65-4d1a-9d59-b18a2c7c6f99",
"organizationId": "dd340600-b5c1-4caf-8097-92a6a17121b6",
"organizationName": "Org",
"projectId": "f126a32c-8a1d-4aa9-9d90-dd31b6383897",
"projectName": "Group",
"projectSlug": "group",
"assetId": "fda563ec-9f96-4adb-8d32-04d6a456bd89",
"assetName": "Group1Repo2",
"assetSlug": "group1repo2",
"assetVersionName": "main",
"componentPurl": "pkg:apk/alpine/[email protected]",
"componentVersion": "3.22.1-r0"
}
]
}
- [ ] Create SQL query
- [ ] plan in progress
This is not done yet, just saving this before I am accidently closing the draft
