org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

[J. Pablo Fernández]

Hello,

I have a very simple migrations.clj file:

(ns lobos.migrations

  (:refer-clojure :exclude [alter defonce drop bigint boolean char double float time])

  (:use (lobos [migration :only [defmigration]] core schema)

        lobos.config)

  (:require lobos.connectivity

            lobos.core

            [clojureql.core :as clq]))

;(println @(clq/table db :foo))

(defmigration create-admins

  (up [] (println "up"))

  (down [] (println "down")))

(lobos.core/run)

When I load it, I get the error "org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)". I presume somehow I'm trying to connect to the database as "lobos", but I'm not specifying that role/user anywhere:

(ns lobos.config

  (:require lobos.connectivity))

(def db

  {:classname "org.postgresql.Driver"

   :subprotocol "postgresql"

   :subname "//localhost:5432/mgr"})

(lobos.connectivity/open-global db)

If I load config.clj by itself it doesn't raise an exception (the first time). I have no idea where to go next as there are no stack traces.

Thanks.

[Nicolas Buduroi]

That's really weird, I don't see how this could happen! Anyway, I'll look further into this issue later today, but for now I would recommend to always use proper credential when accessing a database like Postgres.

Thanks for your time testing Lobos, this is invaluably helpful.

P.S.: I've never tested a connection without credential as I didn't even knew it was possible!

2011/8/18 J. Pablo Fernández <pup...@pupeno.com>

[J. Pablo Fernández]

In PostgreSQL, you can authenticate by password or by ident. By ident uses the unix process that's creating the connection and I use it to avoid having to remember or type passwords on my local machine. It's documented here: http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-IDENT

[J. Pablo Fernández]

Now something interesting to add, it doesn't fail on opening the connection, but on using it for migration. The same db spec works in ClojureQL.

[Nicolas Buduroi]

Then it might be a Postgres administration issue, does the role used by AUTH-IDENT have the rights to manipulate the target database schema?

I'm not qualified to help on administering database privileges, but normally I create a database using the user I connect with, then I'm sure it will have all the required priviledges. I've not yet seriously thought about how Lobos could help here, but the database/schema mismatch and the multiple ways to administer RDBMS privileges seems like an unsolvable problem!

2011/8/18 J. Pablo Fernández <pup...@pupeno.com>

Now something interesting to add, it doesn't fail on opening the connection, but on using it for migration. The same db spec works in ClojureQL.

[J. Pablo Fernández]

Yes, my user has access to everything. All my Rails projects are configured like that, no user, no password, and I just run rake db:create && rake db:migrate and it creates the database and runs all the migrations. But for some reason it seems lobos is connected as lobos instead of pupeno and that might be causing the problem. But there's no lobos user.

[J. Pablo Fernández]

 Anyway, I reduced config.clj to:

(ns lobos.config)

(def db

  {:classname "org.postgresql.Driver"

   :subprotocol "postgresql"

   :subname "//localhost:5432/mgr"

   :user "mgr"

   :password "chi1lie7Wai3ioP6aing3ooci"})

and migrations.clj to:

(ns lobos.migrations

  (:refer-clojure :exclude [alter defonce drop bigint boolean char double float time])

  (:use (lobos [migration :only [defmigration]] core schema)

        lobos.config))

(defmigration create-admins

  (up [] (println "up"))

  (down [] (println "down")))

and when I load the migrations file I still get the error:

user=> (load-file "/Users/pupeno/Projects/mgr/src/lobos/migrations.clj")

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

I deleted the lobos migrations table just in case, same error.

[J. Pablo Fernández]

Something weird is going on, if I do the same in lein repl, it works, but in La Clojure's REPL, it doesn't:

$ lein repl

REPL started; server listening on localhost port 33537

user=> (load-file "/Users/pupeno/Projects/mgr/src/lobos/migrations.clj")

