i used to yakfish upstream from roseville bridge a bit, great water for lure fishing, usual suspects bream,flathead,whiting,jewfish,mullet,tailor even got a kingy up there once while chasing flatties on plastics.
pretty popular for yak fishers up that way.
might even head up there tomorrow myself.
+1 for upper middle harbour! I'll be spending A LOT of time up there over summer. Anything from bream and whiting on small SP's and HB's to 20kg+ Jewfish on livebaits. Make sure that if your livebaiting upstream from the roseville bridge, use either tailor or mullet. You need to "match the hatch". These are the main baitfish in the area which makes them the best bait for the predatory fish.
Narrabeen lakes is great for a few bream and flathead during summer. Generally quite a shallow lake but there are a few nice drop offs that produce big lizards and even the odd jewfish when the time is right. If whiting on poppers excites you then there are some nice spots in the lake.
Pittwater is also worth a look. Kingfish are the go in summer. Live baiting is best but when they are on the surface poppers and unweighted SP's can be deadly. No shortage of other species too if thats what your after.
These are all poplar sydney "yakking" areas. Roseville has a specific kayak ramp so that should come in handy. In summer don't burley too hard around roseville. Plenty of Bull and Tiger sharks come upstream. Generally speaking there's nothing to worry about though... There's not really anywhere in sydney that sharks aren't at...
Good luck!
Cheers, Tom