6

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!

2
  • Can you connect successfully with the same settings using psql? e.g. psql -h localhost -U admin, entering the password at the prompt? My first guess is that you might have two PostgreSQL installs listening on different ports, and you created the user on a different one to the one you're connecting to with clojure. Commented Apr 7, 2014 at 3:03
  • No, I cannot connect that way either. The error message is the same. Commented Apr 7, 2014 at 19:04

2 Answers 2

5

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.

Sign up to request clarification or add additional context in comments.

2 Comments

You guessed right, the 'show port;' command yielded 5433, whereas 5432 is the assumed default. So now my Clojure program works, thank you very much :)! Having said that, is there a simple way to change this uncomfortable 2-pg-instances situation?
@ValentinWaeselynck I suggest a new question for that, with a bunch more detail on your OS/version, how you installed everything, etc. serverfault.com or dba.stackexchange.com
4

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.