[#<migrations$reify__2593 lobos.migrations$reify__2593@15136019>]

user=> 

I'm really puzzled now. If I run the REPL in the same way as La Clojure, then it fails in the same way:

$ /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/deploy.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/dt.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/javaws.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/jce.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/jconsole.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/management-agent.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/plugin.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/sa-jdi.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/alt-rt.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/alt-string.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/charsets.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/classes.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/jsse.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/../Classes/ui.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext/apple_provider.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext/dnsns.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext/localedata.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext/sunjce_provider.jar:/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext/sunpkcs11.jar:/Users/pupeno/Projects/mgr/classes:/Users/pupeno/Projects/mgr/lib/clojure-1.2.1.jar:/Users/pupeno/Projects/mgr/lib/clojure-contrib-1.2.0.jar:/Users/pupeno/Projects/mgr/lib/clout-0.4.1.jar:/Users/pupeno/Projects/mgr/lib/commons-codec-1.4.jar:/Users/pupeno/Projects/mgr/lib/commons-fileupload-1.2.1.jar:/Users/pupeno/Projects/mgr/lib/commons-io-1.4.jar:/Users/pupeno/Projects/mgr/lib/compojure-0.6.4.jar:/Users/pupeno/Projects/mgr/lib/core.incubator-0.1.0.jar:/Users/pupeno/Projects/mgr/lib/hiccup-0.3.6.jar:/Users/pupeno/Projects/mgr/lib/java.jdbc-0.0.3-20110701.155856-4.jar:/Users/pupeno/Projects/mgr/lib/jetty-6.1.26.jar:/Users/pupeno/Projects/mgr/lib/jetty-util-6.1.26.jar:/Users/pupeno/Projects/mgr/lib/postgresql-8.4-702.jdbc4.jar:/Users/pupeno/Projects/mgr/lib/ring-core-0.3.10.jar:/Users/pupeno/Projects/mgr/lib/ring-jetty-adapter-0.3.10.jar:/Users/pupeno/Projects/mgr/lib/ring-servlet-0.3.10.jar:/Users/pupeno/Projects/mgr/lib/servlet-api-2.5-20081211.jar:/Users/pupeno/Projects/mgr/lib/servlet-api-2.5.jar:/Users/pupeno/Projects/mgr/lib/tools.macro-0.1.0.jar:/Users/pupeno/Projects/mgr/lib/lobos-0.8.0-20110809.143608-5.jar:/Users/pupeno/Projects/mgr/lib/clojureql-1.0.1.jar:/Users/pupeno/Projects/mgr/src:/Users/pupeno/Projects/mgr/test clojure.main

Clojure 1.2.1

user=> (load-file "/Users/pupeno/Projects/mgr/src/lobos/migrations.clj")

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

user=> 

[J. Pablo Fernández]

Hello,

I created the minimum reproduction of this issue.

I created a fresh lein project, and set project.clj to:

(defproject foo "1.0.0-SNAPSHOT"

  :description "FIXME: write description"

  :dependencies [[org.clojure/clojure "1.2.1"]

                 [lobos "0.8.0-SNAPSHOT"]

                 [org.clojure/java.jdbc "0.0.3-SNAPSHOT"]

                 [postgresql/postgresql "8.4-702.jdbc4"]])

I installed the dependencies (lein deps), configured La Clojure to not include any jars except those in the lib directory (no jars from the SDK) and created two files, lobos/config.clj with:

(ns lobos.config)

(def db

  {:classname "org.postgresql.Driver"

   :subprotocol "postgresql"

   :subname "//localhost:5432/mgr"})

and lobos/migrations.clj with:

(ns lobos.migrations

  (:use lobos.config))

Yes, that's it, nothing more. When you load migrations.clj you get the error:

user=> (load-file "/Users/pupeno/Temporary/foo/src/lobos/migrations.clj")

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

You can reproduce it without La Clojure by running it like this:

$ /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /Users/pupeno/Temporary/foo/classes:/Users/pupeno/Temporary/foo/lib/clojure-1.2.1.jar:/Users/pupeno/Temporary/foo/lib/postgresql-8.4-702.jdbc4.jar:/Users/pupeno/Temporary/foo/lib/java.jdbc-0.0.3-SNAPSHOT.jar:/Users/pupeno/Temporary/foo/lib/lobos-0.8.0-20110809.143608-5.jar:/Users/pupeno/Temporary/foo/lib/clojure-contrib-1.2.0.jar:/Users/pupeno/Temporary/foo/test:/Users/pupeno/Temporary/foo/src clojure.main

Clojure 1.2.1

user=> user=> (load-file "/Users/pupeno/Temporary/foo/src/lobos/migrations.clj")

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

Note that removing lobos-0.8.0-20110809.143608-5.jar from the classpath stops this problem:

$ /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /Users/pupeno/Temporary/foo/classes:/Users/pupeno/Temporary/foo/lib/clojure-1.2.1.jar:/Users/pupeno/Temporary/foo/lib/postgresql-8.4-702.jdbc4.jar:/Users/pupeno/Temporary/foo/lib/java.jdbc-0.0.3-SNAPSHOT.jar:/Users/pupeno/Temporary/foo/lib/clojure-contrib-1.2.0.jar:/Users/pupeno/Temporary/foo/test:/Users/pupeno/Temporary/foo/src clojure.main

Clojure 1.2.1

user=> (load-file "/Users/pupeno/Temporary/foo/src/lobos/migrations.clj")

nil

and using lein repl works too.

[J. Pablo Fernández]

I'm still trying to figure this out, but I forgot to show the stack trace before:

Clojure 1.2.1

user=> (require 'lobos.config)

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

user=> (.printStackTrace *e)

org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist (config.clj:1)

at clojure.lang.Compiler.eval(Compiler.java:5440)

at clojure.lang.Compiler.load(Compiler.java:5857)

at clojure.lang.RT.loadResourceScript(RT.java:340)

at clojure.lang.RT.loadResourceScript(RT.java:331)

at clojure.lang.RT.load(RT.java:409)

at clojure.lang.RT.load(RT.java:381)

at clojure.core$load$fn__4519.invoke(core.clj:4915)

at clojure.core$load.doInvoke(core.clj:4914)

at clojure.lang.RestFn.invoke(RestFn.java:408)

at clojure.core$load_one.invoke(core.clj:4729)

at clojure.core$load_lib.doInvoke(core.clj:4766)

at clojure.lang.RestFn.applyTo(RestFn.java:142)

at clojure.core$apply.invoke(core.clj:542)

at clojure.core$load_libs.doInvoke(core.clj:4800)

at clojure.lang.RestFn.applyTo(RestFn.java:137)

at clojure.core$apply.invoke(core.clj:542)

at clojure.core$require.doInvoke(core.clj:4881)

at clojure.lang.RestFn.invoke(RestFn.java:408)

at user$eval1.invoke(NO_SOURCE_FILE:1)

at clojure.lang.Compiler.eval(Compiler.java:5424)

at clojure.lang.Compiler.eval(Compiler.java:5391)

at clojure.core$eval.invoke(core.clj:2382)

at clojure.main$repl$read_eval_print__5632.invoke(main.clj:183)

at clojure.main$repl$fn__5637.invoke(main.clj:204)

at clojure.main$repl.doInvoke(main.clj:204)

at clojure.lang.RestFn.invoke(RestFn.java:421)

at clojure.main$repl_opt.invoke(main.clj:262)

at clojure.main$main.doInvoke(main.clj:355)

at clojure.lang.RestFn.invoke(RestFn.java:397)

at clojure.lang.Var.invoke(Var.java:361)

at clojure.lang.AFn.applyToHelper(AFn.java:159)

at clojure.lang.Var.applyTo(Var.java:482)

at clojure.main.main(main.java:37)

Caused by: org.postgresql.util.PSQLException: FATAL: role "lobos" does not exist

at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:464)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)

