Generally speaking, our most popular photo applications should work well under Mac OS X 10.5 Leopard. John Nack, states in his blog, "The good news is that most Adobe apps don't require updates in order to run well. That is, the CS3 versions of Photoshop, Illustrator, InDesign, Flash, Dreamweaver, Fireworks, and other apps are good to go for Leopard right now. Rock out."
Macworld Magazine also gives most Adobe apps a green light in a recent article, commenting that "Products that will require updates for full Leopard compatibility include Adobe Acrobat 8 Professional and the company’s professional video applications, including Adobe Premiere Pro CS3, After Effects CS3 Professional, Encore CS3 and Soundbooth CS3." But the photo apps seem to be fine, including Lightroom. [Update 10-28-07 PM: Reports are coming in that there are problems with Lightroom 1.2 and Leopard. James Duncan Davidson has posted on Inside Lightroom, and the comments following the post should add more information.]
On the Inside Aperture web site, the biggest warning hovered around Leopard and Time Machine. So you might want to read the comments at the end of that post for the latest information. Since that post, Apple has released an an Aperture 1.5.6 update that improves Leopard compatibility. It's available via Software Update and recommended for all Aperture users.
As for iPhoto 08 and Leopard, installing the iLife Support 8.1.1 via Software Update should address any minor compatibility issues between iPhoto and the big cat.
So all in all, looks like photographers using one or more of the major photo apps can update to Leopard with a reasonable amount of confidence. But with any update, practice good data management and back everything up first... just in case.