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 (UPDATE 2. went nowhere, added 3.) 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. UPDATE not the case, see comment below
- Look at its hyperconnected-localic factorization, which in this case coincides with the connected-light factorization (or "comprehensive") of a locally-connected morphism. For essential geometric morphisms between presheaf categories, this yields the comprehensive factorization of a functor into a final functor followed by a discrete fibration; Factorization theorems for geometric morphisms II is a reference (at the end of the paper). Now, a geometric morphism is atomic iff both halves of this factorization are atomic (Elephant C3.5.4), so we have reduced it to when both the final and the discrete fibration components induce an atomic morphism.