@atripp We are currently testing new ProGet 2023 release. Are there any news about the migration from OSS Index to PGVC? Is there a tool, a script or some kind of documentation available?

@atripp said in SPDX license expressions:

Thanks @sebastian !! Your RegExFu is impressive

Thanks, but to be honest: it's mainly just trial and error

@atripp said in SPDX license expressions:

Hi @sebastian

Just an update; this was committed to the PG2023 code base, and seems to work on a few packages I tried (but I can't find too many).

Basically we just will enumerate the code matches in this Regex: ^\(?(( OR )?(?<code>[0-z-_\.]*))+\)?$

That will catch (a OR b) and a OR b, but if one were to be silly and add ((a) OR (b)) then it would revert back.

Doesn't seem worth getting any more complex than that :)

Thanks for the update on this! I don't think we will see a lot of complex combinations in the wild that are pure OR disjunctions, but just in case someone does something stupid, I took the liberty to slightly modify your Regex. How about this: ^(?>((\s+OR\s+)?(?<c>\()*(?<code>[0-z-_\.]+)(?<-c>\))*)+)(?(c)(?!))$

The \s+ before and after the OR will allow any amount of whitespaces, and the (?<c>\(), (?<-c>\)) and (?(c)(?!)) parts use balancing groups to allow any number of matching brackets (i.e. same amount of opening and closing brackets). Also, I replaced (?<code>[0-z-_\.]*) with (?<code>[0-z-_\.]+) to make sure there are no empty code matches.

I haven't been able to come up with a valid SPDX expression that could not be matched by that Regex, so I think this should pretty much cover it. Only thing that one could add would be to allow leading and trailing whitespaces.

Hi @atripp, thanks for the quick feedback and the insights!

As for the migration part: great to hear that there is something planned. It seems that it would be best for us to wait until some guidance or migration tool is available.

ProGet Vulnerability Central (PGVC) was announced for ProGet 2023 and is available as a preview feature since 2022.20. Would you mind sharing some insights on this new feature? I already have a few questions about it:

1. What is the advantage of using PGVC over OSS Index? Two things I could figure out so far are a) you don't need an API key from OSS (they could stop their free service any time) and b) it's updated with each version update of ProGet (which really only is of any significance if the ProGet server wasn't connected to the internet, in which case I would wonder what feeds there would be to scan...?). What would be other advantages? Does PGVC use more sources? Is it updated faster or more frequently than OSS? Is it more accurate, more reliable, ...?

2. The blog post states that PGVC data is available "instantly" while OSS Index needs to be updated over night. What does that mean? It's my understanding that both databases are updated daily. What is the actual difference here?

3. What about migrating from OSS Index to PGVC? We have a lot of assessed vulnerabilities from OSS. Will ProGet match those to the ones from PGVC? Or will we have to re-asses all of them?

4. What about using both services (OSS Index and PGVC) at the same time? Is that possible (it seems possible in the feeds' settings)? Would it make sense to do that, maybe because both services use different sources or maybe as a form of redundancy/backup?

Hi @apxltd

I didn't realizte ProGet actually already supports multiple licenses. In that case it might make sense to support just the OR operator for now (and see if there is an actual need to support other operators or more complex expressions).

In fact, one could argue that the AND operator actually creates a new license (the combination of the two or more licenses involved), and that it makes sense to treat the combination as a completely new license, while the OR operator offers you a chance to choose one of the licenses. Same goes for the WITH operator: I don't mind treating License Abc WITH exception Xyz as a completly new and different license. So it might make sense to treat OR different to AND or WITH.

The only operator that is similar to the OR operator would be the + operator. Wouldn't it be great if ProGet "knew" that LGPL-2.1 and LGPL-3.0 are newer versions of LGPL-2.0 and that LPGL-2+ means "LGPL-2.0 or later" (effectively LGPL-2.0 OR LGPL-2.1 OR LGPL-3.0)?

Hi @apxltd, thanks for the insights!

I half expected this to be way more complicated than I had hoped for, but one can dream...

@sebastian said in Package license definition:

7. It would require even less effort to just ask package authors to specify license codes, and then eventually the problem will go away on its own probably

Will try this, starting with the Google folks...

One more thought on the last point: What about packages that do not use a supported open source license? Think of Oracle.ManagedDataAccess. There simply is not SPDX tag that matches their proprietary license

That package is actually a good example for a scenario where hash-based license assignments would make a lot of sense. They update their license every now and then. Last update was from version 21.7.0 to 21.8.0. A hash-based license check would have caught that and labeled the new license as "unknown". As a result, version 21.8.0 would have been blocked until someone reviews the new license and manually approves it.

Hi there!

In our ongoing quest to identify the licenses of all packages used in our products (and, eventually, blocking all packages with unknown licenses), we came across license expressions that combine several licenses. The syntax for such combinations is documented here: https://spdx.github.io/spdx-spec/v2-draft/SPDX-license-expressions/

Take for example the npm package "atob" (https://www.npmjs.com/package/atob). It is licensed under both, the MIT and Apache 2 license. Consequently, the corresponding license expression is (MIT OR Apache-2.0). Obviously, this is not recognized by Proget out of the box.

Now, we could define new licenses for every combination of two licenses that we come across. However, I was wondering whether it would make sense to support at least a simple subset of the SPDX license expression syntax, specifically the AND and OR key words. License filtering could then be implemented as follows:

X AND Y: Allow downloading package if both X and Y are allowed.
X OR Y: Allow downloading if at least one of the licenses is allowed.

X and Y could be simple SPDX license tags or another disjunctive or conjunctive license expression.

Would such an approach make sense? It probably would not trivial to implement and I honestly don't know how many packages use such combining expressions (I haven't come across a lot of them yet), so I don't know if it would be worth the effort. Would be nice to hear how everyone else is dealing with such packages.

HI @apxltd,

1. It's even more confusing to use than packageid://, so we'd need to find a better UI solution

Would it be more confusing to have two or three hash values instead of dozens, maybe hundreds of packageid:// entries for a given license? People are getting used to using hash values (e.g. with GIT commits). One would have to be able to view the original license text, of course (see next point)...

2. We'd want to store the full license text as well, so it'd be easy to confirm the contents

Agreed. Otherwise, there is no way of confirming that the hash value was assigned to the correct license. But I don't think that would be infeasible. One could either store the content of the license file in a dedicated table in the database, or in file. And we only have to store it once per hash value. A single license file takes what, 1 maybe 2 KB? Let's say we will have a dozen, maybe a hundred different license files at the end (unlikely, probably more in the lower double digits area). That would take less than 1 MB in total.

3. This is all a nontrivial engineering effort

Agreed, but it's not too complex either. There is obviously already code in Proget which detects that there is an embedded license file and that can display the content of that file. I'd say you are probably almost halfway there :-)

4. We're not sure how many packages this would impact and how much value / time savings it would represent

Here is an example: Consider the Google.Apis.* packages (https://www.nuget.org/packages?q=google.apis). They all have the exact same Apache 2.0 license file embedded, so we would have to assign just a single hash value instead of dozens / hundreds of individual packages.
Another point is updates of packages. At the moment we would have to assign a license to every new version of a package. I think this feature could be a huge time saver.

5. None of this would even work for remote packages, which is by far what most users find confusing and have issues with

Yes, for this to work, Proget would have to download the given package and read to content of its license file. However, there are two major parts of Proget where license become relevant, and I believe the it's not an unrealistic scenario that Proget has downloaded the package in question in both scenarios:

1. Blocking packages. We want to prevent users from downloading packages with specific licenses. Let's assume a user wants to download X with version 1.2.3, and that package has an embedded license file. Now, to be able to serve the package to the user, Proget has to download it first, right? Either it has done so already and the package is cached, or Proget has to download it on demand. In both cases Proget has a chance to read the license file and compute a has value for its content, if it hasn't already done that before.
2. Reporting. Most packages that are analyzed by the SCA feature should have been downloaded in the past via Proget.

6. It would probably require less engineering effort to scan/query all packages on NuGet and make a "database" of package licenses using a little human intelligence

I think downloading all packages (including all versions of each package) and analyzing them would take a lot of effort and resources.

7. It would require even less effort to just ask package authors to specify license codes, and then eventually the problem will go away on its own probably

Will try this, starting with the Google folks...

@apxltd said in Package license definition:

In the past, we thought of adding a kind of wildcard URL for licenses, like a "package://Microsoft.*" => "MSPL" would basically associate all packages with that prefix that don't otherwise have a SPDX code, or an explicit license.

Wonder if that would help here?

A colleague of mine actually had an interesting idea today: How about calculating hash values (like SHA-1) for embedded license files and assigning licenses to those hash values? That way one would only have to assign licenses to each license text once (if the license text is identical across different packages or package versions).

The workflow would be similar to assigning licenses to actual packages / versions, but instead of adding an pseudo URL like "packages://SomeVendor.SomePackage/1.2.0" we could do something like "hash://0xA1B2C3", where 0xA1B2C3 would be the hash value of the content of the license file. All other packages / versions with the exact same license text would automatically be mapped to the same license.

Of course, some license texts include the name of the product or a copyright note, so we would still get multiple entries for the same license. But it should be significantly less than adding one entry for every package / version.

What do you guys think about this idea?

@apxltd Using URLs for packages would be a nice feature, especially as package URLs contain version numbers. Getting rid of those might already be helpful, because some package come in a rather large number of versions. Of course, even applying a wildcard to just the version of a package might lead to wrong results, because in theory a package could change it's licese from one version to the next, but that is probably not a very realistic scenario.

However, things can become messy when different wildcard URLs could be applied to the same package. Unfortunately, not all Microsoft packages use MSPL. Some use MIT, some use the proprietary licenses... It's a real mess. So it probably wouldn't be as easy as making just one rule for microsoft.*. But still, using wildcards could make things a bit easier.

@pmsensi We haven't used TrustedInstaller yet. The approach is interesting, and yes: it would probably make sense to have a central service like a ProGet server check package owners, but it would be a completely different approach to a very different problem. I don't think applying licenses to package owners makes a lot of sense, because - as written above - package owners like Microsoft can apply different licenses to different packages.

That being said, having a new entity "package owner" or "publisher" or something like that and being able to filter for that entity could be a cool new feature. This could also be used in the SBOM reporting feature (like: 40 packages come from Microsoft, 20 from vendor A, 7 from vendor B).

@pmsensi said in Package license definition:

1222 packages are a lot! For now, we do not have many packages, but we will have them for sure :)

Yeah, we have been using ProGet for a while now... :-)

