Consider the Hilbert space $\mathcal H = L^2(\mathbb R)$ - roughly, the set of square integrable functions on $\mathbb R$. The momentum operator $P$ is a linear map
$$ P : \mathcal D_P \rightarrow \mathcal H$$
$$ \psi \mapsto -i \psi'$$
where $\mathcal D_P \subseteq \mathcal H$ is some densely-defined domain. First question - what is the appropriate choice for $\mathcal D_P$?
At a bare minimum we must have that if $\psi\in \mathcal D_P$, then $-i\psi' \in \mathcal H$. Therefore, a reasonable guess would be
$$\mathcal D_P := \{ \psi \in \mathcal H \ | \ \psi\in C^1(\mathbb R) , \psi' \in \mathcal H\}$$
where $C^1(\mathbb R)$ denotes the once differentiable functions on $\mathbb R$. We can now ask whether this $P$ is hermitian - that is, for any $\psi,\phi \in \mathcal D_P$, $\langle \psi,P\phi\rangle = \langle P\psi,\phi\rangle$.
$$\int dx \ \psi^*(x) \cdot (-i \phi'(x)) = -i\int dx \ \psi^*(x) \phi'(x) = -i \left[\left.\psi^*(x)\phi(x)\right|^\infty_{-\infty} - \int dx \ (\psi'(x))^* \phi(x)\right]$$
Now, it would be great if the first term in square brackets would vanish. You're right that it's perfectly possible to have square integrable functions which don't vanish at infinity (e.g. a rectangular "pulse train" with constant height but rapidly decreasing width). However, for all $\psi\in\mathcal D_P$ we made the additional demand that $\psi' \in \mathcal H$. This implies that $\psi(\pm \infty)=0$.
Note that
$$\int_a^b dx \ \psi(x) \psi'(x) = \frac{1}{2}\int_a^b dx \ \frac{d}{dx}\left(\psi^2(x)\right) = \frac{1}{2}\left(\psi^2(b)-\psi^2(a)\right)$$
From the Cauchy-Schwarz inequality,
$$\left|\int_a^b dx \ \psi(x) \psi'(x)\right|^2 = \frac{1}{4}\left|\psi^2(b)-\psi^2(a)\right|^2 \leq \left(\int_a^b dx \left|\psi(x)\right|^2\right)\cdot \left(\int_a^b dx \ \left|\psi'(x) \right|^2\right)$$
Because both $\psi$ and $\psi'$ are in $L^2(\mathbb R)$, it follows that if we let $a$ and $b$ both go to infinity, the integrals written above all vanish. This implies that
$$\psi^2(b)-\psi^2(a) \rightarrow 0 \text{ as } a,b\rightarrow \infty $$
Since $a$ and $b$ are independent, this means that $\lim_{x\rightarrow \infty} \psi(x) = C$ for some constant $C$; because $\psi\in L^2(\mathbb R)$, we must have that $C=0$. The same reasoning applies to $\psi(-\infty)$.
From here, the remainder of the analysis follows as usual, which demonstrates that $P$ is indeed hermitian on this domain.
The lesson to take away from this is that the domain of an operator is an absolutely essential part of its definition. Two operators which have the same "formula" (e.g. $P_1\psi = -i\psi', P_2\psi = -i\psi'$) but act on different domains are different operators. The domain of an operator must be chosen carefully if we want the operator to have certain properties, such as self-adjointness.
While we're on the subject, the fact that $P$ is hermitian does not imply that $P$ is self-adjoint. It is certainly a necessary condition, but it is not sufficient.
Given some operator $P$ with a densely-defined domain $D_P\subseteq \mathcal H$, the definition of the adjoint operator $P^\dagger$ goes as follows:
Let $\mathcal D_{P^\dagger}$ be the set of all functions $\eta\in\mathcal H$ such that, for all $\psi\in \mathcal D_P$, there exists some $\phi\in\mathcal H$ such that $\langle \eta, P\psi\rangle = \langle \phi,\psi\rangle$. We then define $P^\dagger \eta = \phi$.
If you are not familiar with this definition, then it may take some time to understand. However, the main takeaway is that self-adjointness is usually extremely tedious and difficult to show. A self-adjoint operator is necessarily hermitian, but the reverse is not true - one needs to additionally show that $\mathcal D_{P^\dagger} = \mathcal D_P$ and this is not nearly as straightforward as it may sound. It's not too hard to show that $\mathcal D_P \subseteq \mathcal D_{P^\dagger}$, but the latter domain is generically bigger.
