I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER jack;.
Now if I try to connect with it, psql asks for a password.
$ psql db_name jack
Password for user jack:
psql: fe_sendauth: no password supplied
What is going on and how do I check if a user has a password or not?
A user having a password or not won't affect if you're asked for a password when trying to connect. That is controlled by the pg_hba.conf file.
If you're being asked for a password that indicates that a password is required. If you try to connect to an account which doesn't have a password set that will simply fail. You need to either set a password for the account and use that, use a different type of connection or modify the pg_hba.conf file to use a different authentication method.
You can keep a password on the account, and then use a local password file to skip having to enter the password each time.
The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).
This file should contain lines of the following format:
hostname:port:database:username:password
If you want to have postgres never ask for a password for a specific user, then supply the -w option during user creation.
createuser -w user
(note that -W (capital w) will always prompt for a password)
By default users made in postgresql with CREATE USER user don't have a password.
-w and -W. They're meant to give connection parameters to the createuser program itself, not to indicate how the future user may connect.
Commented
Mar 1, 2013 at 16:52