First of all, you should be aware that there are several textures that might be applied to a model. The most common one is the diffuse-map (color, sometimes including alpha), in modern games you'll also find normal-maps (bump-map), specular-maps (amount of specular reflection) etc.
There are different processes to obtain these textures, but generally these have to be made specifically for the model at hand (as the texture has to fit the UV layout) and can't be downloaded or purchased somewhere (unless you buy a model with the texture).
A common workflow looks like this: You create a mesh with lots of details, commonly with a sculpting tool. Ideally your sculpting base-mesh or starting mesh is the low-poly mesh that meets the requirements of your game (amount of triangles).
Then you unwrap the mesh. There are even specialized tools (like UVLayout) that are very good at unwrapping meshes with minimal distortion and making optimal use of the texture-space (packing).
So when you have the UV layout and the model, it's time to create the texture(s). The normal map usually looks like shown in the following image (surface normals are encoded in the RGB colorspace):
It's very hard to create such a texture by hand, therefore it's usually obtained by baking the high-res mesh to the texture-space of the low-res mesh.
Baking texture is a process that's generally useful to get a good starting point for your textures. A common approach is to bake an ambient occlusion map. It will look like this (image © vitamin3d):
This already gives you a great starting-point for your diffuse texture. The process to create the texture is very different from artist to artist and also dependent on the type of texture you need. I think it's safe to say that Photoshop is widely used, in combination with a digital tablet. Sometimes textures are entirely painted, sometimes they are based off photographic imagery.
In case of my avatar image, I decided to print out the UV layout, paint the texture by hand (pencil, felt-tip marker) and scan it back in.
As you can see, there are lots of different ways to create textures, but there's usually a lot of work (and skill) involved in creating good textures. Since the textures also have to match the style/mood of your game, they should be crafted for your game.