Re: user can't access own database after creation - Mailing list pgsql-jdbc

On 2 Dec 2015, at 19:14, Dave Cramer <pg@fastcrypt.com> wrote:

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@postgresintl.com

www.postgresintl.com

On 2 December 2015 at 13:37, Nuno Zimas <nzimas@gmail.com> wrote:

On 2 Dec 2015, at 18:32, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Dec 2, 2015 at 9:45 AM, Nuno Zimas <nzimas@gmail.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 initialized
2015-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@Ubuntu-1204-precise-64-minimal ~ # sql -d db1 -U user1 -h 10.10.10.97 -W
Password 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

​