Key to understanding this is the flow rate of the hot water: Fine grains = slow flow rate; coarse grains = fast flow rate.
Grain size determines how quickly the hot water flows, and thus how much time it is contact with the grains. Faster flow means the water has less time to be in contact with the grains and thus less time the water can extract the oils and other nutrients. (Certainly, surface area factors in, too; cf. Stephie's answer.)
Methods where the grains are soaked (cold-brew, French press, pour-over) don't require fine grains. One could use fine grains in such methods (as in the Turkish method), but it's not necessary, and they would be harder to filter out.
Methods where water quickly flows by the grains also extract more quickly, because of higher temperature and pressure.
Grain Size Flow Rate Extraction Time Methods
⬤ • ⬤ Cold brew, French press, etc.
• ⬤ • Espresso, moka, etc.
Kingston's How to Make Coffee: The Science Behind the Bean:
When grinding for espresso at home, as a rule of thumb if the coffee is coming out too fast, use a finer grind to slow down the extraction; or, if the coffee is coming out too slowly, make the grind coarser.
If your coffee tastes a little sour, this is often due to underextraction. With methods of brewing other than espresso, using a finer grind will allow more coffee to be extracted during the brewing time. The same is true of bitter coffee—this could be due to your beans, equipment, or brewing method, but it may also be caused by overextraction. Make the grounds a little coarser and to slow down the extraction rate.