First off, let's be clear -- any discussion about Image Stabilization on a tripod refers ONLY to a truly rock-solid tripod, on a totally firm surface without vibrations from passing traffic and so on. In many real-world situations, we're using tripods and other supports in conditions that really aren't totally solid. A good test, before discussing the question any further: the next time you're mounted on a tripod, turn your camera's Live View on, and magnify the LCD monitor image to its greatest setting. It's sometimes amazing how much shake and movement there really is, even on a tripod.We hope that your knowledge of image stabilization is now one stop greater!
The point is pretty clear. In any situation where you're not truly rock-steady, whether you're mounted on a tripod, or certainly a monopod, using Image Stabilization normally makes a great deal of sense.
However, since the launch of the first Canon Image Stabilized lens (the EF 75-300mm IS zoom lens, from 1995), Canon engineers have recommended switching IS off if and when you're mounted on a tripod. Again, this pre-supposes it's a truly rock-solid tripod.
Canon's optical Image Stabilization has definitely evolved since its launch in 1995, and there are now different versions for lightweight, less-expensive lenses (like the EF-S 18-55mm standard zoom for compact cameras) than the more advanced IS units we see in (for example) L-series super-telephoto lenses. Basically, current Canon EF and EF-S lenses can detect when there's a total absence of "shake" (in other words, solidly tripod mounted), and internally disable the Image Stabilization if it's left on. But in some lenses -- and it varies, depending on the IS design in the lens in question -- the moveable IS lens elements aren't locked and centered when the IS is disabled this way, and can sometimes be susceptible to slight movement during exposure. On such lenses, physically switching IS off with the switch on the lens allows the lens to lock and center these elements.
Again, there are variables -- too many to get into here, since it depends on which lens model, which version (in other words, how old is the lens in question), and so on. But the bottom line remains pretty simple. It's safer to just switch IS off if you know there will be a complete absence of camera and lens movement during exposure.
One other thing... Canon's optical Image Stabilization is designed as a tool to get sharper pictures at "normal" shutter speeds. While the slow-speed limits may vary slightly from one lens model to another, Image Stabilization is disabled if the system detects a shutter speed longer than roughly one full second. So for longer night-time exposures, expect to just turn IS off, because it won't have an effect in your final pictures.
Hope this helps clarify the questions about Canon's optical Image Stabilization when cameras are tripod mounted.