Running Osquery As Sudo/root vs User
Understanding the importance of CROSS JOIN
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.