Querying table users in Linux can cause load issues on LDAP directory servers

[lucasmrod]

Bug report

The users table is documented as "Local user accounts".
On Linux devices configured to use LDAP/AD for authentication this is not the case.
Querying the users table translates to making LDAP requests to list the whole LDAP directory.

Following is a scenario where a server sends the query SELECT * FROM users; to Linux devices.
All the devices will list the whole LDAP directory (regardless of which users have logged in to the devices) and this generate a lot of LDAP requests to the LDAP directory server.

graph TB;
    server["Osquery Server"];
    ldap_directory["LDAP directory server <br>20k users"];
    hostA["Linux Host A<br>(used by 4 users)"];
    hostB["Linux Host B<br>(used by 2 users)"];
    hostC["Linux Host C<br>(used by no users)"];

    server -- "SELECT * FROM users;" --> hostA;
    hostA -- 20k LDAP requests --> ldap_directory;
    server -- "SELECT * FROM users;" --> hostB;
    hostB -- 20k LDAP requests --> ldap_directory;
    server -- "SELECT * FROM users;" --> hostC;
    hostC -- 20k LDAP requests --> ldap_directory;

A similar behavior would happen if a "query pack" is configured to run on all devices at a given interval.

• Here's a thread from a user hitting this.
• We know of a customer that has hit this and had to remove their user queries because of this (Linux: When caching users, all LDAP users are returned, causing performance issues in LDAP directory.  fleetdm/fleet#18343):

[...] bad event happened because 160,000 hosts each requested 21,000 users every X minutes in a query_pack.

• This is also an issue due to all the SQL::selectAllFrom("users")�uses in osquery codebase that are there to allow JOINing tables to the users table.

What operating system and version are you using?

Ubuntu 22.04 / Ubuntu 24.04 (or any Linux machine with LDAP configured for authentication)

What version of osquery are you using?

5.12.2

What steps did you take to reproduce the issue?

Run SELECT * FROM users; on a linux host configured with LDAP for authentication.

What did you expect to see?

Only local users are returned.

What did you see instead?

The whole LDAP directory is returned (and the host making LDAP requests)

---

A few solutions to discuss

A. Here's PoC that explicitly uses fgetpwent_r on /etc/passwd instead of using setpwent+getpwent_r+endpwent(which are opaque and will get users from/etc/passwd`, LDAP, NSS, etc.): master...lucasmrod:osquery:poc-using-fgetpwent_r-on-etc-passwd
(PS: We could have a different table for LDAP/AD users, users would use such table with care knowing that it causes loads on LDAP directories.)

B. Instead of replacing the implementation in B, maybe reuse the type column for Linux and use fgetpwent_r on /etc/passwd if type='local'.

C. Determine which users are "local" to the device (e.g. by looking at the last table or whatever syscalls the last table uses to determine users that have logged in to the device), and then limit the LDAP requests to those accounts only. I'm all ears if there's another way to determine which accounts are "local" to a Linux device.

D. Combine solutions A + C: solution A to include local accounts defined /etc/passwd + solution C to include users that are not in /etc/passwd but that have logged to the device via LDAP (so this would just reduce the LDAP requests and not list the whole directory).

E. Add a new table to osquery local_users (maybe for Linux only?). That has the local behavior (/etc/passwd). Though some work would be needed so that it has the same semantics as the users table (which is needed for querying some tables like chrome_extensions, etc.).

[JohnSpeno]

I believe a similar issue affects these tables:

1. groups
2. shadow
3. user_groups

So we resort to parsing the local files using augeas which works, but is suboptimal.