Re: user can't access own database after creation

One thing I noticed is that psql is connecting via SSL.

Have a look at the pg logs to see what the error is

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 2 December 2015 at 13:37, Nuno Zimas <nzimas(at)gmail(dot)com> wrote:

>
> On 2 Dec 2015, at 18:32, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
> 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”​.
>
>
> I don’t use any GUI to admin the postgresql, so by admin UI i actually
> mean the admin interact of the Java application that is generating the
> reported error.
>
> Cheers,
> Nuno.
>
>
> Dave
> ​
>
>
>