I set the password for the user using the syntax:

postgres=# alter user pavel with password '1'; ALTER ROLE Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- pavel | Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+----------- postgres | 10 | t | t | t | t | md521.............................. | | pavel | 18066 | t | f | f | f | md58f.............................. | |

But when I enter under the role of pavel

 $ psql -U pavel postgres

My password is not requested.

Why doesn't postgres ask me for a password, although I don't use sudo ? What could be the reasons? (it all works under linux).

pg_hba.conf

 # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5
  • What's in the pg_hba.conf ? Maybe there the trust method is registered for your user or host? .pgpass n't there a .pgpass file in the root of your home directory? - Sergey Gornostaev
  • @Sergey Gornostaev .pgpass did not find and in pg_hba.conf there a lot of things added to the question that seemed to me to relate to your question. - Pavel

1 answer 1

The psql client will not ask for a password if it already knows the password ( .pgpass , if it exists) or if the server itself does not request a password transfer.

What and when the server requests is described in pg_hba.conf , the rules are checked in order from top to bottom. And another important point - without specifying the -h (hostname) key, psql will try to connect via a unix-socket.

In your pg_hba.conf first rule

 local all postgres peer

not applicable because you are not trying to connect as a postgres user. But the following rule:

 local all all peer

Describes just the right case, the local connection - i.e. on unix-socket, any user and any database. The specified authentication type is peer . This check does not use a password, instead it checks that the unix user who connects to the database is equal to the username in the database. If your console is open under the pavel user, this is why you are logged in and logged in.

Separately about sudo - postgresql does not pay attention to sudo itself. If you do not have a root account in the database, then the root will not pass authorization even though it is root. Similarly, if you make md5 authorization instead of peer in hg_hba for a unix-socket, then a password will be required for the postgres user, also when logging in via sudo -u postgres psql postgres .

Next line

 host all all 127.0.0.1/32 md5

For connections via tcp / ip, the loopback is already specified as md5 - i.e. the server requires the client to send a hashed password (what exactly hash besides the password I do not remember). So, if you try to do

 psql -U pavel -h 127.0.0.1 postgres

That password will be requested.