I've been scratching my head over this, and I've seen much conflicting "help" online... So I'm just posting it here and hope people will find it.
On OS X Lion Apache comes with mod_rewrite enabled by default, but the AllowOverride directives are still set to None.
You're also installing Drupal into ~/Sites/[username]/drupal
Here's how to change it:
1) Edit /etc/apache2/httpd.conf and look for:
#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
# Options FileInfo AuthConfig Limit
#
AllowOverride None
Change "AllowOverride None" to:
AllowOverride All
2) Edit /etc/apache2/users/[username].conf and look for:
AllowOverride None
Change it to:
AllowOverride All
3) Go to System Preferences->Sharing, uncheck "Web Sharing" and check it again to restart OS X's Apache.
4) In your Drupal install at /Users/[username]/Sites/drupal/ edit .htaccess and look for:
# RewriteBase /
Uncomment (remove the hash '#') and change it to:
RewriteBase /~[username]/drupal
That's it.