0

I am unable to login and use a created database because I either receive a password or peer authentication failure. Below outlines how I set things up, how I've tried to fix the encountered issues, and further potentially relevant information.

I am using postgresql-9.5, and when I downloaded postgres, it was with a different linux mint user/username than I am currently attempting to access it from. All commands below were sent from the new user/username.

I created a new postgres database and a new user with a password with the following commands: sudo su - postgres

$ psql

postgres=# CREATE DATABASE PostgresDB;

postgres=# CREATE USER PostgresUser WITH PASSWORD 'password';

postgres=# ALTER ROLE PostgressUser SET default_transaction_isolation TO 'read committed'

postgres=# GRANT ALL PRIVILEGES ON DATABASE PostgresDB TO PostgresUser;

I then proceeded to attempt a login in a number of ways:

  • psql -d PostgresDB -U PostgresUser

Returns: psql: FATAL: Peer authentication failed for user "PostgresUser"

  • psql -d PostgresDB -U PostgresUser -W and password entry
  • psql -d, psql -U PostgresUser -h 127.0.0.1 -d PostgresDB and password entry

Each Returns: psql: FATAL: password authentication failed for user "PostgresUser"

I made all of the above attempts to login before and after resetting the password with: ALTER PostgresUser WITH PASSWORD 'password'

I have checked the pg_hba.conf file, the important parts of which, read:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# 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     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
TQM
  • 89
  • 1
  • 5

2 Answers2

0

Have you tried to look at the log file?

To solve the riddle, try this:

SELECT usename, usesysid FROM pg_user;

You will notice that the user name is in lower case.

That is because PostgreSQL folds all characters in an SQL statement to lower case (the SQL standard decrees that SQL is case insensitive).

The shell, however, is case sensitive, so you try to connect with a user name that does not exist.

Once you have made that experience, you'll probably understand why it is a good idea to always use only lower case characters and digits in database object names.

If you insist on upper case characters, you'll have to surround the name with double quotes.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This information is very good to know, thank you! Unfortunately, I tried to create a new database in all lowercase, and log in with all lowercase, but I'm having the same issues unfortunately. – TQM May 19 '18 at 02:29
  • OK, and what is in the log? What does the query yield? – Laurenz Albe May 19 '18 at 12:45
0

While it did not work to retry everything using lowercase database and user names, I was able to solve the issue through another method.

Using the instructions outlined in the accepted answer for this question (How to thoroughly purge and reinstall postgresql on ubuntu?), I purged everything related to PostgresSQL on my computer, and reinstalled. I was not able to log in with: psql -d postgresdb -U postgresdbuser or psql -d postgresdb -U postgresdbuser -W.

However, I was able to login to the database with: psql -U postgresdbuser -h 127.0.0.1 -d postgresdb

TQM
  • 89
  • 1
  • 5