I'm pretty sure some are false positives, though. It seems that when a package has been downloaded, but there is no usage of it in any product (happens for infrastructure packages like angular/core, angular/cli or some Visual Studio extensions), there is no license entry in the database even though the package has a perfectly fine SPDX tag.

What's driving me nuts at the moment is that Microsoft seems to be using embedded license info files for quite a number of their packages. Assigning licenses to those is going to take a while...

Have you tried setting the log level for that API key to "Everything"? And do you see log entries for that key when uploading NuGet packages?

Have you tried disabling anonymous access?

Hi @pmsensi,

we are facing a similar challenge. Basically, what I am interested in is a list of packages that we are currently using in our products that don't have a known license assigned to them (or: Proget was unable to identify the license, e.g. because the license info just states "see licenense.txt" or something like that). I think that you are looking for something similar and are not asking to analyze all packages that exist on Nuget or npm, because that would probably would not be feasible (as @atripp already mentioned in her reply).

We are using the new Reporting & SCA feature extensively, and we are getting some very nice license statistics out of it. However, the one thing that is currently missing is the number or list of packages with an unknown license. I hope that this will be added in a future release. In the mean time, looking at the database can be a workaround to at least get a list of the package name.

I think starting with 2022, ProGet started storing license infos of known packages in the database. I am assuming that that info is stored for packages that have been downloaded via ProGet (i.e. "cached") or maybe also for packages that have been reported to ProGet via pgscan (@atripp please feel free to add some info to this).

