Running Osquery As Sudo/root vs User

When using osquery (osqueryi or osqueryd) you have to keep in mind whether you are running it in user-space or as sudo/root. By default, vanilla osquery will run as the local user, which means that certain queries will automatically return results for the current logged in user.

An example of this behavior can be seen using osqueryi to query the chrome_extensions table which returns different results if it is run with sudo versus without.

—

Running a query against chrome_extensions without sudo:

➜  ~ osqueryi
Using a virtual database. Need help, type '.help'
osquery> .mode line
osquery> select * from chrome_extensions LIMIT 1;
uid = 502
name = Slides
identifier = aapocclcgogkmnckokdopfmhonfmgoek
version = 0.10
description = Create and edit presentations
locale = en_US
update_url = https://clients2.google.com/service/update2/crx
author =
persistent = 0
path = /Users/fritz-imac/Library/Application Support/Google/Chrome/Default/Extensions/aapocclcgogkmnckokdopfmhonfmgoek/0.10_0/

—

The same query run as sudo will return an error:

➜  ~ sudo osqueryi
Password:
Using a **virtual database**. Need help, type '.help'
osquery> select * from chrome_extensions LIMIT 1;
W0927 16:26:48.358438 119010752 virtual_table.cpp:987] The chrome_extensions table returns data based on the current user by default, consider JOINing against the users table
W0927 16:26:48.358495 119010752 virtual_table.cpp:1002] Please see the table documentation:https://osquery.io/schema/#chrome_extensions
osquery>

—

As can be seen from the error output above, when not in standard user space, the chrome_extensions table like several others (eg. preferences, firefox_addons, plist, etc.), requires you to supply a user in the WHERE clause.

How does this impact osquery solutions like Kolide?

When using Kolide Cloud or Fleet, all queries are run as root by default. If you want to run a query or pack against multiple devices and the query includes a table which relies upon supplying a user, you will need to choose one of the following methods based on your specific use-case (queries are intended to be used on macOS devices):

CROSS JOIN — Recommended Multi-user approach

The error produced above mentions: consider JOINing against the users table. However, if we attempt to run our query with a standard JOIN we will encounter the same error as before:

osquery> SELECT * FROM chrome_extensions JOIN users USING(uid);
W1110 10:40:58.305212 236953088 virtual_table.cpp:961] The chrome_extensions table returns data based on the current user by default, consider JOINing against the users table
W1110 10:40:58.305227 236953088 virtual_table.cpp:976] Please see the table documentation: https://osquery.io/schema/#chrome_extensions

This is occurring because the SQLite engine is attempting to query the chrome_extensions table before it queries the users table. In order to ensure the tables are called in the order we desire, we need to use a CROSS JOIN and select from our users table first like so:

CROSS JOIN‘ing the users table:

SELECT
  u.username,
  ce.*
FROM users u
CROSS JOIN chrome_extensions ce
USING (uid);

In SQLite, CROSS JOIN allows us to explicitly specify the order the SQLite engine queries each table. In the example above, SQLite will first enumerate all the users, and then attempt to join them with chrome_extensions.

Alternative methods that also work

• Utilizing a subquery that returns results only for a logged_in_user

SELECT *
FROM chrome_extensions
WHERE uid = (
  SELECT u.uid
  FROM users u
  JOIN logged_in_users liu
  WHERE liu.user = u.username
    AND liu.tty = 'console');

- Utilizing a subquery to look for all human user accounts:

SELECT *
FROM chrome_extensions
WHERE uid = (
  SELECT uid
  FROM users
  WHERE gid IN (20, 201)
     OR directory LIKE '/Users/%');

- Utilizing a subquery that looks for the most frequent (primary) user of the device by the number of logon sessions:

SELECT * FROM chrome_extensions WHERE uid =
(WITH most_frequent_console_user AS (
    SELECT
        username,
        count(username) AS occurrence
    FROM
        LAST
    WHERE
        tty = 'console'
    GROUP BY
        username
    ORDER BY
        occurrence DESC
    LIMIT 1
)
SELECT
    u.uid
FROM
    most_frequent_console_user
    JOIN users u ON u.username = most_frequent_console_user.username)

Expanding these concepts

Understanding how to dynamically supply values required in the WHERE clause pays dividends when using osquery. The same concepts of supplying a uid from the users table can be adapted when querying items requiring path such as the file table in the example of our mdfind blog post: Spotlight search across every Mac in your fleet with Osquery.

If you’d like to read more osquery content like this, sign up for our biweekly newsletter.