glassfish 4.0 admin console connection pool ping fails

Question

With this error: Ping Connection Pool failed for postgreSQLPool. Connection could not be allocated because: FATAL: database "/localhost:5432/mydatabase" does not exist

Pool configuration is as follows:

Pool Name: postgreSQLPool 
Resource Type: javax.sql.ConnectionPoolDataSource
Datasource Classname: org.postgresql.ds.PGConnectionPoolDataSource
portNumber: 5432
databaseName: myDatabase
datasourceName: myDatabaseDS
roleName: myRole
networkProtocol: jdbc:postgresql:
serverName: //localhost
user: myUser
password: myPassword

The postgresql server is running because I can connect to the database using the same connection values as above.

postgresql-9.3-1100.jdbc4.jar is in $glassfish_home/domains/domain1/lib directory

Any help would be appreciated,

Dave

Answer 1

Your pool configuration looks wrong. serverName should not have // in it and networkProtocol is not for the JDBC URL.

Your config should look more like this:

Pool Name: postgreSQLPool 
portNumber: 5432
databaseName: myDatabase
datasourceName: myDatabaseDS
roleName: myRole
serverName: localhost
user: myUser
password: myPassword

(I'm not at all sure that roleName or datasourceName are needed or correct, but don't have time to further review the documentation to check).

If you still have problems, try falling back to the basic data source:

Resource Type: javax.sql.DataSource
Datasource Classname: org.postgresql.ds.PGSimpleDataSource

For info on the resource type, see PGConnectionPoolDataSource, ConnectionPoolDataSource.

See:

• the docs on the create-jdbc-connection-pool console command
• this handy wiki entry for LifeRay which shows a sample config.

Answer 2

Here:
<DB_NAME>=myDatabase
<DB_PORT>=5432
<DB_USER>=myUser
<DB_USER_PWD>=myPassword
<SCHEMA_NAME>=mySchema
<DB_CONN_POOL>=postgreSQLPool
<DATA_SRC>=myDatabaseDS

Procedure:
a) Permit firewall glassfish server , postgres port, for example in centos7

#systemctl enable firewalld
#firewall --add-port=<DB_PORT>/tcp --permanent
##systemctl reload firewalld (<---doesn't work)
#firewall-cmd --reload

b) Configure pg_hba.conf for SSL login for the user:

hostnossl        all        all           0.0.0.0/0        reject
hostssl          all        postgres      0.0.0.0/0        reject
hostssl          <DB_NAME>  <USER_NAME>   <IR_ADDR>        md5

c) Create User, DB, Schema and grant previleges to the user, on required objects, for example:

###CREATE USER
sudo -u postgres psql -p <DB_PORT> -d postgres -c "CREATE USER <USER_NAME> ENCRYPTED PASSWORD 'myUser_pwd';"
###CREATE DB
sudo -u postgres psql -p <DB_PORT> -d postgres -c "CREATE DATABASE <DB_NAME> OWNER <USER_NAME> ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "CREATE SCHEMA "<SCHEMA_NAME>";"
###REVOKE PREVILEGES ON <SCHEMA_NAME> from public
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "REVOKE ALL ON SCHEMA "<SCHEMA_NAME>" FROM "public";"
###GRANT USAGE ON <SCHEMA_NAME> TO <DB_USER>
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "GRANT USAGE ON SCHEMA "<SCHEMA_NAME>" TO <USER_NAME>;"
###GRANT PREVILEGES ON TABLES OF <SCHEMA_NAME> TO <DB_USER>
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "GRANT ALL ON TABLES IN SCHEMA "<SCHEMA_NAME>" TO <USER_NAME>;"
###GRANT PREVILEGES ON SEQUENCES OF <SCHEMA_NAME> TO <DB_USER>
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "GRANT ALL ON SEQUENCES IN SCHEMA "<SCHEMA_NAME>" TO <USER_NAME>;"
###GRANT PREVILEGES ON FUNCTIONS OF <SCHEMA_NAME> TO <DB_USER>
sudo -u postgres psql -p <DB_PORT> -d <DB_NAME> -c "GRANT ALL ON FUNCTIONS IN SCHEMA "<SCHEMA_NAME>" TO <USER_NAME>;"

d) Restart Postgresql to config changes take place
e) Place the correct postgresql jdbc4 jar (corresponding to version of the server used eg. 9.3/9.4/10.1) in the lib folder of the domain ((ie).../glassfish/domains/<DOMAIN_NAME>/lib)
f) Restart Glassfish to config changes take place

use commands:

asadmin create-jdbc-connection-pool --datasourceClassName "org.postgresql.ds.PGConnectionPoolDataSource" --restype "javax.sql.ConnectionPoolDataSource" --property "ServerName=localhost:PortNumber=<DB_PORT>:DatabaseName=<DB_NAME>:User=<DB_USER>:Password=<DB_USER_PWD>" "<DB_CONN_POOL>"

and

asadmin create-jdbc-resource --connectionpoolid "<DB_CONN_POOL>" "jdbc/<DATA_SRC>"