Home Original page

Update virtual sql functions to support multiple constraints by brian-mckinney · Pull Request #8746 · osquery/osquery

Background

  • USER_BASED tables must supply a uid constraint, or else the logged in user is used as the constraint.
  • the vscode_extensions table, and potentially others, pass this constraint to [usersFromContext](https://github.com/osquery/osquery/blob/a12cf1b59a52a9cafb4246d1adb4e34f7969e514/osquery/tables/system/system_utils.cpp#L17-L32), which makes a virtual SQL call to select all users with the supplied uid
  • SQL::selectAllFrom only supports a single constraint, in this case uid is the constraint.

Problem

  • On linux, there is a special case where remote users can only be returned by the users table if include_remote = 1 is specified as a constraint.
  • This means that on linux, if you need to select a remote user via a uid, you must supply two constraints. uid = 123 and include_remote = 1
  • This means that USER_BASED tables (like vscode_extensions) on linux are unable to select remote users, even if the correct uid is supplied, because usersFromContext will ignore the second constraint.
  • The obvious solution was to modify usersFromContext to support the include_remote constraint. However, this was hindered by the fact, that SQL::selectAllFrom only supported a single constraint.

Solution

  • This PR adds a new overload function to SQL::selectFrom that can support multiple constraints.
  • A small amount of refactoring was done, to make the existing functions use the new function, in order to reduce code duplication
  • A test was added to the sql tests
  • usersFromContext was modified to support include_remote transparently
    • if a uid is supplied, we make the assumption that the user exists
    • We attempt first to select from the users table without include_remote. If no results are returned, we will try again with include_remote=1, but only on linux.
    • We do not attempt to include remote users in the first check, because LDAP queries may still happen depending on the search order of sssd.conf on the host system, which we do not control.

Manual Testing

Setup

I set up a docker stack that implemented open ldap and created a remote user named alice and pre-populated the home directory with vscode extensions

root@d5ecad67c1bd:/# getent passwd alice
alice:*:10001:10001:alice:/home/alice:/bin/bash
root@d5ecad67c1bd:/# grep alice /etc/passwd
root@d5ecad67c1bd:/#
osquery> select * from users where uid = 10001;
osquery> select * from users where uid = 10001 and include_remote=1;
+-------+-------+------------+------------+----------+-------------+-------------+-----------+------+
| uid   | gid   | uid_signed | gid_signed | username | description | directory   | shell     | uuid |
+-------+-------+------------+------------+----------+-------------+-------------+-----------+------+
| 10001 | 10001 | 10001      | 10001      | alice    | alice       | /home/alice | /bin/bash |      |
+-------+-------+------------+------------+----------+-------------+-------------+-----------+------+

Build from master - Validate vscode_extensions cannot see remote users

direct query

osquery> SELECT uid, name, publisher
    ...> FROM vscode_extensions
    ...> WHERE uid = 10001
    ...> LIMIT 5;
osquery>

subquery attempt

osquery> SELECT uid, name, publisher
    ...> FROM vscode_extensions
    ...> WHERE uid in (SELECT uid FROM users WHERE include_remote = 1)
    ...> LIMIT 5;
osquery>

join attempt

osquery> SELECT users.uid, username, name, publisher
    ...> FROM vscode_extensions
    ...> JOIN users
    ...>   ON users.uid = vscode_extensions.uid
    ...> WHERE users.uid in (SELECT uid FROM users WHERE include_remote = 1)
    ...> AND users.include_remote = 1
    ...> LIMIT 5;
osquery>

Build from this PR - validate vscode_extensions can see remote users

direct query

osquery> SELECT uid, name, publisher
    ...> FROM vscode_extensions
    ...> WHERE uid = 10001
    ...> LIMIT 5;
+-------+-----------------------------------+-----------+
| uid   | name                              | publisher |
+-------+-----------------------------------+-----------+
| 10001 | github.copilot-chat               | GitHub    |
| 10001 | github.copilot                    | GitHub    |
| 10001 | ms-vscode.cpptools-extension-pack | Microsoft |
| 10001 | ms-vscode.cpptools-themes         | Microsoft |
| 10001 | ms-vscode.cmake-tools             | Microsoft |
+-------+-----------------------------------+-----------+

subquery example

osquery> SELECT uid, name, publisher
    ...> FROM vscode_extensions
    ...> WHERE uid in (SELECT uid FROM users WHERE include_remote = 1)
    ...> LIMIT 5;
+-------+-----------------------------------+-----------+
| uid   | name                              | publisher |
+-------+-----------------------------------+-----------+
| 10001 | github.copilot-chat               | GitHub    |
| 10001 | github.copilot                    | GitHub    |
| 10001 | ms-vscode.cpptools-extension-pack | Microsoft |
| 10001 | ms-vscode.cpptools-themes         | Microsoft |
| 10001 | ms-vscode.cmake-tools             | Microsoft |
+-------+-----------------------------------+-----------+
osquery>

join example

osquery> SELECT users.uid, username, name, publisher
    ...> FROM vscode_extensions
    ...> JOIN users
    ...>   ON users.uid = vscode_extensions.uid
    ...> WHERE users.uid in (SELECT uid FROM users WHERE include_remote = 1)
    ...>   AND users.include_remote = 1
    ...> LIMIT 5;
+-------+----------+-----------------------------------+-----------+
| uid   | username | name                              | publisher |
+-------+----------+-----------------------------------+-----------+
| 10001 | alice    | github.copilot-chat               | GitHub    |
| 10001 | alice    | github.copilot                    | GitHub    |
| 10001 | alice    | ms-vscode.cpptools-extension-pack | Microsoft |
| 10001 | alice    | ms-vscode.cpptools-themes         | Microsoft |
| 10001 | alice    | ms-vscode.cmake-tools             | Microsoft |
+-------+----------+-----------------------------------+-----------+
osquery>