PostgreSQL datasource problem with Railo

397 views
Skip to first unread message

Lauren Paul

unread,
Apr 2, 2015, 12:31:30 PM4/2/15
to lu...@googlegroups.com

Hi Lucee folks,

 

We are currently running Railo 4.1.2.005 final.  

 

We want to create a datasource in Railo that will execute a database query on a Heroku Postgres database.

 

When we try to create the datasource we get this message:

 

FATAL: no pg_hba.conf entry for host “131.204.97.7”, user “kgdxxwchqhfwto”, database “d200shbvfilcsq”, SSL off

 

 

How can we correctly create this datasource?  And if SSL is required, where in Railo do we tell it to require SSL?  I've tried entering in the Host field "sslmode=require" after the hostname and it still errors out.


PostgreSQLerror.png

Igal @ Lucee.org

unread,
Apr 2, 2015, 12:34:05 PM4/2/15
to lu...@googlegroups.com
FATAL: no pg_hba.conf entry for host
this error is coming from Postgres so you should check the docs for that and see where the pg_hba.conf is expected to be found, then figure out why it is not found (not there/permissions/etc).

Igal Sapir
Lucee Core Developer
Lucee.org

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/23e12a48-001e-4aa9-a67b-f18fa08a45b5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Lauren Paul

unread,
Apr 2, 2015, 12:40:15 PM4/2/15
to lu...@googlegroups.com
This is the reply I get from Heroku support:

Hi there,

We don't permit editing of the pg_hba.conf - however we do permit access to your Postgres DB from all IP addresses. If you're using the credentials outside of Heroku check that you are using SSL for the connection

Regards,
John.

I'm not sure how to "check that we are using SSL for the connection". 

Jon Clausen

unread,
Apr 2, 2015, 1:05:27 PM4/2/15
to lu...@googlegroups.com

You’ll probably need to create your own connection string for the datasource which specifies SSL, like so:

jdbc:postgresql://[heroku IP address]/[database]?user=[username]&password=[password]&ssl=true

When you create the datasource in the Lucee admin, select Other - JDBC Driver, then enter your connection string in the box provided.

Lauren Paul

unread,
Apr 3, 2015, 9:50:41 AM4/3/15
to lu...@googlegroups.com
I've tried it every way I can (Postgresql datasource or Other-JDBC Driver option), and this still does not work for me.  I either get the same error or "Connection attempt failed."  I've confirmed I'm using the right credentials provided by Heroku.  I'm just spinning my wheels at this point.  Any other suggestions appreciated.

Jon Clausen