One simple approach to get the license info of all "known" packages, would be this:

select 
	pi.Package_Name, l.External_Id, l.Title_Text
from 
	PackageIds pi
left join 
	PackageLicenses pl on pl.Package_Id = pi.Package_Id
left join
	Licenses l on pl.License_Id = l.License_Id
group by pi.Package_Name, l.External_Id, l.Title_Text
order by 1;

Note that this select groups by package names and ignores versions. You could now simply look for packages without a known license like this:

select 
	pi.Package_Name, l.External_Id, l.Title_Text
from 
	PackageIds pi
left join 
	PackageLicenses pl on pl.Package_Id = pi.Package_Id
left join
	Licenses l on pl.License_Id = l.License_Id
where l.External_Id is null
group by pi.Package_Name, l.External_Id, l.Title_Text
order by 1;

However, if you look at the result of first query, you might notice that there might be multiple entries for some packages. That might be because a package has changed its license or just its license info (maybe switching from a "see license.txt" to an actual SPDX tag), or maybe because there was a bug in previous versions of ProGet that has been fixed in 2202.18 (https://inedo.myjetbrains.com/youtrack/issue/PG-2263). So you might get some false positives with this approach.

To get a list of packages without any entry of a known license, we have to eliminate the ones with multiple entries. There might be a better and more readable way to get this done, but the query that worked for us is this one:

select 
	distinct pi.Package_Name
from 
	PackageIds pi
left join 
	PackageLicenses pl on pl.Package_Id = pi.Package_Id
left join
	Licenses l on pl.License_Id = l.License_Id
where l.External_Id is null
and pi.Package_Name in 
(select Package_Name
from 
(
select 
a.Package_Name
from 
(
select 
	pi.Package_Name, l.External_Id, l.Title_Text
from 
	PackageIds pi
left join 
	PackageLicenses pl on pl.Package_Id = pi.Package_Id
left join
	Licenses l on pl.License_Id = l.License_Id
group by pi.Package_Name, l.External_Id, l.Title_Text

) a
group by a.Package_Name
having count(*) = 1
) b);

BTW: in case you are curious: that select gives us a list of 1222 packages.

Hi @stevedennis,

having a PackageLicense table would be great for two reasons:
a) It would give as the ability to do some reporting on used licenses.
b) There are lots of packages that use embedded license files. ProGet already has a feature where we can manually assign the corresponding license to a specific version of a package, but as far as I can tell, this is done by generating a pseudo URL for each package and assigning that URL to the corresponding license. While this does work fine as long as it is done only for a small number of packages, I'm not so sure how it affect usability (and maybe performance) when this is done on a larger scale. Having a separate table that connects packages (or package versions) to specific licenses might be a cleaner way to store and process that information.

