Password authentication fails when trying to connect to postgresql from clojure with jdbc

Question

I'm learning web development in Clojure and I'm not at all an expert in either PostgreSQL or JDBC.

I'm going through the "Web development with Clojure" book and I'm stuck because I cannot connect to a PostgreSQL database, although I believe I have followed the tutorial step by step.

I connected to psql with

sudo -u postgres psql

Then I created an admin user with password 'admin'

postgres=# CREATE USER admin WITH PASSWORD 'admin';
CREATE ROLE

Then I created a database named reporting :

postgres=# CREATE DATABASE reporting OWNER admin;
CREATE DATABASE

No complaints so far.

Then from my clojure code, I attempt to connect to the database and create a table :

(ns reporting-example.models.db
  (:require [clojure.java.jdbc :as sql]))

;defining the db connection
(def db 
  {:subprotocol "postgresql"
   :subname "//localhost/reporting"
   :user "admin"
   :password "admin"})

;function for creating an 'employee' table
(defn create-employee-table []
  (sql/create-table :employee
                    [:name "varchar(50)"]
                    [:occupation "varchar(50)"]
                    [:place "varchar(50)"]
                    [:country "varchar(50)"])  
  )

; then trying to actually create the table, here's the part that breaks with an exception :
(sql/with-connection db
    (create-employee-table))

And I'm getting an ugly :

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "admin"

Which does not make sense to me, the credentials seem fine.

I tried the above code from both the Counterclockwise REPL and the Leiningen REPL. I'm on Ubuntu 13.10, if that matters.

Could someone explain to me what I am doing wrong here? Thanks in advance!

Answer 1

Since you can't connect with psql using the same settings there are really three possibilities:

• wrong/typo'd password
• wrong/typo'd username
• you are not connecting to the same server as you created the account on.

To check the latter, connect however you did to create the account and then run

show port;

Make sure it is the same as what you specify in your app. That isn't perfect - two pg instances can share a port if one isn't listening on TCP/IP and has a different socket directory - but it is a good first test.

You should also examine the PostgreSQL server error logs, as they may contain more detailed information about the nature of the authentication failure.

Answer 2

If you have not tried this already, review your pg_hba.conf file. It will be named something like

• /etc/postgresql/9.6/main/pg_hba.conf (Ubuntu 16.04)
• /var/lib/pgsql/9.3/data/pg_hba.conf (Fedora 20);

You may have to use find / -name pg_hba.conf to locate it.

At the bottom of the file, change the METHOD values to trust for local testing (see postgres docs for full information). Restart postgres to ensure everything is started clean and the new params are read:

> sudo systemctl restart postgresql     # ubuntu

Hopefully this will cure your woes. It solved my problems on Ubuntu/Fedora.