Querying safari_extensions returns nothing since macOS 10.15

[patil215]

Bug report

What operating system and version are you using?

 version = 10.15.4
   build = 19E2265
platform = darwin

What version of osquery are you using?

version = 4.3.0

What steps did you take to reproduce the issue?

1. Install and enable a Safari extension, for example, LastPass: https://support.logmeininc.com/lastpass/help/how-do-i-install-the-safari-app-extension-on-my-mac-lp010097
2. Confirm that extension is enabled in Safari (Safari -> Preferences -> Extensions)
3. Open osquery (osqueryd -S) and run:
   SELECT e.name, e.identifier, e.version, e.description, e.path, "safari" as browser FROM users u JOIN safari_extensions e ON e.uid = u.uid;

What did you expect to see?

LastPass extension listed.

What did you see instead?

Nothing is printed.

osquery> SELECT e.name, e.identifier, e.version, e.description, e.path, "safari" as browser FROM users u JOIN safari_extensions e ON e.uid = u.uid;
osquery>

[FritzX6]

Apple changed the way that Safari handles extensions in macOS Mojave deprecating their old legacy extension platform and instead requiring all extensions to go through the App Store and be registered as Apps. (https://9to5mac.com/2018/06/09/safari-12-extensions-more/)

We can use predictable installation path patterns to approximate the previous functionality by using a query like so:

WITH extensions_flat AS (SELECT * FROM plist WHERE path LIKE '/Applications/%.app/Contents/PlugIns/%SafariAppExtension.appex/Contents/Info.plist')
SELECT 
SPLIT(path, '/', 1) AS extension_parent_app,
MAX(CASE WHEN key = 'CFBundleIdentifier' THEN value END) AS bundle_identifier,
MAX(CASE WHEN key = 'CFBundleDisplayName' THEN value END) AS display_name,
MAX(CASE WHEN key = 'NSHumanReadableDescription' THEN value END) AS description,
MAX(CASE WHEN key = 'CFBundleShortVersionString' THEN value END) AS bundle_short_version,
MAX(CASE WHEN key = 'CFBundleVersion' THEN value END) AS bundle_version,
MAX(CASE WHEN key = 'NSHumanReadableCopyright' THEN value END) AS copyright
FROM extensions_flat
GROUP BY path;

extension_parent_app = 1Password 7.app
   bundle_identifier = com.agilebits.onepassword7.1PasswordSafariAppExtension
        display_name = 1Password
         description = Easy Access to Your Logins, Credit Cards and Identities.
bundle_short_version = 7.5
      bundle_version = 70500002
           copyright = Copyright © 2018 AgileBits Inc. All rights reserved.

extension_parent_app = DuckDuckGo Privacy Essentials.app
   bundle_identifier = com.duckduckgo.macos.PrivacyEssentials.SafariAppExtension
        display_name = Privacy Dashboard
         description = DuckDuckGo Privacy Dashboard - track who we caught trying to track you.
bundle_short_version = 1.4.1
      bundle_version = 12
           copyright = Copyright © 2019 Duck Duck Go, Inc. All rights reserved.

Additional research should be done to investigate whether additional paths should be considered (eg. /Users/%/Applications... as well)

[theopolis]

Slight modification to the above query:

WITH extensions_flat AS (SELECT * FROM plist WHERE path LIKE '/Applications/%.app/Contents/PlugIns/%.appex/Contents/Info.plist')
SELECT 
SPLIT(path, '/', 1) AS extension_parent_app,
MAX(CASE WHEN key = 'CFBundleIdentifier' THEN value END) AS bundle_identifier,
MAX(CASE WHEN key = 'CFBundleDisplayName' THEN value END) AS display_name,
MAX(CASE WHEN key = 'NSHumanReadableDescription' THEN value END) AS description,
MAX(CASE WHEN key = 'CFBundleShortVersionString' THEN value END) AS bundle_short_version,
MAX(CASE WHEN key = 'CFBundleVersion' THEN value END) AS bundle_version,
MAX(CASE WHEN key = 'NSHumanReadableCopyright' THEN value END) AS copyright
FROM extensions_flat
GROUP BY path;

[FritzX6]

Ah that is better! Thanks @theopolis!

[mike-myers-tob]

Teddy's above command worked great but it feels like a workaround to use in the short term. Longer term, should the virtual table code detect its environment and present the extensions correctly by parsing the plists in their new locations?

[theopolis]

Yes and no, I don’t think this current solution is accurate. It’s possible to have matches to this query, which are not recognized by Safari as extensions. And I am not sure if the inverse is true.

[FritzX6]

Amended query which I think is closer to the end goal that we will want. This plist just popped up for me today when I was looking at this issue again, I think it is a recent addition:
~/Library/Containers/com.apple.Safari/Data/Library/Safari/AppExtensions/Extensions.plist

Of particular noteworthiness is the inclusion of the Enabled flag which I know people have requested in the past for other extension tables.

One issue with the query below is I am flattening using MAX for the keys level and has_injected_content which may result in unintended output if there were multiples of these nested keys. Haven't tested that enough to be sure.

The full query with results:

WITH 
app_extensions_flat AS (
  SELECT * FROM plist 
  WHERE path LIKE '/Applications/%.app/Contents/PlugIns/%.appex/Contents/Info.plist'),
app_extension_pivot AS (
  SELECT 
    SPLIT(path, '/', 1) AS extension_parent_app,
    MAX(CASE WHEN key = 'CFBundleIdentifier' THEN value END) AS bundle_identifier,
    MAX(CASE WHEN key = 'CFBundleDisplayName' THEN value END) AS display_name,
    MAX(CASE WHEN key = 'NSHumanReadableDescription' THEN value END) AS description,
    MAX(CASE WHEN key = 'CFBundleShortVersionString' THEN value END) AS bundle_short_version,
    MAX(CASE WHEN key = 'CFBundleVersion' THEN value END) AS bundle_version,
    MAX(CASE WHEN key = 'NSHumanReadableCopyright' THEN value END) AS copyright
  FROM app_extensions_flat
  GROUP BY path),
human_accounts AS (
  SELECT username, uid, directory FROM users WHERE SUBSTR(uuid,0,8) != 'FFFFEEE'),
safari_raw AS (
  SELECT 
    username, uid,
    MAX(CASE WHEN subkey = 'Enabled' THEN value END) AS enabled,
    MAX(CASE WHEN subkey LIKE '%Level' THEN value END) AS level,
    MAX(CASE WHEN subkey LIKE '%Has Injected Content' THEN value END) AS has_injected_content,
    REGEX_SPLIT(key,' \(', 0) AS bundle_identifier, 
    REGEX_MATCH(key,'\((.*?)\)', 1) AS extension_id
  FROM plist JOIN human_accounts ha ON directory = '/Users/' || SPLIT(path,'/',1) 
  WHERE path LIKE '/Users/%/Library/Containers/com.apple.Safari/Data/Library/Safari/AppExtensions/Extensions.plist'
  GROUP BY key, path),
-- Remove nulls
safari_extensions_plist AS (
  SELECT * FROM safari_raw WHERE enabled NOT NULL)
SELECT * FROM safari_extensions_plist LEFT JOIN app_extension_pivot USING(bundle_identifier);

            username = fritz-imac
                 uid = 502
             enabled = 1
               level = All
has_injected_content = 1
   bundle_identifier = archive.org.waybackmachine.mac.extension
        extension_id = ZSFX78H3ZT
extension_parent_app = Wayback Machine.app
        display_name = Wayback Machine
         description = Archive and explore web pages over time!
bundle_short_version = 1.4
      bundle_version = 19
           copyright = 
           
            username = fritz-imac
                 uid = 502
             enabled = 1
               level = All
has_injected_content = 1
   bundle_identifier = com.agilebits.onepassword7.1PasswordSafariAppExtension
        extension_id = 2BUA8C4S2C
extension_parent_app = 1Password 7.app
        display_name = 1Password
         description = Easy Access to Your Logins, Credit Cards and Identities.
bundle_short_version = 7.7
      bundle_version = 70700016
           copyright = Copyright © 2020 AgileBits Inc. All rights reserved.
           
            username = fritz-imac
                 uid = 502
             enabled = 1
               level = All
has_injected_content = 1
   bundle_identifier = com.duckduckgo.macos.PrivacyEssentials.SafariAppExtension
        extension_id = HKE973VLUW
extension_parent_app = DuckDuckGo Privacy Essentials.app
        display_name = Privacy Dashboard
         description = DuckDuckGo Privacy Dashboard - track who we caught trying to track you.
bundle_short_version = 1.4.3
      bundle_version = 14
           copyright = Copyright © 2019 Duck Duck Go, Inc. All rights reserved.

[mike-myers-tob]

Confirmed today that:

• the file ~/Library/Containers/com.apple.Safari/Data/Library/Safari/AppExtensions/Extensions.plist exists in both macOS 10.15 and 11, for Safari 14. But only after there is at least one extension installed.
• The plist file is inaccessible without "Full Disk Access" permission in the TCC.db aka Security & Privacy -> Privacy settings. But that's not a unique problem; many plist files are probably like this now.
• For what it's worth, I installed 1 extension from the App Store, which lists as 2 extensions in this plist file and 8 in Safari -> Preferences -> Extensions, where 6 of the 8 are enabled. I think this is okay, there are probably just different abstractions of what constitutes "an extension".
• There is no enabled key in the plist I have. So I would not filter on it.

osquery> WITH 
    ...> app_extensions_flat AS (
    ...>   SELECT * FROM plist 
    ...>   WHERE path LIKE '/Applications/%.app/Contents/PlugIns/%.appex/Contents/Info.plist'),
    ...> app_extension_pivot AS (
    ...>   SELECT 
    ...>     SPLIT(path, '/', 1) AS extension_parent_app,
    ...>     MAX(CASE WHEN key = 'CFBundleIdentifier' THEN value END) AS bundle_identifier,
    ...>     MAX(CASE WHEN key = 'CFBundleDisplayName' THEN value END) AS display_name,
    ...>     MAX(CASE WHEN key = 'NSHumanReadableDescription' THEN value END) AS description,
    ...>     MAX(CASE WHEN key = 'CFBundleShortVersionString' THEN value END) AS bundle_short_version,
    ...>     MAX(CASE WHEN key = 'CFBundleVersion' THEN value END) AS bundle_version,
    ...>     MAX(CASE WHEN key = 'NSHumanReadableCopyright' THEN value END) AS copyright
    ...>   FROM app_extensions_flat
    ...>   GROUP BY path),
    ...> human_accounts AS (
    ...>   SELECT username, uid, directory FROM users WHERE SUBSTR(uuid,0,8) != 'FFFFEEE'),
    ...> safari_raw AS (
    ...>   SELECT 
    ...>     username, uid,
    ...>     MAX(CASE WHEN subkey = 'Enabled' THEN value END) AS enabled,
    ...>     MAX(CASE WHEN subkey LIKE '%Level' THEN value END) AS level,
    ...>     MAX(CASE WHEN subkey LIKE '%Has Injected Content' THEN value END) AS has_injected_content,
    ...>     REGEX_SPLIT(key,' \(', 0) AS bundle_identifier, 
    ...>     REGEX_MATCH(key,'\((.*?)\)', 1) AS extension_id
    ...>   FROM plist JOIN human_accounts ha ON directory = '/Users/' || SPLIT(path,'/',1) 
    ...>   WHERE path LIKE '/Users/%/Library/Containers/com.apple.Safari/Data/Library/Safari/AppExtensions/Extensions.plist'
    ...>   GROUP BY key, path),
    ...> -- Remove nulls
    ...> safari_extensions_plist AS (
    ...>   SELECT * FROM safari_raw)
    ...> SELECT * FROM safari_extensions_plist LEFT JOIN app_extension_pivot USING(bundle_identifier);
+----------+-----+---------+-------+----------------------+---------------------------------------------+--------------+------------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------+-------------------------------------------------------------+
| username | uid | enabled | level | has_injected_content | bundle_identifier                           | extension_id | extension_parent_app   | display_name        | description                                                                                                                      | bundle_short_version | bundle_version | copyright                                                   |
+----------+-----+---------+-------+----------------------+---------------------------------------------+--------------+------------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------+-------------------------------------------------------------+
| mmyers   | 501 |         | All   | 1                    | com.adguard.safari.AdGuard.AdvancedBlocking | TC3Q7MAJXF   | AdGuard for Safari.app | AdvancedBlocking    | This extension applies advanced blocking rules which are not supported by Safari Content Blocking API.                           | 1.8.10               | 48             | Copyright © 2020 AdGuard Software Ltd. All rights reserved. |
| mmyers   | 501 |         | All   | 1                    | com.adguard.safari.AdGuard.Extension        | TC3Q7MAJXF   | AdGuard for Safari.app | AdGuard Safari Icon | You can use this icon to manage AdGuard for Safari settings. For instance, pause blocking on a website, or block an ad manually. | 1.8.10               | 48             | Copyright © 2020 AdGuard Software Ltd. All rights reserved. |
+----------+-----+---------+-------+----------------------+---------------------------------------------+--------------+------------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------+-------------------------------------------------------------+

[a350h]

Going through this now, its working for me in macOS Monterey 12.5.1, but I'm also discovering that not all extensions are listed in that file. there is another file ~/Library/Containers/com.apple.Safari/Data/Library/Safari/WebExtensions. Both contain plists, but with different data. Im no SQL wizard, but I don't think the differences can be reconciled in SQL (other than a handful of similarities) and would need to be joined in python or other environments.

[a350h]

Ok maybe i didnt give myself enough credit. This grabs the basics from both files.

WITH 
app_extensions_flat AS (
  SELECT * FROM plist 
  WHERE path LIKE '/Applications/%.app/Contents/PlugIns/%.appex/Contents/Info.plist'),
app_extension_pivot AS (
  SELECT 
    SPLIT(path, '/', 1) AS extension_parent_app,
    MAX(CASE WHEN key = 'CFBundleIdentifier' THEN value END) AS bundle_identifier,
    MAX(CASE WHEN key = 'CFBundleDisplayName' THEN value END) AS display_name,
    MAX(CASE WHEN key = 'NSHumanReadableDescription' THEN value END) AS description,
    MAX(CASE WHEN key = 'CFBundleShortVersionString' THEN value END) AS bundle_short_version,
    MAX(CASE WHEN key = 'CFBundleVersion' THEN value END) AS bundle_version,
    MAX(CASE WHEN key = 'NSHumanReadableCopyright' THEN value END) AS copyright
  FROM app_extensions_flat
  GROUP BY path),
human_accounts AS (
  SELECT username, uid, directory FROM users WHERE SUBSTR(uuid,0,8) != 'FFFFEEE'),
safari_raw_app AS (
  SELECT 
    username, uid,
    MAX(CASE WHEN subkey = 'Enabled' THEN value END) AS enabled,
    MAX(CASE WHEN subkey LIKE '%Level' THEN value END) AS level,
    MAX(CASE WHEN subkey LIKE '%Has Injected Content' THEN value END) AS has_injected_content,
    REGEX_SPLIT(key,' \(', 0) AS bundle_identifier, 
    REGEX_MATCH(key,'\((.*?)\)', 1) AS extension_id
  FROM plist JOIN human_accounts ha ON directory = '/Users/' || SPLIT(path,'/',1) 
  WHERE path LIKE '/Users/%/Library/Containers/com.apple.Safari/Data/Library/Safari/AppExtensions/Extensions.plist'
  GROUP BY key, path),
safari_raw_web AS (
  SELECT
    username, uid,
    MAX(CASE WHEN subkey = 'Enabled' THEN value END) AS enabled,
    REGEX_SPLIT(key,' \(', 0) AS bundle_identifier, 
    REGEX_MATCH(key,'\((.*?)\)', 1) AS extension_id
  FROM plist JOIN human_accounts ha ON directory = '/Users/' || SPLIT(path,'/',1) 
  WHERE path LIKE '/Users/%/Library/Containers/com.apple.Safari/Data/Library/Safari/WebExtensions/Extensions.plist'
  GROUP BY key, path),
safari_extensions_plist AS (
  SELECT username, enabled, bundle_identifier, extension_id FROM safari_raw_app UNION SELECT username, enabled, bundle_identifier, extension_id FROM safari_raw_web
)
SELECT * FROM safari_extensions_plist LEFT JOIN app_extension_pivot USING(bundle_identifier);

+---------------+---------+-----------------------------------------------+--------------+------------------------+----------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------+---------------------------------------------+
| username      | enabled | bundle_identifier                             | extension_id | extension_parent_app   | display_name                           | description                                                                                                 | bundle_short_version | bundle_version | copyright                                   |
+---------------+---------+-----------------------------------------------+--------------+------------------------+----------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------+---------------------------------------------+
| <user>        | 1       | com.parallels.desktop.console.OpenInIE        | 4C6364ACXT   | Parallels Desktop.app  | "Open In" button for Internet Explorer | Opens a web page in Internet Explorer inside Windows virtual machine powered by Parallels.                  | 18.0.0               | 53049          | Copyright 2022 Parallels International GmbH |
| <user>        | 1       | okta.ExtensionLauncher.Extension.WebExtension | B7F62B65BN   | Okta Extension App.app | Okta Browser Plugin                    | Okta Browser Plugin protects your passwords and securely logs you into all your business and personal apps. | 6.10.0               | 59             | Copyright © 2022 Okta. All rights reserved. |
+---------------+---------+-----------------------------------------------+--------------+------------------------+----------------------------------------+-------------------------------------------------------------------------------------------------------------+----------------------+----------------+---------------------------------------------+