I'd have to check whether we could give you a dump of our database, but we are only just starting to use ProGet, so there isn't really too much data there yet. The fact that we are new to this is actually the reason we are so interested in reports about licenses and vulnerabilities: We are setting up workflows, etc. and at some point we want to completely block downloads of vulnerable packages or packages that use restrictive/unknown licenses, but before we activate hard filtering rules we want to get an overview on how this would affect products currently in development (so we can adapt our rules or define exceptions). At the moment we are generating data by integrating pgscan to our build pipelines and gathering download statistics for packages (and hope that we will be able to analyze that data in a useful way), so feel free to reach out in a month or so. Maybe we will have enough meaningful data to be of use for you guys by then.

Cheers,
Sebastian

Hi @stevedennis,

we did a quick test and it turned out that connecting vulnerabilities to consumers of affected packages is actually pretty straight forward.

The following statement gives us all consumers:

SELECT 
	v.Vulnerability_Id,
	d.Dependent_Package_Name, 
	MIN(d.Dependent_Version_Text) AS Min_Dependent_Package_Version, 
	MAX(d.Dependent_Version_Text) AS Max_Dependent_Package_Version,  
	d.Package_Name, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text 
FROM 
	PackageDependents d 
JOIN 
	Feeds f 
	ON d.Feed_Id = f.Feed_Id
JOIN 
	(SELECT * FROM Vulnerabilities CROSS APPLY STRING_SPLIT(Package_Versions, ',') ) v
	ON d.Package_Name = v.Package_Name 
	AND d.Version_Text = LTRIM(v.value)
	AND f.FeedType_Name = v.FeedType_Name
