Re: user can't access own database after creation

On Wed, Dec 2, 2015 at 9:45 AM, Nuno Zimas <nzimas(at)gmail(dot)com> wrote:

> Dear everyone,
>
> When attempting to access a PostgreSQL 9.4 database via JDBC on my local
> Windows environment, everything works as intended. The database user can
> access the database they have previously created from the admin UI of the
> application.
>
> Things take a turn for the worse when trying to perform the exact same
> operation against any PostgreSQL 9.x backend running remotely on either
> Ubuntu 12.04 / 14.04 / 15.04. The application log keeps spewing the error
> below.
>
> 2015-12-01 11:04:25,291 WARN [http-nio-8080-exec-5] log.Log4jLogger - the entity manager of "Task" has not been initialized2015-12-01 11:04:25,292 ERROR [http-nio-8080-exec-5] log.Log4jLogger - u90srlidr034307j user1 jdbc:postgresql://10.10.10.97:5432/task0bg96qj544p0yu37
>
> Changing the JDBC URL to jdbc:postgresql://
> 10.10.10.100:5432/db1?user=user1&password=password&ssl=true did not make
> any difference.
>
> I have ascertained that postgres can log in via psql using the details
> JDBC is suppilied with. Got similar results on PoatgreSQL 9.2, 9.3 and 9.4
>
> root(at)Ubuntu-1204-precise-64-minimal ~ # sql -d db1 -U user1 -h 10.10.10.97 -WPassword for user user1:
> psql (9.4.5, server 9.4.5)
> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)Type "help" for help.
> db1=#
>
> Moreover, the auth method is set to trust on pg_hba.conf, meaning that
> PostgreSQL will accept all kinds of auth methods from the specified host.
>
> host all all 10.10.10.99/32 trust
>
> ​​
> To recap, once a user is added to the database backend from the admin UI
> (via JDBC), they can create databases themselves, but cannot access them.
>
> I've compared the pg_hba.conf and postgresql.comf files on both my
> localhost and the remote server and did not find anything outstanding.
>
> The database JDBC is connecting to appears to have correct permissions.
>
> db1 | user1 | UTF8 | C | C | =Tc/user1 +
> | | | | | user1=CTc/user1
>
> The JDBC-PostreSQL driver used by the application is
> postgresql-9.4-1201.jdbc4.jar.
> You may find here a partial dump of the PostgreSQL log, which shows
> queries performed when the error happens,
> http://pastebin.com/ZZb5a55W
>
> Finally the relevant portion of the Java source file where the connection
> is defined looks as follows:
>
> pool.initConnectionPool(driver, appProfile.getURI(), dbLogin, dbPwd);
> Map<String, String> properties = new HashMap<String,
> String>();
> properties.put(PersistenceUnitProperties.JDBC_DRIVER,
> driver);
> properties.put(PersistenceUnitProperties.JDBC_USER,
> dbLogin);
> properties.put(PersistenceUnitProperties.JDBC_PASSWORD,
> dbPwd);
> //properties.put(PersistenceUnitProperties.JDBC_PROPERTY,
> dbPwd);
>
> //properties.put(PersistenceUnitProperties.JDBC_URL,
> appProfile.getURI());
>
>
> The full version of the file can be found here:
>
> https://github.com/flabser/2next/blob/master/src/com/flabser/dataengine/DatabaseCore.java
>
> Thank you very much for reading.
>
>
​I've read this twice and maybe I'm being dense but I see nothing here that
is readily actionable. Nor does it suggest a problem with the JDBC driver.

​"""​
​To recap, once a user is added to the database backend from the admin UI
(via JDBC), they can create databases themselves, but cannot access them.
"""
​
You should try to organize and simplify you situation better, and hopefully
in so doing will find your problem. I'm doubting it has anything to do
with the driver and am not inclined to start debugging your application
from the limited information provided. Unless you are referring to pgAdmin
somewhere in here but if you are you should use its proper name and not
"admin UI"​.

Dave
​