Postgresql – Setting password authentication for default Postgres account

postgresqlpostgresql-9.3rhel6

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?

Best Answer

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.

Related Solutions

Postgresql – What’s the default superuser username/password for postgres after a new install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – Enable both ident and md5 authentication in PostgreSQL

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html indicates that the order of entries maters, specifically:

Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses.

(so try reversing the order - put the line you are adding at the top)

Best Answer

Related Solutions

Postgresql – What’s the default superuser username/password for postgres after a new install

Postgresql – Enable both ident and md5 authentication in PostgreSQL

Related Topic