This is my favourite part of my job.
Producing can mean a bunch of different things. The three main kinds of producers that people usually talk about are:
#1 is what I like to call “George Martin style”. It usually consists of the artist/band bringing the guts of a song to the studio and asking the producer to add/subtract elements. This could mean adding a string or horn section, or suggesting that maybe repeating the 2nd chorus eight times isn't all that necessary. The product of working like this can either be really subtle or totally over the top depending on the song/artist.
#2 is what I like to call “Steve Albini style”. Sometimes a band already has their sound sorted and just wants to capture it 'as is'. This is when you need someone that can just record great sounds and knows their way around microphone placement.
#3 is what I like to call “knowing when to stop style”. Sometimes when recording, It's hard to see the forest from the trees. I've definitely had this happen to me while recording my own music. It can be difficult to know when you've nailed a drum take, or how you should sing the 3rd verse, or if the guitar solo should be just regular distorted or blistering distorted. It can be invaluable to have an outside opinion in these situations. I find it lets you know that you're on the right track and can get rid of that feeling of insecurity that comes from having all of the worlds options at your disposal.