PostgreSQL datasource problem with Railo

[Lauren Paul]

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.

[Igal @ Lucee.org]

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]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/1c3a3ab0-d9dc-47d8-a6cb-cc024cbd3c81%40googlegroups.com.

[Lauren Paul]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/378578ff-ff6a-4ad1-b469-e2bbd6637204%40googlegroups.com.

[Lauren Paul]

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]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/8057c79f-3add-4c33-b155-d7a925d6287c%40googlegroups.com.

[Lauren Paul]

With or without spaces I get "Connection attempt failed." 

[Jon Clausen]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/8057c79f-3add-4c33-b155-d7a925d6287c%40googlegroups.com.

[Igal @ Lucee.org]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/etPan.551eaa85.30861fde.7515%40jonclausen-mbp.local.

[Lauren Paul]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/af40b5ff-4907-4f89-8945-0d1b39f09af1%40googlegroups.com.

[Lauren Paul]

What directory would I find the psql?  If I just open a command prompt and type that in, it doesn't work.

[Jon Clausen]

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/

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/68ce4cdb-acbd-45c6-b16c-01905cac1610%40googlegroups.com.

[Igal @ Lucee.org]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/etPan.551eb815.2685cf6c.7515%40jonclausen-mbp.local.

[Lauren Paul]

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]

So basically I can connect via command prompt but not via Railo.  What do we think I need to try next?

[Jon Clausen]

Excellent.  Then it may be a keystore issue with the SSL cert on the AWS host.   Import the public key referenced here:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/13e9cf06-5f6e-4df6-95ac-c01d4bd152b6%40googlegroups.com.

[Lauren Paul]

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]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com.

[Lauren Paul]

I successfully ran this script. I restarted Railo and still no joy. 

[Lauren Paul]

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.

[Lauren Paul]

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]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/fadc99d2-56fa-4d56-b4ef-258d712a661d%40googlegroups.com.