Your description of the six functors does not mention any relations between the $!$-functors and the $*$-functors or the tensor product, which is where these dualities are hiding.
Poincaré duality is a relation between the $*$-functors and the $!$-functors. Typically there are canonical isomorphisms $f_!=f_*$ when $f$ is proper and $f^!=f^*[d]$ when $f$ is nice (e.g. smooth) of relative dimension $d$. Depending on how the $!$-functors are constructed, one of these isomorphisms will hold by definition but the other one will be nontrivial to prove. When $f:X\to S$ is nice, we get isomorphisms (where $1_S$ is the unit object in $D(S)$)
$$
H^n(X,M) := \mathrm{hom}(1_S, f_*f^*(M)[n]) = \mathrm{hom}(1_S,f_*f^!(M)[n-d])=:H_{d-n}^\mathrm{BM}(X,M)
$$
and
$$
H^n_c(X,M):= \mathrm{hom}(1_S, f_!f^*(M)[n]) = \mathrm{hom}(1_S,f_!f^!(M)[n-d])=:H_{d-n}(X,M),
$$
On the other hand, there are vertical identifications when $f$ is proper.
Serre duality uses a relation between the $!$-functors and the tensor product. Namely, for $f:X\to S$, the functor $f_!$ is $D(S)$-linear; in particular we have the projection formula $f_!(A\otimes f^*(B))= f_!(A)\otimes B$. By adjunction this implies the isomorphism
$$
f_*\mathrm{Hom}(A, f^!(B)) = \mathrm{Hom}(f_!(A),B).
$$
One gets Serre duality when $f$ is proper (so $f_!=f_*$) and $B=1_S$ (so $f^!(B)$ is the "dualizing sheaf"). Combining the projection formula with Poincaré duality we can also deduce that $f_*$ preserves $\otimes$-dualizable objects when $f$ is both proper and nice, which gives a perfect pairing. But the usual formulation of Serre duality in terms of cohomology is specific to the derived category of quasi-coherent sheaves when $S=\mathrm{Spec}(k)$ is a field (note that in the quasi-coherent case the adjunction $(f_!,f^!)$ only exists when $f$ is proper).
There are further duality statements, such as Verdier duality, which says that $\mathrm{Hom}(-,f^!(1_S))$ is an equivalence of a certain subcategory $D_c(X)\subset D(X)$ of "constructible"/"coherent" objects with its opposite. But unlike the previous two, this statement does not directly follow from the various relations between the six functors.
They do not exist in general. The simplest example is maybe to take $X\rightarrow Y$ to be the closed embedding of the origin inside $\mathbb{A}^1.$ Then $f_*$ sends a vector space $V$ to the $\mathcal{D}_{\mathbb{A}^1}$-module $V\otimes\delta_0$, where $\delta_0$ is the irreducible $\mathcal{D}_{\mathbb{A}^1}$-module set-theoretically supported at $0$. The point is that because $\delta_0$ is infinite-dimensional as a vector space, this functor does not commute with products and hence cannot have a left adjoint.
Best Answer
There may be some confusion in this question about what exactly Voevodsky/Ayoub and Mann do, as they do very different things.
Note also that the definition of a formalism of a six operations in Mann's thesis is far from capturing everything. A more complete definition would be a right-lax symmetric monoidal functor from an $(\infty,2)$-category in which both the 1-morphisms and the 2-morphisms are spans (Gaitsgory and Rozenblyum work with an intermediate 2-category, probably for simplicity's sake, in which the 1-morphisms are spans and the 2-morphisms are just morphisms). These 2-morphisms encode the functoriality of bivariant homology, i.e., the isomorphisms $f_!\simeq f_*$ for $f$ proper and $f^!\simeq f^*$ for $f$ étale. But even this does not capture all the structure that we have in the examples, such as the contravariance of bivariant homology with respect to quasi-smooth morphisms.