0

I'm trying to resolve an issue that was found during a Nessus scan involving the default postgres account not having authentication.

https://www.tenable.com/plugins/nessus/10483

I've been trying to add md5 authentication to the postgres user, and I'm not sure what I'm doing wrong. I generated an md5sum for the password with the following:

echo -n test | md5sum

I copied that hash and applied it to the following command:

psql -c "alter user postgres password 'md5{hash here}';"

And I got the "ALTER ROLE" feedback. I then tried modifying my pg_hba.conf file to show the following. For this example, "other" is the user used by our application which cannot have authentication or it breaks the functionality (we would have to rewrite a significant amount of code to allow it).

local postgres postgres md5
local other other trust
host other other 0.0.0.0/0 trust
host postgres postgres 0.0.0.0/0 md5
host replication postgres 0.0.0.0/0 md5
host all all ::1/128 ident

Whereas the original file looked like this:

local all all trust
host all all 0.0.0.0/0 trust
host replication postgres 0.0.0.0/0 trust
host all all ::1/128 ident

With these changes, any time I try to enter the password for postgres, it gives me the following error:

psql: server closed the connection unexpectedly
          This probably means the server terminated abnormally
           before or while processing the request.

And then trying to connect to the "other" user results in this error:

psql:  could not connect to server: Connection refused 
           Is the server running locally and accepting
           connections on Unix domain socket "/tmp/.s.PGSQL.9999"?

The postgresql.conf file has the line:

listen_addresses = *

Am I missing something? Both users were able to connect before I made these changes via the "trust" method.

Also, was my method of generating and entering the md5sum of the password the correct way to set it up so that you could connect by providing the password, not the hash, when connecting?

EDIT: After finding and checking the log files, I found the following:

>FATAL: password authentication failed for user "postgres"
>DETAIL: Connection matched pg_hba.conf line 83: "host all postgres 0.0.0.0/0 md5"

This appears several times for the same timestamp. This was just after rebooting after making changes to the pg_hba.conf file. I hadn't even tried to login to postgres yet. Something in the background must be trying to connect. Do I need to have a credentials file somewhere storing the password hash or something?

EDIT: I managed to get it to require a password for just the postgres account by setting the authentication method in pg_hba.conf to 'password' instead of md5 and using the cleartext password when altering the user, but now, the service seems to be intermittently crashing. I'll log into one account, quit, try the other account, quit, then try the other, and suddenly it will say:

psql:  could not connect to server: No such file or directory
           Is the server running locally and accepting
           connections on Unix domain socket "/tmp/.s.PGSQL.9999"?

Anyone know what's causing this?

Darin Beaudreau
  • 137
  • 8

1 Answers1

1

Firstly, the password hashing scheme used by Postgres is a little bit more complex, than an MD5 hash of the password: it is the MD5 hash of the concatenation of password and username (cf. this question). Therefore in your example you should use:

ALTER ROLE postgres WITH PASSWORD 'md5633bc3c3d823be2a52d3dff94031e2c2';

where 633bc3c3d823be2a52d3dff94031e2c2 is the hash of testpostgres. However this is equivalent to:

ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'test';

or without the ENCRYPTED keyword if the password_encryption parameter is on (default).

Your second problem is caused by maintenance tasks being unable to log into Postgres. You should allow user postgres to log in without a password using a Unix domain socket:

local all postgres peer

It is perfectly safe, since Unix domain sockets identify the calling user.

Piotr P. Karwasz
  • 5,748
  • 2
  • 11
  • 21
  • Ok, but the host connections are the ones not working properly. The log is telling me the host connection (logging in via the shell) is the one giving me the problem. I have the line "host all postgres 0.0.0.0/0 md5" in my pg_hba.conf file and used the method you provided to generate the correct md5 hash, but I still can't login without it telling me it couldn't connect to the server. This is after setting local postgres connections to peer. – Darin Beaudreau Feb 11 '20 at 13:24
  • By default `psql` attempts to connect through a Unix domain socket (`/tmp/.s.PGSQL.9999`), which matches a `local` record in `pg_hba.conf`. If you want to use the `host` record, connect through TCP/IP: `psql -h localhost -U postgres`, probably you'll need `-p 9999`, as you are running postgres on a non standard port. – Piotr P. Karwasz Feb 12 '20 at 03:36