Unable to connect to PostgreSQL database using network connection in Plesk

Symptoms

• Attempt to access PostgreSQL database using network connection to localhost:5432 fails:

  CONFIG_TEXT: psql: FATAL: Ident authentication failed for user "pgdb1_user1"

• Plesk websites installed with Ruby application show the following error:

  CONFIG_TEXT: 500 Internal Server Error
  If you are the administrator of this website, then please read this web application's log file and/or the web server's log file to find out what went wrong.

Cause

PostgreSQL is configured to authenticate network connections using ident service, which is deprecated.

Resolution

1. Connect to the server via SSH.

2. Change authentication configuration of host type from ident to password in pg_hba.conffile.

   /var/lib/pgsql/data/pg_hba.conf on RedHat-based systems

   /var/lib/postgresql/9.1/main/pg_hba.confon Debian-based systems

   BEFORE:

   CONFIG_TEXT: host all all 127.0.0.1/32 ident
   host all all ::1/128 ident

   AFTER:

   CONFIG_TEXT: host all all 127.0.0.1/32 password
   host all all ::1/128 password

3. (Optional step) Create new line in same format for any external host:

   CONFIG_TEXT: host all all 203.0.113.2/32 password

4. Restart PostgreSQL service to apply changes:

   # service postgresql restart