It's possible that you're being bitten by this PgAdmin bug (changelog):
2012-11-28 AV 1.16.1 Date picker controls returns a full timestamp
by
default, which can cause inadvertent date changes
on jobs and role validty dates. Ignore the time part.
This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.
You can check these expiry dates with:
SELECT usename,valuntil FROM pg_user;
and if they're wrong, reset them with:
ALTER USER username VALID UNTIL 'infinity';
and upgrade pgAdmin.
See this part of the pg_dump
manpage:
-W, --password
Force pg_dump to prompt for a password before connecting to a database.
This option is never essential, since pg_dump will automatically prompt
for a password if the server demands password authentication.
Don't use -W
at all. In your case, it's just confusing.
Also, you need to know that the fact that the server asks for a password or not is not driven by the existence of this password.
It's driven by the server-side pg_hba.conf
file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).
EDIT: reviewing your pg_hba.conf. The relevant lines are:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
The 1st line concerns the postgres
user. It's irrelevant for your pg_dump command since you're using the santa
user with -U santa
The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local
). From the client, it means when you do not use -h localhost
. It says that if the OS user is the same name than the db user, he doesn't need a password.
The 3rd line says that if -h localhost
is used (IPv4 TCP connection), a password will always be asked to the client. The 4th line says the same with IPv6.
Based on this, this command run by the santa
OS user should not ask or need a password:
pg_dump --no-owner myapp_db > myapp_db_backup.sql
-U santa
is optional because the db username is taken as the OS user by default.
Best Answer
Generally it does.
Your SSH tunnel is configured so that the remote SSH server connects to PostgreSQL through a TCP connection to
localhost
. And generally, a defaultpg_hba.conf
maps this type of connection to themd5
authentication scheme, for which a password is required. Otherwise any user with a shell account could connect to PostgreSQL, which would be wrong as a default configuration.The more common way to use PostgreSQL through a SSH tunnel is to set a password to the PostgreSQL databases accounts that you need to connect to. Use the
\password
command insidepsql
or theALTER USER
SQL statement.