Go to Monitoring>Debug log.
Where you would normally put the username, put the name of the Site.
For example:
If your site is called MySite, enter MySite as the user name. Shockingly undocumented, but this gives you the debug you're looking for.
ATTENTION:
Winter17 is making a change to this--you'll now need to ALSO set a specific cookie in the guest user's browser to enable this.
https://releasenotes.docs.salesforce.com/en-us/winter17/release-notes/rn_forcecom_debugging_guest_user.htm#rn_forcecom_debugging_guest_user
Related Idea Exchange Idea for Integrations
The requirement for such a cookie is incompatible when building web-hook services for 3rd parties, there is an Idea Exchange post here to provide an IP whitelist as an alternative.
Update from Spring '18 release notes:
In Winter ’17, we introduced a change that required guest users to set browser cookies to activate debug logging. We had the best of intentions: All your public site visitors share one guest user license. Thus, when you enable logging for the guest user, the visitors’ collective activities can fill your debug logs quickly, hitting the log-size limit and causing log truncation. Sadly, people didn’t like the Winter ’17 change. Setting cookies is complicated, and the change made collecting debug logs for public users’ asynchronous activity impossible. We’ve seen the error of our ways. Your public users no longer need a debug_logs browser cookie to trigger logging. This change applies to both Lightning Experience and Salesforce Classic.
The only effect that "with sharing" has is to enforce object sharing rules; that is, if you use "with sharing" and query an object that has private record visibility, any Apex SOQL will only return results for objects shared to them; if you do not use "with sharing", the Apex SOQL will return results for all objects.
As VNE_Hess stated, the user's license restrictions are always enforced no matter what; you can't directly get around them when the code is executing as that user.
It's also worth mentioning that although Apex never enforces field-level and CRUD security, most of the standard VisualForce rendering components do enforce FLS/CRUD, so even though your system-context Apex was able to retrieve (and modify, etc) a field that the user doesn't have profile visibility to, if you pass it to VF via say an inputfield component, the user won't see it.
The Sites Guest User is a special profile that is heavily restricted from making updates to core CRM objects. From memory I think it's read-create only to the core objects except for Articles. Salesforce imposes this restriction AFAICT to require customers to upgrade to more expensive Partner or Customer Portal licenses to get this level of access.
If you're developing AppExchange apps that deal with the standard objects, it's very important to understand that to pass security review, your Apex code must manually enforce FLS and CRUD at every step of the way. This is a HUGE pain in the rear if your app does a lot of these updates, and results in very error-prone code because the ESAPI toolkit that Salesforce provides has a much more error-prone API than the core SOQL/DML interface.
Salesforce would save its app developer community literally thousands of hours of work per year by implementing a "with FLSAndCrud" type keyword, but architecturally that seems unlikely any time soon.
Best Answer
Okay, I have resolved this issue as I was preparing a wrong Image Url for the attachment.
My Site url was 'https://sandbox6-qbscompanies.cs23.force.com/registration', and the image url was getting resolved to 'https://sandbox6-qbscompanies.cs23.force.com' + '/servlet/servlet.FileDownload?file=' + attachment.id.
Thus I set the image url like '/registration/servlet/servlet.FileDownload?file=' + attachment.id. Then it worked correctly on public site.