Why was Paulie so against drugs?

Because he listened to the advice of Nancy Reagan to say No to drugs.

He represents old school mafia v new. Vito Corleone didn’t want to get into the narcotics business either. I don’t know the history of the actual mafia but I gather this sentiment was based on some real divisions in the mafia world on this issue. The old school guys had some principles and knew the boundaries of their power. The new school were lured by the money. Drugs also require making alliances with outside networks and international cartels, plus the added pressures of dealing with federal law enforcement that other posters mentioned.

Old time Mafia guys were very wisely against narcotics trafficking. If a soldier got busted selling that junk and was facing 25 years he would likely turn witness for a major conspiracy prosecution case against the entire outfit. That’s why the old timers forbade junk peddling.