On a new PostgreSQL server, the following error message was received, when a user tried to use psql:
$ psql -h 127.0.0.1 -U dbuser
Password for user dbuser: *****
psql: FATAL: password authentication failed for user "dbuser"
FATAL: password authentication failed for user "dbuser"
The logs showed the following entries:
[11834] dbuser@dbuser FATAL: password authentication failed for user "dbuser"
[11834] dbuser@dbuser DETAIL: Password does not match for user "dbuser".
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
[11835] dbuser@dbuser FATAL: password authentication failed for user "dbuser"
[11835] dbuser@dbuser DETAIL: Password does not match for user "dbuser".
Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5"
Obviously the first question in such a situation is: Was the entered password correct? And in the special case of PostgreSQL a second question should always come into mind: Is there a correct configuration in pg_hba.conf for the attempted authentication?
The authentication obviously correctly matched a line in pg_hba.conf, as could also be seen in the log file:
# grep 127.0.0.1 /etc/postgresql/9.6/main/pg_hba.conf
host all all 127.0.0.1/32 md5
After it was verified that the entered password was actually correct, the creation of the SQL user was once more analyzed. This turned out to be a manual user creation using an SQL query (old habits):
postgres=# CREATE USER dbuser WITH PASSWORD 'mysecretpassword';
When using the createuser command, a special parameter -e can be used. This will show the actual SQL queries sent to PostgreSQL:
postgres@pgserver:~$ createuser -P -E -e dbuser
Enter password for new role: *****
Enter it again: *****
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE dbuser ENCRYPTED PASSWORD 'md5b3e88aa92b0943f1d2eed5cc618255e8' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
A significant difference is the usage of "ENCRYPTED PASSWORD" vs. "WITH PASSWORD" from the manual SQL query. Would that mean that the md5 password encryption (as defined in pg_hba.conf) didn't work because the password from the manual user creation (using SQL query) was understood as plain text?
This can be verified again, now that the user was created using createuser:
$ psql -h 127.0.0.1 -p 5432 -U dbuser
Password for user dbuser: *****
psql (9.6.16)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
dbuser=>
Indeed, the password authentication worked this time and the user is logged in!
The documentation of the createuser command shows that there are two different options available whether or not to use an encrypted password:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SYSID uid
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP groupname [, ...]
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'abstime'
Particularly these keywords are described as:
ENCRYPTED | UNENCRYPTED
These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.
It also says that if neither of these keywords was used, the system default would be applied. To check the current PostgreSQL setting whether or not to encrypt passwords by default, the following SQL query can be run:
postgres@pgserver:~$ psql -qAtc "SELECT name, setting FROM pg_settings WHERE name = 'password_encryption'"
password_encryption|on
It can also be checked in the configuration file, in case the default should be changed:
root@pgserver:~# grep password_encryption /etc/postgresql/9.6/main/postgresql.conf
#password_encryption = on
The manual user creation using an SQL query did not contain either ENCRYPTED nor UNENCRYPTED keywords. This means that PostgreSQL automatically applied the default: ENCRYPTED. The given password ("mysecretpassword") was therefore encrypted and stored in the database. Logins with exactly this plain password ("mysecretpassword") would of course fail, because it does not match the encrypted stored value.
Although the manual user creation using an SQL query still works, the newer createuser command should be used primarily. This will avoid errors or mistakes from remembered SQL queries from earlier PostgreSQL versions. As mentioned, old habits.
AWS Android Ansible Apache Apple Atlassian Automation BSD Backup Bash Bluecoat CMS Chef Cloud Consul Container Containers CouchDB DB DNS Database Databases Docker ELK ElasticSearch Elasticsearch Filebeat FreeBSD GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Icingaweb2 InfluxDB Internet Java Kibana Kubernetes LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Rancher SSL Security Shell SmartOS Solaris Surveillance SystemD TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder