There are several types of texturing software. Ranging from older 2D painting methods such as Photoshop to the most advanced methods of painting directly onto a 3D model. The programs listed below are currently considered best practices, although uses vary. I personally recommend the Substance suite of products by Allegorithmic due to its ease of use, integration into existing video game engines and pipelines, and agnostic approach with other software and rendering engines.
Special Note: Allegorithmic has been purchased by Adobe