The crm application does have access to its database. It shows what a refusal looks like at each stage of a connection.
The four attempts differ in one psql option each. -h is the server address, -p the port (5432 if omitted), -d the database. -U is the PostgreSQL role, an account inside the server. It has nothing to do with Linux users: you work as student, yet you log in as the crm and shop roles. psql looks for the password in ~/.pgpass, and the PGPASSWORD variable overrides the file.
Log in as crm four ways
Log in as crm: on port 5433, into the other database shop, with a wrong password, and into its own database crm.
First, predict
What will psql answer on port 5433?
Now start the exercise, run the command and compare your prediction with the output.
A connection goes through three checks in turn, and each has its own refusal.
First, the network. psql opens a TCP connection to an address and a port. If nothing listens there, the kernel itself answers: Connection refused. The server never saw the attempt. ss -tln shows what is listening.
Second, the rule. The connection is accepted, and the server looks for a line for it in the rules file pg_hba.conf. A line says who may log in, to which database and from where. No matching line gives no pg_hba.conf entry for host …, user …, database …: the refusal lists exactly what the server compared.
Third, the password. A line matched, and its method scram-sha-256 requires a password. A wrong one gives password authentication failed for user ….
psql prints every refusal twice: first it tries an encrypted connection (SSL encryption), then an unencrypted one (no encryption). Both lines give the same reason.
The application log says FATAL: password authentication failed for user "shop". What follows from it?
-
Without a line the server would answer
no pg_hba.conf entryand never ask for a password. The password is requested by the method of a line that matched. -
Then psql would have written
Connection refused. AFATALline comes from the server, so the server accepted the connection. -
The server checks table rights after the login, and the login never happened: the refusal came at the password.
The server writes the password message only after it has accepted the connection and found a rule line. So the network and the rule are passed, and the password in the application's settings differs from the role's password. You can check it with psql -h 127.0.0.1 -U shop -d shop and the password from ~/.pgpass.