You will read your server's rules and find the line that lets crm in.
The server itself knows where the file is: sudo -u postgres psql -c 'SHOW hba_file' prints /etc/postgresql/16/main/pg_hba.conf. Only postgres can read it, so use sudo. grep drops the comments and blank lines:
Every line has five fields:
| Field | In the crm line | Meaning |
|---|---|---|
| type | host |
TCP; local is the unix socket on this machine |
| database | crm |
where to log in; all means any |
| role | crm |
who logs in; all means any role |
| address | 127.0.0.1/32 |
from where; /32 is exactly one address |
| method | scram-sha-256 |
how to check: a password |
local lines with the peer method let you in without a password. This is peer authentication: the server asks Linux for the user name and admits you to the role with the same name. That is why sudo -u postgres psql gets in, while psql -d shop as student does not: the server has no student role.
The server reads the lines top to bottom and takes the first one where the type, database, role and address match. Its method decides: scram-sha-256 asks for a password, reject refuses at once with pg_hba.conf rejects connection. The server does not look at lines below the first match.
pg_hba.conf has two lines, one under the other. crm logs in to the crm database from 127.0.0.1 with the right password. What happens?
host all all 127.0.0.1/32 scram-sha-256
host crm crm 127.0.0.1/32 reject
-
There is a line:
all allfrom127.0.0.1/32matches any database and role. -
The lines do not conflict as far as the server is concerned: it does not look for contradictions. It rejects a file only when it cannot parse a line.
-
The server never gets to
reject: the first match ishost all all … scram-sha-256, and that line asks for the password.
The server takes the first line where the type, database, role and address match. Here it is host all all 127.0.0.1/32 scram-sha-256: the password is right, so crm gets in, and the reject below is never read. For a ban to work, it goes above the permission.
Key points
-
SHOW hba_fileprints the path to the rules file; read and edit it with sudo. -
A rule line is a type, a database, a role, an address and a method;
localis the socket,hostis TCP. -
The first matching line decides;
rejectin it refuses even if a permission follows below.