Add `vscode_extensions` table

[lukeheath]

Goal

User story
As a security engineering using Fleet,
I want to know what VS Code extensions are installed on my devices,
so that I can create policies for vulnerable or disallowed extensions.

More info

VS Code is the most common IDE used by software engineers (81% in 2023 Stack Overflow poll)

Much of the reason for its popularity is the thriving extensions ecosystem. These extensions have access to quite a lot, and make their own HTTP calls. There is some concern about how vulnerable these are. There is also concern about AI extensions that may be pushing code files to an LLM, which would violate company policy.

Changes

Product

• [x] Osquery issue: https://github.com/osquery/osquery/issues/8138
• [ ] Outdated documentation changes: update the osquery version we're generating the table documentation from

ℹ️  Please read this issue carefully and understand it. Pay special attention to UI wireframes, especially "dev notes".

Context

• Requestor(s): _____

QA

Risk assessment

• Requires load testing: TODO
• Risk level: Low / High TODO
• Risk description: TODO

Manual testing steps

1. Step 1
2. Step 2
3. Step 3

Testing notes

Confirmation

1. [ ] Engineer (@____): Added comment to user story confirming succesful completion of QA.
2. [ ] QA (@____): Added comment to user story confirming succesful completion of QA.

[lukeheath]

This has been accepted onto our Q4 roadmap. We hope to ship before the end of the year.

@ksatter @Patagonia121 Would you please notify the customer who initially requested this? Thanks!

[Patagonia121]

Okay @lukeheath all tagged customers have been notified about this!

[lucasmrod]

Related: https://github.com/osquery/osquery/issues/8138

[lucasmrod]

A query has been merged to the standard library that allows fetching VSCode extensions: https://github.com/fleetdm/fleet/pull/14213

• Requires fleetd.
• Currently works on macOS, but could be extended to Windows and Linux.

[noahtalerman]

@Patagonia121 to follow up w/ customers about the query: https://fleetdm.com/queries/get-a-list-of-visual-studio-code-extensions

Is macOS only enough? Do they get all the info they need from the query?

[lucasmrod]

FYI: There's a PR in progress in osquery, so we might be getting such table in osquery core soon: https://github.com/osquery/osquery/pull/8150

[noahtalerman]

Nice!

[noahtalerman]

FF (2023-10-12)

Noah: Is this just about adding vscode_extensions to fleetdm.com/tables? If yes, this is a website request.

Noah: If it requires agent work we won't take it.

Luke: A lot of new AI plugins that are pushing this up the priority list. Concerned about company IP. Need the reporting to know it's there.

Noah: WONT

[noahtalerman]

Feature fest:

The osquery PR to add a vscode_extensions table is being reviewed by @zwass: osquery/osquery#8150

Zach, do you think this table will make it into the next version of osquery?

[Patagonia121]

Correct, this was to add tables to Fleet to find those extensions - we've already built some queries that will help users as a workaround, but hopefully we'll have native tables in Fleet in the future (at least that's what we've heard requested by customers)

[zwass]

Yeah, this should make the next release.

[noahtalerman]

Yeah, this should make the next release.

@zwass great! When is the next osquery release happening?

[zwass]

It's not scheduled, but I'd like to see us get one out before the end of the year.

[noahtalerman]

Hey @zwass @sharon-fdm and @xpkoala heads up, I moved this story to the "In review" column in the Endpoint ops board so we can queue the improvement up for later QA (the osquery PR is currently in review)

@zwass when the osquery PR is merged can you please move this story to the "Awaiting QA" column so that we can test it?

[lukeheath]

@sharon-fdm @noahtalerman @zwass Looks like this merged a couple of weeks ago. Is it ready for QA? https://github.com/osquery/osquery/pull/8150

If so, please add manual testing steps and move to "Ready for QA". Thanks!

[zwass]

Yes, the new osquery release is now deployed to edge so this should be easy to test.

[xpkoala]

Looking good using edge.

[noahtalerman]

the new osquery release is now deployed to edge so this should be easy to test.

@lukeheath or @zwass have we pushed the new osquery release (that includes this table) to the stable channel?

[noahtalerman]

@zwass do you know if there were doc updates made to the osquery schema as part of adding these tables?

[zwass]

We have not pushed to stable (osquery release has not been declared stable yet). There are always docs for new tables in osquery because they are automatically generated via the table implementation. Those aren't available on the website yet because the release hasn't been marked stable.

[noahtalerman]

C&C: Asked Luke if we pushed the latest osquery to stable: https://github.com/fleetdm/fleet/issues/15215#issuecomment-1899062824

[noahtalerman]

C&C: Let's close after the osquery 5.11 release is pushed to stable.

[lucasmrod]

@noahtalerman osquery 5.11.0 has been pushed to stable. The table is returning results on my device (SELECT * FROM users JOIN vscode_extensions USING (uid);).

[noahtalerman]

. osquery 5.11.0 has been pushed to stable. The table is returning results on my device (SELECT * FROM users JOIN vscode_extensions USING (uid);).

Nice!

@eashaw do you know how to pull this table into fleetdm.com/tables?

It looks like the table is in the osquery schema: https://osquery.io/schema/5.11.0/#vscode_extensions

Do we have to run some GitHub action or script?

[eashaw]

@noahtalerman, we need to update the website's custom configuration to use the new osquery schema version and run the generate-merged-schema script in the website folder. I'll make a PR to do that.

[noahtalerman]

@zwass how do you get results from the vscode_extensions table?

Rachael and I tried this query but no results were returned:

select * from vscode_extensions

Do we need to do some joining against the users table?

[lucasmrod]

Yes, see https://github.com/fleetdm/fleet/issues/13891#issuecomment-1924547487.

[noahtalerman]

Yes, see https://github.com/fleetdm/fleet/issues/13891#issuecomment-1924547487

Wow, how did I miss that? 😅 Thanks Lucas

[noahtalerman]

Hey @eashaw do you know what we have to do to update the tables in the Fleet product? Currently in dogfood (commit ba03140), the vscode_extensions table doesn't appear in the right-side bar and isn't a valid table:

[eashaw]

@noahtalerman The version that is deployed to dogfood does not have the updated osquery_fleet_schema.json file that includes this table.

[noahtalerman]

Hey @eashaw if I'm understanding correctly, the generate-merged-schema script creates the osquery_fleet_schema.json file.

When we release a new version of the Fleet product, it will take the latest osquery_fleet_schema.json.

If that's the case, we would have to run the generate-merged-schema again to make sure the updates to the schema in this PR make it into the core product: #16779

Is that right?

[eashaw]

@noahtalerman That is correct. I made a PR to regenerate the merged schema and fix a minor formatting issue on the vscode_extensions override file here: https://github.com/fleetdm/fleet/pull/16829

[noahtalerman]

@eashaw thanks!

Sounds like regenerating the merged schema is a manual task. If that's right, do we have a ritual so we remember to do this?

I'm thinking it's something we could do every Fleet release (patch and minor) so that any updates to the schema make it into the product ASAP.

If you agree, I can take that to Luke (release DRI)

[eashaw]

@noahtalerman I have a ritual to update the merged schema every three weeks (This typically happens on the 2nd Tuesday of a sprint). I'll change the ritual frequency to weekly and will make sure it is documented in the handbook. (https://github.com/fleetdm/fleet/pull/16852)

[fleet-release]

Extension table blooms, Security clarity looms, No policy dooms.

[rachaelshaw]

@Patagonia121 this was shipped and is now documented on the website