Postgresql – psql asks for password, CREATE USER didn’t specify one

postgresqlpsql

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?

Best Answer

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.

Related Solutions

PostgreSQL: Remove password requirement for user postgres

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.

pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same username in the operating system as in PostgreSQL, use peer (UNIX, only for local connections) or sspi (Windows).

If there's a password set, but pg_hba.conf doesn't tell PostgreSQL to ask for it, the password is ignored.

If pg_hba.conf tells PostgreSQL to ask for a password but there's none set, then all login attempts will fail no matter what password is supplied.

Postgresql – psql: FATAL: Peer authentication failed for user

In a fresh install from a few days ago, the second line of my pg_hba.conf is

local   all             all              peer

I believe this is the one that makes your connection attempt fail.

The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there is no second try, so the connection attempt will likely fail. Or, as the documentation states:

There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

The solution is easy: either remove the above line if you don't plan to use peer authentication, or move your specific rule above this one.

Best Answer

Related Solutions

PostgreSQL: Remove password requirement for user postgres

Postgresql – psql: FATAL: Peer authentication failed for user

Related Question