Warning: NSFW probably means not safe for Photo Stream
You're out of town and your special someone is back home so you decide to secretly snap some racy pictures of yourself and sext them on over. Meanwhile they're just finishing showing off the new iPad or Apple TV to mom and dad, the screen saver or slide show kicks in, and your private pics just start Photo Streaming by...
And they can't be deleted. Not until they're automatically removed from your iOS devices after 30 days or 1000 photos, so you're logging out of everything for a month or taking 1000 pictures of the floor or something to flush the stream... because otherwise you're stuck.
While iOS 5, iCloud, and Photo Stream are awesome if you want to take a picture of your family at a party or your little ones during the game or some flowers or clothes you like or want to remember, that same "automagically everywhere" Camera Roll sync can easily -- easily -- turn around and bite you in the apps.
So if you're going to take photos -- or save them from Safari, Messages, Mail, etc -- that are NSFW, you should probably consider them not safe for Photo Stream either.
Jump into Settings > iCloud and turn off Photo Stream while you have your fun, then delete them from your Camera Roll, then -- when you're ready to go back to family pics and landscapes -- turn Camera Roll back on.
And if you work in Hollywood, this goes quadrupal for you!
UPDATE: As mentioned in the comments below, you can purge your Photo Stream via iCloud.com, but that's still not the same as being able to delete an individual picture.