A mattress that is too hard places unwanted pressure on areas like your lower back, causing discomfort, whereas mattresses that are too soft do not provide enough support and hence cause unhealthy sleeping positions.
Generally, a mattress that is leaning towards medium-firm is preferred by people with lower back pain as it supports the spine while still allowing the hips and shoulders to sink in a little.
Your body type and sleeping position play a part too. Stomach and back sleepers may be better with a firmer mattress to stop their back from arching, while side sleepers often prefer a slightly softer mattress that allows more comfort for their pressure points. Also, as a general rule, lighter people need softer mattresses while heavier people benefit from firmer mattresses.