Grain is a disruptive factor in a photo. The picture becomes less accurate. And so grain irons out imperfections (by covering them up), so to speak. The tendency towards perfection, which we almost always strive for, cannot be fulfilled by us in every situation in everyday life. And therefore imperfections makes us feel comfortable, I assume.
patina — a thin layer that develops on a surface because of age or use
On the other hand it is surely because we got to know grainy photographs in our childhood. So it's a reminder of the past. We often romanticize youth. Grain takes us back to this forgotten time. Just like our memories and thoughts, grain makes a photo less clear. Grain awakens the feeling of nostalgia in us.
Perhaps you can also compare grain to the cracking of records when listening to music. I hope not to have lost the younger generation among my readers now. Records were round black discs on which acoustic signals were played with the help of a needle. If there was a bit of dust or wear and tear from playing too often, it crackled easily. And this sound feels warm. Like a log fire. That’s definitely romantic.
And grain in a photo may be just that: a little romantic. It takes away perfection and it makes a photo feel more personable. Like in the good old days.
There are photographers who work digitally, but who still add grain to all of their photos in post production. I've done this a few times as well — and maybe in a bad way because I added the grain uniformly which again looks too perfect. I admit, I have a hard time with grain in my own images.
You're creating a well thought-out exposure and color scheme and then decorating the picture with grain feels somehow wrong to me. Does it make sense to fake a film look? Is it cool to simulate something to create a nostalgia feeling? I like the real grain of film, though. Not always. But sometimes.
I also find it technically difficult to add grain in post. Because you already need to know the final display size to make the grain look realistic. And you need to decide what size the grainy dots should have. And how many do you want to add?