As Mike Shulman pointed out, this is the same as requiring the geometric morphism induced by $f$ to be atomic. In Atomic toposes 7.2 you'll find that in the case of $! = F \colon C \to 1$, $F^*$ is logical iff $C$ is a groupoid.
What about the general case? Not an answer in any way, but two possible paths:
- Atomic morphisms are locally connected (Elephant C3.5). What do we know about functors for which the induced geometric morphism is locally connected? looking at the Elephant again, Lemma C3.3.5 which gives a sufficient condition, and On functors which are lax epimorphisms, which characterizes connected ones, one is tempted to conjecture that the induced geometric morphism is locally connected iff it is absolutely dense into its Cauchy-image, or something like that.
- In the spirit of Simon Henry comment above, and given the characterization of atomic sites in Atomic toposes 7.3, or C3.5.8 in the Elephant, maybe it is enough for $F$ to be a fibration in categories where (i) every morphism is an effective epi and (ii) every pair of morphisms with common codomain can be completed to a commutative square (right Ore condition); note that a groupoid obviously satisfies these two conditions.