This discussion has been held before, but I don't see a particularly recent one, as in, after a bunch of US states legalized marijuana, recent.
Off the top of my head, tobacco, cannabis, caffeine, coca, opium and shrooms would be reasonably obtainable medieval substances that dwarves could use. I'm not suggesting to depict the kind of super drugs that we have in the modern world. Most of these plants, including tobacco, have been bred or engineered to produce stronger effects than they would have in the middle ages. The medieval equivalents should not be much stronger than alcohol (keeping in mind, alcohol can be pretty bad if you're not a dwarf) Opium is strong, but was difficult to produce in abuseable quantity before 1400. Caffeine of course doesn't grow on trees, exactly, the player would have to obtain and distill substances like coffee, tea, cocoa, kola or guarana to get at it. Which I guess is also true of opium, I'm less familiar with that one.
If I remember my war-on-drugs indoctrination propaganda correctly, shrooms are dangerous because they are wild and can have very different levels of hallucinogen per individual, but that feels like a dwarfy or at least rogue-likey caveat. NetHack had a fun hallucinating syndrome that could be triggered by eating yellow mode, which I could definitely imagine having different levels depending on the severity of shroom. One of the dangers of hallucinating in NetHack was that it could lead to accidental murders, and I could see shroom abuse causing dwarves to go into temporary Berzerk or Insane status or maybe even shroom induced weird moods. So the player can either balance letting dwarves use the shrooms for happy thoughts against the occasional bad trip, or try to ban shrooms and prevent dwarves from jumping on them like they were socks.
Speaking of which, it could be interesting to allow the player to put bans or quotas on the import or production of various substances, or limit their use to medical, religious, trade or recreation. Dwarves that are addicted to a certain substance (maybe dwarves with an addictive personality, or a lot of stress) could try to steal some drugs from stockpiles or visitors could try to smuggle in contraband. Assuming that the Dwarven Economy will not be revived for the purpose of contraband, maybe smugglers could appear like thieves or ambushes, a dwarf addict grabs an item of appropriate value and brings it to the smuggler, the smuggler leaves with the item and the player gets a message "a smuggler has made off with a (whatever it was)". Alternatively, the dwarf picks up a random item to trade for drugs and the smuggler provides the dwarf with a quantity of drugs based on the value of the item. Tavern visitors could throw a wrench into the drug war as well.
I had previously suggested aphrodisiacs, maybe those could function as a type of drug.
Alfrodo suggested that DF substances like Golden Salve, Mog Juice and Liquid Fire could be treated as drugs. In the Volsunga Saga, Sigurd roasts a dragon heart to eat, and the fumes give him the ability to speak with birds, and the birds convince him to kill his ally Regin. Sounds to me a lot like the conversation about shrooms, so perhaps the blood or extracts of megabeasts could have hallucinatory effects.