John has already given a nice intuitive answer, I'd like to write it in a bit more mathematical way. First of all metric means a bilinear defined over a product (vector) space: $g(v,w): V \times V \rightarrow \mathbb{R}$. Where the vectors $v,w$ lie in the vector space $V$. This bilinear should be smooth, symmetric $g(v,w) = g(w,v)$, and positive definite , which means for any $v,w$ it should be positive. This allows one to measure length of the vectors in $V$. And now if we weaken the condition of positive definiteness to 'non-degenrate' then we call it a pseudo-Riemannian metric. Non-degenerate means $g(v,w)=0, \forall w \in V \Rightarrow v = 0$.
The reason why in physics we usually don't go into such language is the following fact: metric is a bilinear so you can always construct the $g(v,w)$ for any pair of vectors if you know what $g(e^\mu,e^\nu)$ are, where $\{ e^\mu \}$ is a basis of the space $V$. This is nothing but the usual $g^{\mu \nu}$. For instance, take $\mathbb{R}^2$ as our $V$, you can define a metric $g^{\mu\nu} = \text{diag}(1,1)$, which is a valid (Riemannian) metric and means nothing but $g(\hat{x}, \hat{x}) = 1 = g(\hat{y}, \hat{y})$. You can check with such a metric (as John pointed out) you will always get $ds^2 > 0$.
If we try to think all these at the level of matrix $g^{\mu \nu}$ it is important to note (Gram-Schmidt theorem) at least locally one can always find a suitable orthonormal basis such that $$g^{\mu \nu} = \eta^{\mu \nu} = \text{diag} \big(-1, \cdots (s \,\, \text{times}), +1, \cdots (r \,\, \text{times}) \big) $$
Hence Riemannian metric would mean $s=0$ (because we want positive definite) and pseduo-Riemannian metric will have $s \neq 0$. In fact the 'signature' $(r,s)$ is always sufficient to tell apart between different metrics, no matter what basis you choose (Sylvester's law of inertia). For example metric which have a signature $(\text{dim}(V)-1,1)$ are called Lorentzian metrics. (Some people might exchange $r$ and $s$).
As of yet I've not talked about manifolds, be it Riemannian or pseudo-Riemannian. We can endow a 'metric structure' to a manifold also. The vector space over which we define the metric is $T_pM$, the tangent space of manifold $M$ at point $p$ on it. In fact we usually define it over the co-tangent space $T^{^*}_pM$ (which has the basis $\{dx^\mu\}$) and that's why we write it as $ds^2 = g_{\mu \nu} dx^\mu dx^\nu = g(dx^\mu, dx^\nu)$. So if you can write a (pseudo-) Riemannian metric on $T_pM$ then $M$ is said to be a (pseudo-) Riemannian manifold.
Hope it might be clear that in special relativity we restrict ourselves only to Lorentzian metrics, but in general relativity we consider all possible pseudo-Riemannian metrics (such as an anti-de Sitter metric has $s=2$).
