Novice's Thoughts

Fresh PostgreSQL configuration in Pop OS 24.04

I have installed postgres 18 via official download page. After that I ran psql and I got,

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "mahesh" does not exist

Then I ran psql -U postgres and I got,

psql -U postgres

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

Then I ran sudo -u postgres psql, this worked.

This is due to OS user and DB user conflict. When I run as my user, postgres won't allow to access postgres user.

This is due to peer authentication.

The solution,

sudo -u postgres psql -c "SHOW hba_file;"

hba - host-based authentication

Result,

etc/postgresql/18/main/pg_hba.conf

In this file edit the postgres connection auth from peer to trust. So that you can connect without password.

Then sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

Then switch from trust to md5

Make sure to apply systemctl reload postgresql after editing the file each time.

Now you can do psql -U postgres

I use https://github.com/dbcli/pgcli - this is good for cli usage.