Search code examples
postgresqljdbcclojure

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

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!

Solution

  • 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.