I am not aware of a characterisation of domains supporting the Poincaré inequality of the above type, but there are several interesting results in this direction.
(1) We say $\Omega$ satisfies the cone condition, which asserts that there is a finite cone $C$ of the form $\{x \in \Bbb R^n : x_1^2+\cdot x_{n-1}^2 \leq bx_n^2, 0 < x_n < a\}$ for constants $a,b>0$ such that for all $x \in \Omega,$ there is a cone $C_x$ congruent to $C$ (i.e. obtained by translating and rotating) with vertex at $x,$ such that $C_x \subset \Omega.$
It is a classical result that the cone condition implies the Poincaré inequality of the above form. This is proved for instance in Sobolev Spaces by Adams and Fournier (section 6 via Rellich-Kondrachov), and in Sobolev Spaces by Maz'ya (section 1.1.11 via local representation formulae). It's worth noting that Lipschitz domains do satisfy the cone condition, but this is more general as it includes punctured domains also (e.g. the unit ball with the origin removed).
(2) The above is a special case of the more general result, which asserts that the Poincaré-Sobolev inequality
$$\left( \int_{\Omega} |u-u_{\Omega}|^{\frac{np}{n+p}} \,\mathrm{d} x \right)^{\frac{n+p}{np}} \leq C \left( \int_{\Omega} |\nabla u|^p \,\mathrm{d} x \right)^{\frac1p} $$
holds if $\Omega$ is a John domain; I'll omit the precise definition but by a result of Martio these are domains where the cone is replaced by images of balls by Lipschitz mappings which are of "fixed size." For a detailed discussion see the paper:
Bojarski, B., Remarks on Sobolev imbedding inequalities, Complex analysis, Proc. 13th Rolf Nevanlinna-Colloq., Joensuu/Finl. 1987, Lect. Notes Math. 1351, 52-77 (1988). ZBL0662.46037.
Moreover a partial converse was proved by Buckley and Koskela, asserting that under mild topological conditions (which holds if $\Omega$ is simply connected), $\Omega$ must be a John domain to satisfy the above Poincaré-Sobolev inequality. The reference is below.
Buckley, S.; Koskela, P., Sobolev-Poincaré implies John, Math. Res. Lett. 2, No. 5, 577-593 (1995). ZBL0847.30012.
This precisely captures the regularity of the boundary; note that John domains are weaker than Lipschitz, but it shows that Hölder regularity is not sufficient - it is known that Sobolev embedding fails for domains with Hölder continuous boundary (see for instance 8.7 in Analysis by Lieb and Loss), and the above refines this fact.
(3) Going back to the Poincaré inequality it is known that you need some regularity; in Section 1.1.4 of Mazy'a's book, it is shown that the inequality fails for a domain constructed by Nikodym. Here a function $u$ is furnished such that $\nabla u \in L^2(\Omega)$ but $u \not\in L^2(\Omega),$ but the example is easy to modify to disprove the validity of an estimate of the above type.
However I am not aware of further results in either direction; whether you can relax the John domain assumption (I imagine there are easy examples that say you can, but general results?), or if there are nicer domains (e.g. domains with Hölder continuous boundary) where the inequality fails - if anyone knows any references in this direction please let me know. I hope my answer does give some insight into results of this type however, and that it is a fairly involved problem.
Best Answer
The natural form of Poincaré inequality is $$\int_\Omega |f-f_\Omega|^2 \le C\int_\Omega |\nabla f|^2\tag1$$ where $f_\Omega=\int_\Omega f$ is the mean of $f$. This is exactly your first inequality, but I think (1) captures the meaning better. The weighted Poincaré inequality would be $$\int_\Omega |f-f_{\Omega, w}|^2w \le C'\int_\Omega |\nabla f|^2w\tag2$$ where $f_{\Omega,w}=\int_\Omega fw$ is the weighted mean of $f$. Again, this is what you have but written in a more natural way.
The industry of weighted Poincaré inequalities is huge, but the most fundamental result is that the Muckenhoupt condition $w\in A_2$ is sufficient for (2). This is proved in detail, e.g., in Chapter 15 of Nonlinear Potential Theory of Degenerate Elliptic Equations by Heinonen, Kilpeläinen, and Martio (now published by Dover,
$10). The constant $C'$ of course depends on the $A_2$ norm of $w$, but not in any explicit way. There has been some recent interest in estimates of the form $C'\le c\|w\|_{A_2}$ with weight-independent constant $c$, but I do not know if this particular one has been proved.Anyway, the assumptions you stated are not enough for (2) to hold. Let $f(x)=\min(M, \log\log (e+|x|^{-1}))$ where $M$ is a large number to be chosen later. Spread one half of available weight uniformly on the square, and put the other half onto the set where $f=M$. Since on most of the square $f$ is much smaller than $M$, the weighted mean of $f$ is about $M/2$. Hence, the left hand side of (2) is of order $M$. But the right hand side of (2) is bounded independently of $M$.