Triplicate responses in `managed_policies` table

[zhumo]

Fleet version: 4.21

Operating system: MacOS 12.6

Web browser: Chrome 105.0.5195.125

osquery: 5.5.1

---

🧑‍💻  Expected behavior

I should see one row for each profile for each user in my settings. A global context should also exist with a blank username attribute.

💥  Actual behavior

I see 3 of each row.

The query I ran was SELECT * FROM managed_policies

More info

[xpkoala]

@zhumo Was this run on Dogfood? I can't run the query on your machine since it is currently offline. I am not able to reproduce when checking another device on dogfood or my personal device on a local instance.

[zhumo]

@xpkoala I ran this in Dogfood yes. I'm online now... feel free to try again. Also, I'm very sorry, late afternoon bug report... it was not the keychain_items table, it was the managed_policies table. oops!

[zwass]

@zhumo to verify this is an agent issue (and not some problem with the Fleet server or UI), can you please run the query locally via sudo osqueryi 'SELECT * FROM managed_policies'?

This would be a great step to take any time we see a similar issue to help isolate which component it's coming from.

[zwass]

I wonder if your device's MDM (which seems to be different from other Fleet devices, perhaps due to some testing you were doing?) actually applied policies in triplicate? On Fleet's SimpleMDM managed devices, I didn't see duplication in the results from that table via live query.

[zhumo]

We also saw this on Charlie Chance’s Mac today. Though hers had duplicates not triplicates.

On Fri, Oct 7, 2022 at 2:22 PM Zach Wasserman @.***> wrote:

I wonder if your device's MDM (which seems to be different from other Fleet devices, perhaps due to some testing you were doing?) actually applied policies in triplicate? On Fleet's SimpleMDM managed devices, I didn't see duplication in the results from that table via live query.

— Reply to this email directly, view it on GitHub https://github.com/fleetdm/fleet/issues/8119#issuecomment-1272096716, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABI4PYSNNJHABMHICGL7GETWCCIA5ANCNFSM6AAAAAAQ7EAKMM . You are receiving this because you were mentioned.Message ID: @.***>

--

Mo Zhu

Product Manager

fleetdm.com http://www.fleetdm.com | @fleetctl https://twitter.com/fleetctl

[zhumo]

@zwass I ran this just now against everyone's macbooks. 5 responded... Saw duplicates...

[zhumo]

@zwass i don't have osqueryi immediately available on my machine. I think it's hidden behind a bunch of namespaces. How do I access it?

[zayhanlon]

@xpkoala Can you reproduce this?

[xpkoala]

@zayhanlon Sorry this took so long, dealing with a myriad of issues today. Looks like this is reproducible on osqueryi as well as via Fleet.

osquery> select * from managed_policies;
+-----------------------------+--------------------------------------+----------------+-------+----------+--------+
| domain                      | uuid                                 | name           | value | username | manual |
+-----------------------------+--------------------------------------+----------------+-------+----------+--------+
| com.apple.security.firewall | D12965C1-12BD-4CAD-A55A-E7F020B0DAAF | EnableFirewall | 1     |          | 1      |
| com.apple.security.firewall | D12965C1-12BD-4CAD-A55A-E7F020B0DAAF | EnableFirewall | 1     | .        | 1      |
+-----------------------------+--------------------------------------+----------------+-------+----------+--------+

[zayhanlon]

@xpkoala Thank you!

[zayhanlon]

@lucasmrod - Investigate: how long for us to fix ourselves?

[lucasmrod]

The "duplicate/triplicate" responses are actually due to managed_policies returning device level profiles and user level profiles. It's just that there's a bug in the managed_policies table that returns a dot . instead of the correct username (If you look hard at the screen captures you can see a dot . in the username column; and the entries that have empty username are device level profiles).

The . issue is fixed by this osquery PR: https://github.com/osquery/osquery/pull/7944. So, once that fix is merged, then this issue will be fixed.

[lucasmrod]

For the purposes of Fleet's macOS CIS policies we care about device level profiles, so we should add a username = '' to all the queries that use managed_policies.

[zwass]

Thank you for the research, Lucas. I merged the PR given your confirmation.

[zhumo]

So this would be resolved via #10602? If so, we should close this?

[lucasmrod]

1. The triplicate responses are expected (one row for system-wide and one row for each user).

2. The mentioned osquery bug is the username being . instead of the actual username (will be fixed in 5.9.0).

3. The query I ran was SELECT * FROM managed_policies

The query to get system-wide preferences should be SELECT * FROM managed_policies WHERE username = ''

So, IMO yes, this issue can be closed.

[zhumo]

Thanks, I'm going to put this into ready for QA. FYI @xpkoala... This behavior is supposed to be fixed... going forward, the expected behavior is that there should be one row for each user and one row with blank "username" attribute for the global context. Please retest when the fixed osquery comes out.

[zwass]

This made it into osquery 5.8.1 which is on stable, so we should be ready for testing.

[lucasmrod]

This made it into osquery 5.8.1 which is on stable, so we should be ready for testing.

Did it? Don't see it. https://github.com/osquery/osquery/releases/tag/5.8.1 (seems it was merged after the 5.8.1 release https://github.com/osquery/osquery/compare/5.8.1...master)

[xpkoala]

Here is what I'm seeing on my local instance which looks like the same behavior we were seeing before (I am on osquery 5.8.1). I'm guessing the fix did not make it into osquery 5.8.1 as @lucasmrod mentioned.

[zwass]

Ah yes seems you two are correct :/ I'd thought I merged it before the release was tagged, but possibly that change was not yet pulled into the branch Seph used.

The good news is that it is actually in 5.8.2 which is currently on edge and I'll push to stable now.

[xpkoala]

Looks good!

[fleet-release]

Triplicate cleared, Managed policies now shine, One row, order thrives.