cgtextures is great. I feel bad seeing textures I recognize from AAA titles on that site, though - aren't professionals supposed to have cameras and, you know, be resourceful and shit?
For more goodness, check out
http://www.mayang.com/textures/
Download limit of 20 per IP per day, but if you were a good little artist you'd buy the dvd, which I swear one of these days I'm going to do, honest.