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
Not true. Choose $p_n(x) := (x-1)x^n$ that vanishes on the boundary of $[0, 1]$ for all $n \in \mathbb{N}$. Then $$ \lVert p_n \rVert_{L^2([0, 1])}^2 = \dfrac{1}{4n^3+12n^2+11n+3}, \quad \lVert p_n' \rVert_{L^2([0, 1])}^2 = \dfrac{n}{4n^2-1}. $$ Observe: $$ \frac{\lVert p_n' \rVert_{L^2([0, 1])}^2}{\lVert p_n \rVert_{L^2([0, 1])}^2} \in O(n^2) $$
So this quotient goes to infinity as $n \rightarrow \infty$ which means that such constant $C$ cannot exist.
But note: If you restrict the degree of the polynomials to some number $N \in \mathbb{N}$, then such constant $C$ exists. This is because the space $P$ of polynomials with degree $\leq N$ that vanishes on the boundary is a finite dimensional space - which makes $\nabla : P\rightarrow P$ a linear continuous map.