Forum OpenACS Q&A: Response to postgres and user passwords not seem to work.

My guess is that your pg_hba.conf (in debian it is in /etc/postgresql) has something like

local        all                                          trust

Then you really don't need a password to get in postgres when you're connecting from the same machine that postgres is running (although postgres will still ask for it after your username if you have -u as a flag).  This assumes that the O/S does a good job at authenticating users on the host machine.

If you want passwords to work, you need to change "trust" to something else, e.g. "password".  The file pg_hba.conf usually has notes about the authentication scheme. Or the manual.