PostgreSQL - using password/md5 authentication with empty password

Question

I have the following role in my system:

CREATE ROLE test LOGIN INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

There is no password. Trying to connect to the db from within the same machine using either psql or PgAdmin3 to the above role I'm getting:

psql: fe_sendauth: no password supplied

When the password is set I can connect perfectly.

The pg_hba.conf is default - merely:

host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password

Create Role statement help says:

PASSWORD password

Sets the role's password. (A password is only of use for roles having the LOGIN attribute, but you can nonetheless define one for roles without it.) If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL.

Can I deduct from that that having a NULL password password authentication will always fail? Is this also the case for MD5 authentication? Is there anyway to connect using an NULL password role without specifying trust authentication?

Answer 1

Indeed an empty password is equivalent to no password so it's not going to be accepted by the server when pg_hba.conf says that a password is required.

Still, there are various ways to avoid inputting the password:

• trust method in pg_hba.conf, possibly constrained to a specific user and database
• client-side PGPASSWORD environment variable
• client-side .pgpass file
• dummy password like "test" stored in the same place than the login name