at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)

at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)

at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)

at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)

at org.postgresql.Driver.makeConnection(Driver.java:393)

at org.postgresql.Driver.connect(Driver.java:267)

at java.sql.DriverManager.getConnection(DriverManager.java:582)

at java.sql.DriverManager.getConnection(DriverManager.java:154)

at clojure.contrib.sql.internal$get_connection.invoke(internal.clj:85)

at lobos.connectivity$get_cnx.invoke(connectivity.clj:46)

at lobos.connectivity$open_global.invoke(connectivity.clj:69)

at lobos.connectivity$open_global.invoke(connectivity.clj:62)

at lobos.config$eval662.invoke(config.clj:17)

at clojure.lang.Compiler.eval(Compiler.java:5424)

... 32 more

[J. Pablo Fernández]

To add some debugging output from inside Lobos, I built a jar locally for Lobos and made my project use that instead of /lobos-0.8.0-20110809.143608-5.jar and guess what, the problem went away, it worked. Actually, that version 20110809, seems not to be the latest one, am I right?

[Nicolas Buduroi]

I think I've found what might be the problem. The create-migrations-table function wasn't properly using the given schema name, thus it was trying to create the migrations table inside the default schema, for which the role you were using didn't have the proper rights. At least that's a theory, I'll probably release a new snapshot version later today or tomorrow so that you can test it.

2011/8/19 J. Pablo Fernández <pup...@pupeno.com>

[J. Pablo Fernández]

Seems to be working now, thanks for all the work Nicolas.