Troubleshooting Scenario:
We have a web application that relies on the PostgreSQL 13 database present on our server. However, the connection to the database is not working. The task is to identify and resolve the issue causing this connection failure. A database user account has been created for the application for connection purposes:
Database name: app1
user: app1user
password: app1user.
Investigation, Root Cause Analysis and troubleshooting
First step is to check that status of PostgreSQL instance on our linux terminal using systemctl:
# sudo systemctl status postgresql
Result: We see that the database is running without issues on the server
Secondly, we log into PostgreSQL as the Postgres user to run some checks to validate user credentials:
# sudo -u postgres psql
On the Postgres prompt we check if the user and database actually exist:
postgres-# \l
Result: Database exists
Additionally, we need to check of the database user with the credentials we are given above also actually exists in the database:
postgres# \du
Result: User exists
The Third step is to test the database connection given the details above using the command:
PGPASSWORD=app1user psql -h 127.0.0.1 -d app1 -U app1user -c ‘\q’
Result: Connection Fails
From the error above we see that there is an issue with the pg_bha.conf file that handles host-based authentication in the Postgresql install
Here, we refer to the official PostgreSQL to understand the file. According to the Postgres documentation: PostgreSQL: Documentation: 17: 20.1. The pg_hba.conf File
We can see that the file handles host-based authentication settings, and we know that on linus hosts, Configuration files are stored in the /etc directory, so we browse to the: /etc/postgresql/13/main/ where we locate the pg_hba.conf file
Fixing the error
Using the Vim editor, we, edit the pg_hba.conf file to include our connection parameters in the section that handles client authentication:
We then save this config file and reload the postgresql service for the changes to take effect, run the command:# sudo systemctl reload postgresql && sudo systemctl restart postgresql
Then we run the test connection command again: this time, with no errors:
As we have no errors thrown into our terminal (standard output – sdout) this means that the application database user credentials has successfully connected to the database.
Brief Explanation of the connection test command:
# PGPASSWORD=app1user psql -h 127.0.0.1 -d app1 -U app1user -c ‘\q’
| PGPASSWORD=app1user | Sets the environment variable PGPASSWORD temporarily for this command only, so psql can use it as the password for app1user without prompting. |
| psql | The PostgreSQL command-line client. |
| -h 127.0.0.1 | Connects to PostgreSQL over TCP using localhost (IPv4). |
| -d app1 | Specifies the database name (app1) to connect to. |
| -U app1user | Specifies the username (app1user) to authenticate as |
| -c ‘\q’ | Executes a command (\q) inside psql. \q tells psql to quit immediately—so this is just a quick connect-and-disconnect test. |