I faced a hair-tearing problem. mod_rewrite was loaded and yet it was not working in .htaccess files (and httpd.conf initially). Here is the solution along with how you can debug mod_rewrite problems.

Debugging procedure:
Add these two line to your httpd.conf immediately after RewriteEngine On.
RewriteLog "/var/log/httpd/rewrite_log"
RewriteLogLevel 9
After that I added the required RewriteRule etc.

The reason for testing directly in httpd.conf is to ensure that mod_rewrite is working in the first place. After some debugging I realized my expression was wrong. So now I found mod_rewrite was working in httpd.conf. However it still wasn't working in .htaccess files.

Solution:
I found AllowOverride was set to None in httpd.conf. I changed it to All (after all I am the only user of the machine). And it finally started working everywhere (after a restart).

What I learnt:
Unmanaged dedicated web hosting is really really painful, unless you are (or have) a good linux system administrator. Too many things to setup and too many points of failure. And I haven't even started working on serious hardening stuff.