For the sake of this question, we'll model a six functor formalism in the following way. Let $\mathsf{C}$ be a category *of spaces* (be it the category of schemes, or topological spaces) and consider a triangulated closed symmetric monoidal category $\mathsf{D}(X)$, with identity $\mathscr{O}\_X$, for each $X\in\mathsf{C}$.


Given a morphism $f:X\to Y$ in $\mathsf{C}$, we have functors $f\_\*,f\_!:\mathsf{D}(X)\leftrightarrows \mathsf{D}(Y):f^\*,f^!$ such that $f^\*\dashv f\_\*$, $f\_!\dashv f^!$, and $f^\*$ is strong symmetric monoidal.


If we also suppose that $\mathsf{C}$ has a final object $S$, it is natural to define cohomology and cohomology with compact support to be
$$H^i(X,M):=\hom\_{\mathsf{D}(S)}(\mathscr{O}\_S,p\_\*M[i])\qquad\text{and}\qquad H\_c^i(X,M):=\hom\_{\mathsf{D}(S)}(\mathscr{O}\_S,p\_!M[i]),$$
where $p:X\to S$ is the natural morphism. If $\mathsf{C}$ is the category of ringed spaces (or even of ringed sites, I think), this coincides with the usual definitions.


**I wonder then how could we obtain some sort of duality isomorphism similar to Poincaré and Serre duality.** Perhaps we could begin with
$$\operatorname{Ext}^i(M,p^!\mathscr{O}\_S)=\hom\_{\mathsf{D}(X)}(M,p^!\mathscr{O}\_S[i])=\hom\_{\mathsf{D}(S)}(p\_!M[-i],\mathscr{O}\_S)$$
and the latter should be something like $H^{-i}\_c(X,M)^\vee$, but I'm not sure how to make this precise.



How duality follows from a six functor formalism
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.