GROUP BY
	v.Vulnerability_Id,
	d.Dependent_Package_Name, 
	d.Package_Name, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text
;

Another statement shows all downloads of affected packages (useful in case not all products already provide consuming data to ProGet):

SELECT
	v.Vulnerability_Id,
	d.Package_Id, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text,
	d.User_Name,  
	MAX(d.Download_Date) AS Last_Downloaded,
	COUNT(*) AS Downloads_Count
FROM 
	PackageDownloads d 
JOIN 
	Feeds f 
	ON d.Feed_Name = f.Feed_Name
JOIN 
	(SELECT * FROM Vulnerabilities CROSS APPLY STRING_SPLIT(Package_Versions, ',') ) v
	ON d.Package_Id = v.Package_Name 
	AND d.Version_Text = LTRIM(v.value)
	AND f.FeedType_Name = v.FeedType_Name
GROUP BY 
	v.Vulnerability_Id,
	d.Package_Id, 
	d.Version_Text,
	d.User_Name,
	v.Score, 
	v.Title_Text
;

Note that in both cases we assume that Package_Versions is either a single version or a comma separated list of versions (this was the case for all the examples we have at the moment).

[Coming back to my original question, that is actually something you guys could integrate into the Vulnerabilities page of ProGet in a slightly modified way: Basically, group both statements by Vulnerability_Id to get the download count and consumers count and display those numbers in separate columns for a quick overview. When a user clicks on one of those numbers, you can display a detailed list for that specific vulnerability.]

One thing we could not find yet was information about the licensing of each package. That is an information which is displayed within ProGet on every package's overview page, but it is apparently not stored within the database (or maybe we just didn't find it)? This is something we would like to be able to link to consumers as well (which product uses which set of licenses?). Is there a simple way to get to that information?

Cheers,
Sebastian

Hi Steve,
we did consider the SQL approach, but didn't go with it initially, since (according to the documentation) it is not really a recommended way to interact with ProGet.
But if you say that you might consider supporting this scenario in the future, we can definitely look into it. Digging into the relational schema shouldn't be a problem. I will do some testing and update this thread when we have some results.

Cheers,
Sebastian

Hi Steve,
thanks for your reply. One thing that would make things a bit easier would be if there would be a link from each entry in the "Vulnerabilities" list to the corresponding package's page.

Example:
Let's say our ProGet server was named "myprogetserver.intranet" and our Nuget proxy feed (i.e. the feed that is connected to the api.nuget.org) was named "Nuget".
[CVE-2019-1302] defines a vulnerability for Microsoft.AspNetCore 2.2.0, so it would be great to have a link to myprogetserver.intranet/feeds/NuGet/Microsoft.AspNetCore/2.2.0/ or myprogetserver.intranet/feeds/NuGet/Microsoft.AspNetCore/2.2.0/vulnerabilities.
[CVE-2021-26701] defines a vulnerability for several versions of System.Text.Encodings.Web (4.0.1, 5.0.0, 4.7.1, 4.7.0, 4.6.0, 4.5.0, 4.0.0 and 4.4.0), so each displayed version should be a link to the corresponding page (myprogetserver.intranet/feeds/NuGet/System.Text.Encodings.Web/4.0.0/, myprogetserver.intranet/feeds/NuGet/System.Text.Encodings.Web/4.0.1/, ...).

I understand that this is a non-trivial request, because packages could occur in several different feeds and information of packages that have not been downloaded are not cached, but maybe one solution to this would be to assign default feeds per package type for the Vulnerabilities view? Of course the perfect solution would be to have an additional column in that view that would list all affected consumers of those packages.

Since you mentioned generating our own report: We are actually considering building our own tool to analyze & display packages and products and the dependencies between them. What would help as a lot for this would be a kind of generic API for all feeds and package types. What we would need is this:

• list of all feeds
• per feed: feed type and list of all packages in that feed
• per package: list of all versions of that package
• per package version: license information, dependencies and vulnerabilities

Basically, it would be very similar to the API for Universal feeds, just for all package types (or at least Nuget and npm).

Any thoughts on the points above?

Cheers,
Sebastian