unread,
Apr 3, 2015, 10:06:52 AM4/3/15
to lu...@googlegroups.com
With the JDBC datasource option, is the SSL cert authority used by Heroku in your keystore?  The connection attempt failure, which is different than the previous error, might be an SSL cert issue  (example: http://stackoverflow.com/questions/5395158/cf8-ssl-connection-to-postgres-fails )

You could try adding &sslfactory=org.postgresql.ssl.NonValidatingFactory to the JDBC URL to see if the cert is at issue.

Lauren Paul

unread,
Apr 3, 2015, 10:18:56 AM4/3/15
to lu...@googlegroups.com
Thanks for the suggestion.  I think I need some help with the syntax.  Here is what I have tried:

jdbc:postgresql://ec2-184-73-254-144.compute-1.amazonaws.com/d200shbvfilcsq ?user=xxxxx &password=xxxx &ssl=true

jdbc:postgresql://ec2-184-73-254-144.compute-1.amazonaws.com/d200shbvfilcsq ?user=xxxxx &password=xxxx &sslmode=require

I tried it with brackets between the hostname, database, user, and password and that doesn't work either. 

Igal @ Lucee.org

unread,
Apr 3, 2015, 10:35:50 AM4/3/15
to lu...@googlegroups.com
you don't have spaces in your actual connection strings before the ? and & characters, right?

your sample has spaces...


Igal Sapir
Lucee Core Developer
Lucee.org

Lauren Paul

unread,
Apr 3, 2015, 10:42:53 AM4/3/15
to lu...@googlegroups.com
With or without spaces I get "Connection attempt failed." 

Jon Clausen

unread,
Apr 3, 2015, 10:58:21 AM4/3/15
to lu...@googlegroups.com

Your connection string (the first example) looks valid. Assuming it’s PG 9.3+ running on Amazon, here are the JDBC connection string docs:

https://jdbc.postgresql.org/documentation/head/connect.html

the &ssl doesn’t need a boolean with it anymore, though.

Does that AWS instance use a non-standard port? You may need to specify the port. I just tried using a connection connection string in Lucee against a PGSQL server running on 9.3 w/ SSL and it connected up right away (though Lucee required me to also enter user/pass information in the datasource config fields.

Try this (using the Lucee user/pass info and omitting it from the connections string):

  1. Enter your username and password in datasource fields
  2. Enter the class as org.postgresql.Driver
  3. Use this connection string:
    jdbc:postgresql://ec2-184-73-254-144.compute-1.amazonaws.com/d200shbvfilcsq?ssl=true

I’m wondering if SSL is using a different port, as when I run psql:

psql -h ec2-184-73-254-144.compute-1.amazonaws.com

it gives me the FATAL: no pg_hba.conf entry for host "[my ip address]", user "test", database "test", SSL off error, which says that it’s not listening on 5432 with SSL (psql would negotiate encryption automatically if that port was using SSL).

If it’s a different port, then you would need to add that to your connection string.

Igal @ Lucee.org

unread,
Apr 3, 2015, 11:11:02 AM4/3/15
to lu...@googlegroups.com
you should definitely not have spaces there.

also, make sure that there is no firewall blocking your connection.


Igal Sapir
Lucee Core Developer
Lucee.org

Lauren Paul

unread,
Apr 3, 2015, 11:13:06 AM4/3/15
to lu...@googlegroups.com
I still get a connection failed message.  According to the documentation we have about connection information.  The port is 5432 which is the default TCP port for Postgresql connections.

Jon Clausen

unread,
Apr 3, 2015, 11:37:11 AM4/3/15
to lu...@googlegroups.com

Can you connect directly from the command line with psql? 

psql --host=ec2-184-73-254-144.compute-1.amazonaws.com --port=5432 --username=[your username] --password "dbname=d200shbvfilcsq" 

If not, then it’s not a JDBC issue, but a configuration or port issue with the AWS server instance. If so, then it may be a keystore issue.

Lauren Paul

unread,
Apr 3, 2015, 11:50:22 AM4/3/15
to lu...@googlegroups.com
What directory would I find the psql?  If I just open a command prompt and type that in, it doesn't work.

Jon Clausen

unread,
Apr 3, 2015, 11:56:11 AM4/3/15
to lu...@googlegroups.com

Then you need the libraries installed (or you need to run them as the postgres user). If you’re on a Mac or Linux try:

which psql to see if it can be found. If not, then install them via homebrew or your Linux package management system (yum, apt, zypper,etc)

If you’re on Windows, then you’ll need to download the installer from PostgreSQL http://www.postgresql.org/download/windows/

Igal @ Lucee.org

unread,
Apr 3, 2015, 12:08:48 PM4/3/15
to lu...@googlegroups.com
might be easier for you to install the GUI client:
http://www.pgadmin.org/
and see if you are able to connect.

but you really should check the connection from your AWS server to the Postgres server, and not from your workstation (though that might be easier to test and will still be telling if you run into problems).


Igal Sapir
Lucee Core Developer
Lucee.org

Lauren Paul

unread,
Apr 3, 2015, 12:24:59 PM4/3/15
to lu...@googlegroups.com
From my web server, when running the psql command, I do get connected.  I'm staring at the prompt stating this:

WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256,
 compression: off)
Type "help" for help.
d200shbvfilcsq=>
...

Lauren Paul

unread,
Apr 3, 2015, 12:54:49 PM4/3/15
to lu...@googlegroups.com
So basically I can connect via command prompt but not via Railo.  What do we think I need to try next?

Jon Clausen

unread,
Apr 3, 2015, 1:05:49 PM4/3/15
to lu...@googlegroups.com
Excellent.  Then it may be a keystore issue with the SSL cert on the AWS host.   Import the public key referenced here:


In to your Java keystore and test the connection again.


[Note: Typo assistance courtesy of iPhone]
--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

Lauren Paul

unread,
Apr 3, 2015, 1:47:34 PM4/3/15
to lu...@googlegroups.com
Should I just import the cert into the Third-Party Root Certification Authorities folder?  Also, does IIS need to be restarted or Railo need to be restarted once I import the cert?

Jon Clausen

unread,
Apr 3, 2015, 5:23:56 PM4/3/15
to lu...@googlegroups.com

I’ve never used Windows’ certificate store before so, honestly, I’m not sure.  The keytool utility in your [JRE Home]/bin directory works well for this. You will need to restart Lucee/Railo.

keytool -import -alias awsRDS -trustcacerts -keystore cacerts -file rds-combined-ca-bundle.pem

Lauren Paul

unread,
Apr 8, 2015, 8:49:56 AM4/8/15
to lu...@googlegroups.com
I successfully ran this script. I restarted Railo and still no joy. 

Lauren Paul

unread,
Apr 13, 2015, 5:39:47 PM4/13/15
to lu...@googlegroups.com
I manually created the datasource in my Application.cfc file.  Here is my syntax:

component {

this.datasources["cla2015"] = {
     class: 'org.postgresql.Driver'
   , connectionString: 'jdbc:postgresql://ec2-184-73-254-144.compute-1.amazonaws.com:5432/d200shbvfilcsq'
   , username: 'kgdxxwchqhfwto'
   , password:
"encrypted:blahblahblahencryptedpasswordhere"
};

}

However, when we try to browse to the page, we still get these errors.

available datasource names are [MuraRailo,music_3_user,slotc,cla_calendar,cla_07_user,cla_scholarships_2,cla_07_user_3,music_3_admin,history,ed3,siciliane,english_apps,english2004

datasource [cla2015] doesn't exist



Anybody got any suggestions why it won't work?  I have attached the application.cfc file where I have added the above script in.
Application.cfc

Lauren Paul

unread,
Apr 27, 2015, 9:15:02 AM4/27/15
to lu...@googlegroups.com
I finally got the datasource to connect by using Railo's "Other - JDBC Driver" as the datasource connection.  The syntax I used was:

jdbc:postgresql://host:5432/databasename?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Thanks for everyone's help with this!

Igal @ Lucee.org

unread,
Apr 27, 2015, 10:50:07 AM4/27/15
to lu...@googlegroups.com
thank you for posting your solution.  it will probably save someone much headache and frustration at some point.


Igal Sapir
Lucee Core Developer
Lucee.org

Reply all
Reply to author
Forward
0 new messages