Most tires have a "maximum" pressure, or a recommended pressure range marked on the side of the tire. These pressure ratings are established by the tire manufacturers after consultation with the legal and marketing departments.
The legal department wants the number kept conservatively low, in case the tire gets mounted on a defective or otherwise loose fitting rim. They commonly shoot for half of the real blow-off pressure.
The marketing department wants the number high, because many tire purchasers make the (unreliable) assumption that the higher the pressure rating, the better the quality of the tire.
Newbies often take these arbitrary ratings as if they had some scientific basis. While you'll rarely get in trouble with this approach, you will usually not be getting the best possible performance with this rote approach.
Savvy cyclists experiment with different pressures, and often even vary the pressure for different surface conditions.
Optimal pressure for any given tire will depend on the load it is being asked to support. Thus, a heavier rider needs a higher pressure than a lighter rider, for identical tires.
Since most bicycles have substantially more weight on the rear wheel than on the front, the rear tire should almost always be inflated to a higher pressure than the front, typically by about 10%.
Rough surfaces generally call for a reduction in pressure to improve ride comfort and traction, but there is a risk of pinch flats if you go too far.
Rider skill also enters into this: more experienced cyclists learn to "get light" for a fraction of a second while going over rough patches; newbies tend to sit harder on the saddle, increasing the risk of pinch flats.
The table below is based on my experience and a certain amount of guesswork, and should only be used as a very rough guide to a starting point. Interpolate/extrapolate for your own weight/tire sizes.
Tire widths are in millimeters, pressure recommendations in pounds per square inch. (Divide by 15 if your gauge reads in bars/atmospheres.)