The varactor is normally reverse biased, so this configuration is OK for a varactor. However for a forward-biasing diode, you'd need N+ buried layer to prevent significant vertical current. Since the substrate is p-type, it would be grounded. Forward biasing the pn junction you've shown above would create a vertical pnp. You would get conduction between the p&n, but you would also get a high-gain path between p & substrate. in a standard CMOS process this beta could be as high as 100 without n-buried layer, and could be as low as 10 with buried layer. So in either case it's not a very good device, as 10x your diode current would be shunted to ground.
What you need is a twin-well process, where you would use an N+ region inside the P+ region shown above. Now you can tie the N-well to the highest potential and the substrate remains grounded. For a device like an internal-charge pump for driving an NMOS LDO gate, the diode voltages will be higher than VIN so you can't tie to VIN. For this type of device, you must consider the biasing carefully for conditions of startup and shutdown.