Johnny Depp's character in the film version of From Hell has opium-induced psychic visions.
The Precogs or "Oracles" of Minority Report only have premonitions while sleeping, so they kept on heavy doses of drugs so that they're in a perpetual sleepy trance.
Also exists in the backstory— many Precogs are the children of drug addicts, their powers arising from their parents' experiments with new strains of narcotics at the time of conception/during pregnancy.
Dakota Fanning in Push uses alcohol to increase her abilities as a Watcher. The catch? She's 12.
In the Western The Hallelujah Trail, Donald Pleasence plays "Oracle" (of course), who's able to prophesy with the help of a taste of booze. The catch is that liquor is in short supply in his small town, and he's prophesying about the arrival of a shipment — and how they need to make sure the Indians don't steal it or the temperance workers destroy it. Notably, every time he takes a drink, a heavenly chorus is heard just before he speaks.
Chef Bushey from Under the Dome becomes a junkie prophet while suffering a meth seizure, and the town's third selectwoman does the same while coming off of Oxycontin. Granted, Chef's prophecy is self-fulfilling...
In The Gunslinger Roland takes mescaline before seeing the "speaking demon".
In A Song of Ice and Fire the warlocks of the Undying of Qarth drink an intoxicating concoction known as "shade of the evening" to the extent that it stains their skin blue. It's unclear whether they believe this to grant them their powers, or whether it's simply a ritual act.
Half the shamans and magicians in the Northland Series are on serious hallucinogens.
Seers in the Branion series do this a lot, making half-poisonous potions with deadly fantasy plants. The sovereign takes one at his coronation ceremony. One Seer becomes addicted, taking the potions for medical purposes after coming down with a disease.
Played for laughs in John Moore's Fractured Fairy TaleBad Prince Charlie, when Charlie goes to see an oracle who turns out to be a very naked and very stoned young woman, who talks and acts like the stereotype of The Stoner.
Karl, of F. Paul Wilson's "Aryans and Absinthe" short story, learns that he can see the future when consuming absinthe. After his attempt to assassinate Hitler fails due to the interference of a friend obsessed with novel social phenomenon, he realizes that his friend similarly sees the future in this manner...
The Oracle of Delphi. There's strong evidence to suggest that volcanic gases filtered into the cave where she made her predictions.
Same applies to John of Revelation, albeit with the addition of bizarre symbolism.
Traditional shamans in many historical and some modern cultures often use various ethnobotanicals, such as psilocybin mushrooms, ayahuasca, peyote to induce transcendent states for spiritual purposes.
The ancient, Vedic ("Indo-Aryan") inhabitants of India used "soma' to feel at one with the universe and induce some fairly wacky visions, if the poems they left behind are any indication. (Note that nobody knows from which plant, or what else, soma comes from. However you know now where Aldous Huxley got an inspiration from.)
There's a theory that Aztec mythology was full of feathered snakes and corn- or snake-headed deities and heart-ripping directly because of the amounts of mescaline and mushrooms (of the magic variety) their priests were using to induce visions. The heart-ripping probably caused by a horror trip.
Aleister Crowley was by all reasonable measurements, a complete heroin/cocaine junkie. He did, however, found a religion, Thelema, and promote it. There are still Thelemites around today, too.
Dr. Timothy Leary is a Real Life example from the Hippie Era. He coined the phrase "Turn on, tune in, drop out" and was a major promoter of the spiritual use of LSD. His advocates included many 60s/70s counterculture icons, including The Beatles.
Olga Bogdashina saw similarity between Aldous Huxley's experiences with mescaline and the heightened sensory perceptions of people with autism in Autism and the Edges of the Known World. And later in the same book she states that some autistics can appear to be precognitive due to their senses and unconscious perceptions, similar to dogs barking before an earthquake. One can draw the conclusion that mescaline may be able to grant prospective intuition, though the hallucinations would be problematic.
Meatwad, from Aqua Teen Hunger Force, begins to give out predictions in a Godly monotone, like Frylock exploding (from his point of view - it really was someone leaving a grenade on their porch after Frylock opened the door, which only irritated Frylock). Turns out, he got visions from eating glue.