How religion works in a fantasy RPG is strongly informed by the game's mood.
Superhero: In a fantasy RPG in the "superhero" mood, there will be good gods and bad gods, and they are in a constant struggle, suspiciously similar to Christian dualism. The god(s) of good versus the god(s) of evil. The PCs worship and may be aided by the good gods. Their enemies are those who serve or are aided by the gods of evil. Even superhero games can have some nuance. Consider Sauron's insidious corruption of Theoden through Saruman and Grima in The Lord of the Rings. Here we have elves (light, order, goodness, peace) and Sauron (evil, darkness, chaos, corruption, war) in contention - the classic good versus evil fantasy epic. And yet we have two layers of subtlety -- Saruman's secret treason to serve Sauron; and his servant Grima's subtle corruption of Theoden.
In this sort of game, a cleric is a main driver of the story. She carries the word and the power of the Gods of Good in her struggle to defeat the Great Evil. Such a cleric might be too powerful for an adventuring party -- imagine if Galadriel came with the Fellowship. This is also why, when you say you're going to run Star Wars, everyone wants to play a Jedi.
Hunter: There are a mix of gods of good and evil; and some in between in a fantasy game at the monster hunter level. Some gods are mysterious. Others are authoritarian and support tradition and stability. Others have a progressive, revolutionary justice philosophy. Most D&D and Pathfinder games are run in the monster hunter mood. The bad guys are bad by dint of their being monsters, or measurably evil -- Detect Evil is a first level spell! Occasionally, the PCs run afoul of Neutral or Good aligned NPCs or monsters because they find themselves at cross purposes, but these conflicts are obstacles toward resolving a "defeat the monsters" plot, rather than the focus of the story.
In this sort of game, a cleric is a major character. Many plots involve Evil Priests and Dark Gods to be defeated with the Power of Good. Paladins deserve special note, too. They're pretty much the embodiment of the Hunter mode of play.
Heroes: In fantasy at this level, we shirk the pure black-and-white morality of good versus evil gods, but the PCs are still heroes. Gods tend to be aloof and distant, not interfering with the mortal realm. This allows bad guys to prove that they're bad with deeds, rather than just by wearing a skull-and-dagger holy symbol and a black cloak. The Eberron campaign setting shifts D&D down a rung from monster hunter fantasy to a heroic mood, in part by including measurably-Good NPCs who are nonetheless quite villainous; and measurably-Evil NPCs who are actually pretty helpful; but also by moving the gods from meddling chessmasters to distant forces. Even prophecy in Eberron comes from a mysterious force the gods themselves don't fully understand.
In a Heroic game, a cleric is a major character. Every fantasy hero gets their power from somewhere, and her source is the God of Valor or Healing or Freedom, as she sees fit.
Gritty: The gods of a gritty fantasy world are neither good nor evil, but forces of nature. Gritty games tend to live in moral grey areas. Gods embody both the good aspect (health) and bad aspect (sickness) of their domain. You can sacrifice cattle to the hearth god to bring a blight on your enemy's crops. You must beg the storm goddess to protect your ship from storms, but if you do so in the right way, your voyage will be blessed. The gods tend to have internal conflicts, too, and the PCs might get swept up in these conflicts, which tend to cost countless human lives. Old school D&D often ran on a more gritty Robert E. Howard mood; and a lot of the new OSR material highlights this tendency and amplifies it. From Lamentations of the Flame Princess to the Fourthcore movement, there's a new interest in gritty fantasy.
In a gritty game, a cleric is an interesting character. She commands the power of an ancient and ineffable extra-planar being to serve her in battle.
Dark: The gods of a dark fantasy world are generally selfish and human in their personalities. They may bring power and blessings, but only to those who are devoted worshipers who propitiate them with enough sacrifices. Those who cannot sacrifice or refuse to sacrifice suffer curses and plagues. People tend to be more afraid of gods than worshipful of them. Gods are not forces of love and grace, but rather divine extortionists running a protection racket. Priests tend to reflect or compliment their gods' personalities, either groveling in constant maddening fear or bleeding the populace so they can live richly. Truly dark fantasy is actually pretty rare. Even "horror" fantasy tends to have only the trappings of horror. In Ravenloft or Pathfinder's Carrion Crown adventure path, the PCs still fight against evil using the often-literal power of good.
In a dark game, a cleric is an unlikely choice. She discovered ancient tablets in the wasteland and learned the names of six Great Ones. By making sacrifice to the Great Ones, she can call on their aid, but they send her disturbing dreams and she feels compelled to seek the jade tower...
Horror: In a horror RPG, the good gods are gone, and the ancient gods are mostly forgotten, except by isolated tribes worshiping a poorly-understood snake-demon at the top of a crumbling ziggurat, or cults placating a mysterious, cloven-hoofed prophet in hidden chambers beneath the great city. The general population may worship their ancestors' spirits, thus bringing eerie hints of hauntings and an unknown afterlife full of unknown punishments into the cultural zeitgeist. There's some new interest in fantasy horror. A major impediment to fantasy horror in modern D&D (5e) and Pathfinder is the mechanical need for a divine healer. DMs who try to run these games for parties without a "healer" tend to get frustrated by their players' increasing defensive turtling as they have to retreat for days or weeks to heal up after combat. More, most fantasy RPGs don't have mechanics for powerlessness and loss (other than loss of hit points). Luckily, other fantasy RPGs exist, and have systems that support horror better, from Gumshoe to Dungeon World to Torchbearer.
Horror games make contacting Old Gods a dangerous proposition. There is never a specific cleric PC, or if there is, she's like Father Callahan in 'Salem's Lot who serves a church without the classic D&D cleric's magical power; and is surprised and challenged when her faith is called for, even in the smallest way. Or maybe all the PCs can, if they choose, learn ways to contact the minds of the Ancient Ones from bizarre ancient tomes that crack the edges of their sanity but teach them spells, like in the Call of Cthulhu RPG.
Example a Gritty/Dark/Horror Fantasy Pantheon
The pantheon, below, can be used straight for a "gritty" mood fantasy RPG. PC clerics would see Checkal as the goddess of healing, Arufu as the god of light, Marku as the god of funerary rites, Mar Dat as the god of valor, Han Dat as the goddess of law, and Grugri as the goddess of nature. They would see Yull as the adversary. The gods would be revered, but also sacrificed to to attack the people's enemies. An example adventure would be negotiating a peace between two tribes that had nearly wiped each other out by sending plagues and blights to hex each other for years. As a reward, the people of the two tribes will give them land and honors, though if one tribe feels that they got a bad bargain, the PCs could receive a poisoned gift. And it is possible for both tribes to feel like they got the bad end of the deal.
For a "dark" fantasy game, these gods would be propitiated, rather than revered. They would sacrifice to the gods to prevent plagues, blights, droughts, curses, etc. There would be no PC clerics in this sort of game, because the gods would never be that unilaterally helpful. An example adventure would be robbing the Moon Temple in the capital city, on the dark of the moon, where the Checkal priestesses are praying for plagues and death, to steal the great moon opal for pay, for a wealthy wizard to use to gain immortality. The wizard has given the PCs amulets inscribed with the name of Ya'Uul to hide them from Checkal's eye so that she will never know it was them who stole her opal. As part of their pay, they get to keep the amulets, and can become invisible to Checkal's shadow demons (as long as they do not interact with them) by wearing them.
For a "horror" fatnasy game, these gods would be long forgotten, and the GM would keep their nature secret, revealing its true horror in ruins of a civilization that destroyed itself. They would discover ways to contact the Shadow Goddess, the Lord of the Archon Host, the God of Death, the Killing God, the Binding Goddess, the Storm Queen, and the Fire Demon and call on their aid (for a price). They would also witness the horrors done in each of these gods' names. An example adventure would be exploring a lost city destroyed by the Archon Host a thousand years ago. It is infested with Ents and surrounded by fey, but full of treasures. Inside, there are tablets that teach the Han Dat Lesser Rites of Binding - spells that bind people to oaths, even unwillingly; but in return, require the caster to never utter a word that is technically false.
Checkal, the Moon Goddess: Men and women pray to the goddess of fertility, but her priesthood is all women. Checkal's cyclic nature makes her suspicious and subtle during the dark of the moon, and bright and generous in her fullness. During the dark of the moon, it is said she walks the earth in mortal guise and her priestesses can call on her to smite their enemies with disease or infertility. During the fullness, they can call on her to strengthen the herds or cure the sick. Her demons are shadow demons, and some of the undead are willing to serve her, especially on the dark of the moon, because Marku still loves her.
Arufu, the Sun God; the Sky King; Warlord of the Archon Host: The chief god of the pantheon, Arufu is a distant and authoritarian lord. He commands the sky, and brings sun and rain for the crops. He is also god of all the things that come out in the rain and live under the sun. Unlike the other gods, Arufu has three hosts that serve him: His angels appear as gigantic men with the wings and crowns of colorful birds of paradise surrounded by clouds of carnivorous butterflies that feed on the corpses of Arufu's enemies. He casts plagues of insects on those who displease him, and commands a legion of demons called Ents, monstrous plants and insects animated to serve him. And lastly, he commands the Archons, soldiers who come to Earth to eradicate entire tribes and nations who have angered the gods, leaving nothing but ruin behind. The Archons are also the host that will come to Earth and bring about the End of Days.
Marku, God of Death; the First Vampire: Arufu's brother was the first death, slain by Arufu in a fight over Checkal. The first death defined what it meant to be dead: Hidden underground, away from the sky and sun for eternity. The dead live underground. They are buried or burned and seeded into the soil, where they sink down into Marku's realm. This being a fantasy game, the underworld is a place you can go visit without any magic. And the dead can come back from the underworld, too, if Marku has been neglected or funerary rites have been done improperly. Corpses can rise as zombies and skeletons, though they always hide under soil, in basements, or in caves when the sun comes up. Marku is also the First Vampire, for after his death, he could only see his cherished Checkal at night, in her fullness.
Checkal and Arufu had two children, the lesser gods:
Mar Dat, God of War: The son of Checkal and Arufu is Mar, god of war. He is the god of might and power, killing and honor, glory and weapons. Mar Dat blesses conflict and killing. Those who displease Mar will suffer ill fortune in battle, or be set upon by hidden enemies. Those who please Mar will have fortune in battle or have their enemies revealed to them. Mar is close with his mother, Checkal, and together they are the gods of vengeance and strength. He is the God of vengeance of peoples and she is the god of personal vendetta. He is the God of strength of arms, and she is the god of physical might. Mar's demons are burly, masculine monsters with horns, spikes, chains, armor and blades.
Han Dat, Goddess of Law: The daughter of Arufu and Checkal is the mother of lies and queen of truth, for without one, the other cannot be. Han Dat is prideful, and those who speak oaths in her name must abide by them or risk her curse. Sacrifices made in Han's name have subtle results. Those who neglect to sacrifice to Han suffer bad fortune in business and law. Those who please Han have good fortune in business and law. Oaths and contracts are made in the name of Han Dat, and those who break their oaths offend Han, and are cursed. Justice is a mortal concept -- Han cares only for law. The Clerics of Han Dat, it is said, never tell an outright lie; but they always withhold some of the truth, so that you are deceived anyway. Han is the mother of men, and Mar is the father of men. The twin siblings birthed all the civilized humanoid races. Han's devils are subtle, offering bargains and trades. They trick fools into bargains for their souls, to feed Han's magic.
Outsiders:
Grugri, Goddess of Storms: The mother of the uncivilized humanoids, Grugri is an interloper goddess from across the great sea. She is the bringer of storms and stands as powerful as Arufu when weather is concerned. But Arufu hates Grugri for reasons only the mystery cults of Grugri claim to know. The Storm Goddess is always portrayed wearing a hood and cloak, hiding her form, no matter who paints the image: The fey, dark folk, and men all portray her thusly. She is also the Queen of Tooth and Claw and Caster of Faeries, to the men of the Southlands. Grugri's demons are the Sovereign Host of the fey. They are called the Others because, as they are not of Arufu's line, they are true outsiders. Goblins and orcs and ogres and the like are called mongrelmen or darkfolk. Most of them hide from Arufu's light. Still, people sacrifice to Grugri and even join mystery cults to her, because her fury can swamp crops, sink ships, flood towns, and bring tornadoes. Best to send her after your enemies, then. Grugri is so greedy she will accept sacrifices from men to destroy her own dark folk or fey demons. The Fey see Grugri under the name "Walker-in-Twilight, Queen of the Sun and Moon," but of course Arufu and Checkal's priests can't have that. The dark folk worship Grugri under that name, calling her the Mother Goddess, She Who Birthed the World, which is also a heresy to the priests of the tribes of the Southlands.
Yull, the Fire Demon, the Summer Balor: Some say Arufu once had the aspect of heat and fire, and he commanded a demon called Yull, a gigantic fire-shrouded sexless human form cast from molten brass. Yull brought wildfires, lightning, drought, and heat sickness. They say he rebelled, though, and was banished. But the Tribes of the North worship a fire god, and some say that in ancient days, Arufu's priests declared the worship of fire to be evil, to keep the men of these lands from heathen practices. Yull brings visions of prophecy. Sacrifices to Yull always bring prophetic dreams and even omens in the waking world. Yull's imps can be summoned to help interpret these signs, but they demand favors in return for their service, and if they are betrayed, Han Dat becomes angry and Yull sends confusing, mad visions and nightmares; so it is best not to summon them at all. Yull rules on another plane of existence, in a city of brass full of fire demons from the North (or who rebelled against Arufu, depending on who you believe). The men of the North believe that the fire god Ya'Uul is their protector, as fire brings warmth and light. They call the Southern tribes the "children of the air god."
