I'm fortunate to have been in the producer's chair for somewhere around 20 recording projects. It's one of those jobs that there's really no training for, and kind of a chicken or the egg proposition; how does anyone know you're any good at it until you've done it? I suppose my first opportunity came about by having made one more record than someone who wanted to make their first.
It's a funny job, not always easy to explain, and never the same twice. In short, I'd say it's one artist asking another to help focus and execute their vision, ostensibly because artist one feels artist two has some experience or insight they'd like to bring to their recording.
On the other hand, it can be more like hiring an electrician to wire your house. It's a complicated job, which they know how to do and you don't.
As such, I've done recordings with well-established working bands that have their songs written and arranged, and are looking for an outside ear and someone to help make decisions during the recording process. In these cases, a producer can help avoid potential conflicts and make the process go much faster, and hopefully get to play tambourine on at least one song.
In other cases, I've been hired by songwriters who have a pile of songs, but no experience with recording. For these, I've helped choose and arrange a cohesive set of songs, hired the band, worked out a budget, played guitar and mandolin, and ran the sessions. Usually left the tambourine to the drummer on those.