PostgreSQL datasource problem with Railo

I successfully ran this script. I restarted Railo and still no joy.On Friday, April 3, 2015 at 4:23:56 PM UTC-5, Jon Clausen wrote:

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

On April 3, 2015 at 1:47:38 PM, Lauren Paul (lpau...@gmail.com <javascript:>) wrote:

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?

On Friday, April 3, 2015 at 12:05:49 PM UTC-5, Jon Clausen wrote:

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
http://www.google.com/url?q=http%3A%2F%2Fdocs.aws.amazon.com%2FAmazonRDS%2Flatest%2FUserGuide%2FCHAP_PostgreSQL.html%23PostgreSQL.Concepts.General.SSL&sa=D&sntz=1&usg=AFQjCNHfs9E9qWrxAVNp1G2999TcR1naKg

In to your Java keystore and test the connection again.

[Note: Typo assistance courtesy of iPhone]

On Apr 3, 2015, at 12:54 PM, Lauren Paul lpau...@gmail.com wrote:

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

On Friday, April 3, 2015 at 11:24:59 AM UTC-5, Lauren Paul wrote:

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=>

On Friday, April 3, 2015 at 11:08:48 AM UTC-5, Igal wrote:

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 http://lucee.org/
On 4/3/2015 8:56 AM, Jon Clausen wrote:

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/

On April 3, 2015 at 11:50:28 AM, Lauren Paul (lpau...@gmail.com) wrote:

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

On Friday, April 3, 2015 at 10:37:11 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 11:13:12 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Friday, April 3, 2015 at 9:58:21 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 10:19:00 AM, Lauren Paul (lpau...@gmail.com) wrote:

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
http://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
http://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.

On Friday, April 3, 2015 at 9:06:52 AM UTC-5, Jon Clausen wrote:

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 )

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.

On April 3, 2015 at 9:50:46 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Thursday, April 2, 2015 at 12:05:27 PM UTC-5, Jon Clausen wrote:

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.

On April 2, 2015 at 12:40:20 PM, Lauren Paul (lpau...@gmail.com) wrote:

This is the reply I get from Heroku support:

Hi there,

              ...


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
https://groups.google.com/d/msgid/lucee/13e9cf06-5f6e-4df6-95ac-c01d4bd152b6%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.


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 <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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.On Wednesday, April 8, 2015 at 7:49:56 AM UTC-5, Lauren Paul wrote:

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

On Friday, April 3, 2015 at 4:23:56 PM UTC-5, Jon Clausen wrote:

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

On April 3, 2015 at 1:47:38 PM, Lauren Paul (lpau...@gmail.com) wrote:

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?

On Friday, April 3, 2015 at 12:05:49 PM UTC-5, Jon Clausen wrote:

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
http://www.google.com/url?q=http%3A%2F%2Fdocs.aws.amazon.com%2FAmazonRDS%2Flatest%2FUserGuide%2FCHAP_PostgreSQL.html%23PostgreSQL.Concepts.General.SSL&sa=D&sntz=1&usg=AFQjCNHfs9E9qWrxAVNp1G2999TcR1naKg

In to your Java keystore and test the connection again.

[Note: Typo assistance courtesy of iPhone]

On Apr 3, 2015, at 12:54 PM, Lauren Paul lpau...@gmail.com wrote:

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

On Friday, April 3, 2015 at 11:24:59 AM UTC-5, Lauren Paul wrote:

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=>

On Friday, April 3, 2015 at 11:08:48 AM UTC-5, Igal wrote:

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 http://lucee.org/
On 4/3/2015 8:56 AM, Jon Clausen wrote:

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/

On April 3, 2015 at 11:50:28 AM, Lauren Paul (lpau...@gmail.com) wrote:

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

On Friday, April 3, 2015 at 10:37:11 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 11:13:12 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Friday, April 3, 2015 at 9:58:21 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 10:19:00 AM, Lauren Paul (lpau...@gmail.com) wrote:

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
http://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
http://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.

On Friday, April 3, 2015 at 9:06:52 AM UTC-5, Jon Clausen wrote:

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 )

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.

On April 3, 2015 at 9:50:46 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Thursday, April 2, 2015 at 12:05:27 PM UTC-5, Jon Clausen wrote:

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.

On April 2, 2015 at 12:40:20 PM, Lauren Paul (lpau...@gmail.com) wrote:

This is the reply I get from Heroku support:

Hi there,

              ...


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
https://groups.google.com/d/msgid/lucee/13e9cf06-5f6e-4df6-95ac-c01d4bd152b6%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.


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/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

Application.cfc (3.89 KB)

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 http://lucee.org/On 4/27/2015 6:15 AM, Lauren Paul wrote:

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!

On Monday, April 13, 2015 at 4:39:47 PM UTC-5, Lauren Paul wrote:

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
<http://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.


On Wednesday, April 8, 2015 at 7:49:56 AM UTC-5, Lauren Paul wrote:

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

    On Friday, April 3, 2015 at 4:23:56 PM UTC-5, Jon Clausen wrote:

        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|


        On April 3, 2015 at 1:47:38 PM, Lauren Paul (lpau...@gmail.com) wrote:
        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?

        On Friday, April 3, 2015 at 12:05:49 PM UTC-5, Jon Clausen wrote:

            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
            <http://www.google.com/url?q=http%3A%2F%2Fdocs.aws.amazon.com%2FAmazonRDS%2Flatest%2FUserGuide%2FCHAP_PostgreSQL.html%23PostgreSQL.Concepts.General.SSL&sa=D&sntz=1&usg=AFQjCNHfs9E9qWrxAVNp1G2999TcR1naKg>

            In to your Java keystore and test the connection again.


            [Note: Typo assistance courtesy of iPhone]

            On Apr 3, 2015, at 12:54 PM, Lauren Paul <lpau...@gmail.com> wrote:
            So basically I can connect via command prompt but
            not via Railo.  What do we think I need to try next?

            On Friday, April 3, 2015 at 11:24:59 AM UTC-5, Lauren Paul wrote:

                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=>

                On Friday, April 3, 2015 at 11:08:48 AM UTC-5, Igal wrote:

                    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 <http://lucee.org/>

                    On 4/3/2015 8:56 AM, Jon Clausen wrote:

                        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/
                        <http://www.postgresql.org/download/windows/>




                        On April 3, 2015 at 11:50:28 AM, Lauren Paul (lpau...@gmail.com) wrote:

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

                            On Friday, April 3, 2015 at 10:37:11 AM UTC-5, Jon Clausen wrote:

                                Can you connect directly from
                                the command line with psql? 

                                |psql --host=ec2-184-73-254-144.compute-1.amazonaws.com <http://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.


                                On April 3, 2015 at 11:13:12 AM, Lauren Paul (lpau...@gmail.com) wrote:

                                    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.

                                    On Friday, April 3, 2015 at 9:58:21 AM UTC-5, Jon Clausen wrote:

                                        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
                                        <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
                                            <http://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
                                        <http://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.


                                        On April 3, 2015 at 10:19:00 AM, Lauren Paul (lpau...@gmail.com) wrote:

                                            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
                                            <http://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
                                            <http://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. 

                                            On Friday, April 3, 2015 at 9:06:52 AM UTC-5, Jon Clausen wrote:

                                                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 )
                                                <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.


                                                On April 3, 2015 at 9:50:46 AM, Lauren Paul (lpau...@gmail.com) wrote:

                                                    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.

                                                    On Thursday, April 2, 2015 at 12:05:27 PM UTC-5, Jon Clausen wrote:

                                                        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.


                                                        On April 2, 2015 at 12:40:20 PM, Lauren Paul (lpau...@gmail.com) wrote:

                                                            This
                                                            is
                                                            the
                                                            reply I
                                                            get
                                                            from
                                                            Heroku
                                                            support:

                                                            Hi
                                                            there,

                    ...

            --
            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
            <https://groups.google.com/d/msgid/lucee/13e9cf06-5f6e-4df6-95ac-c01d4bd152b6%40googlegroups.com?utm_medium=email&utm_source=footer>.
            For more options, visit
            https://groups.google.com/d/optout
            <https://groups.google.com/d/optout>.
        --
        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/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com
        <https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com?utm_medium=email&utm_source=footer>.
        For more options, visit
        https://groups.google.com/d/optout
        <https://groups.google.com/d/optout>.


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+unsubscribe@googlegroups.com
mailto:lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com
mailto:lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/fadc99d2-56fa-4d56-b4ef-258d712a661d%40googlegroups.com
https://groups.google.com/d/msgid/lucee/fadc99d2-56fa-4d56-b4ef-258d712a661d%40googlegroups.com?utm_medium=email&utm_source=footer.
For more options, visit https://groups.google.com/d/optout.

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!On Monday, April 13, 2015 at 4:39:47 PM UTC-5, Lauren Paul wrote:

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.

On Wednesday, April 8, 2015 at 7:49:56 AM UTC-5, Lauren Paul wrote:

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

On Friday, April 3, 2015 at 4:23:56 PM UTC-5, Jon Clausen wrote:

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

On April 3, 2015 at 1:47:38 PM, Lauren Paul (lpau...@gmail.com) wrote:

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?

On Friday, April 3, 2015 at 12:05:49 PM UTC-5, Jon Clausen wrote:

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
http://www.google.com/url?q=http%3A%2F%2Fdocs.aws.amazon.com%2FAmazonRDS%2Flatest%2FUserGuide%2FCHAP_PostgreSQL.html%23PostgreSQL.Concepts.General.SSL&sa=D&sntz=1&usg=AFQjCNHfs9E9qWrxAVNp1G2999TcR1naKg

In to your Java keystore and test the connection again.

[Note: Typo assistance courtesy of iPhone]

On Apr 3, 2015, at 12:54 PM, Lauren Paul lpau...@gmail.com wrote:

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

On Friday, April 3, 2015 at 11:24:59 AM UTC-5, Lauren Paul wrote:

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=>

On Friday, April 3, 2015 at 11:08:48 AM UTC-5, Igal wrote:

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 http://lucee.org/
On 4/3/2015 8:56 AM, Jon Clausen wrote:

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/

On April 3, 2015 at 11:50:28 AM, Lauren Paul (lpau...@gmail.com) wrote:

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

On Friday, April 3, 2015 at 10:37:11 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 11:13:12 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Friday, April 3, 2015 at 9:58:21 AM UTC-5, Jon Clausen wrote:

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.

On April 3, 2015 at 10:19:00 AM, Lauren Paul (lpau...@gmail.com) wrote:

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
http://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
http://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.

On Friday, April 3, 2015 at 9:06:52 AM UTC-5, Jon Clausen wrote:

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 )

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.

On April 3, 2015 at 9:50:46 AM, Lauren Paul (lpau...@gmail.com) wrote:

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.

On Thursday, April 2, 2015 at 12:05:27 PM UTC-5, Jon Clausen wrote:

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.

On April 2, 2015 at 12:40:20 PM, Lauren Paul (lpau...@gmail.com) wrote:

This is the reply I get from Heroku support:

Hi there,

              ...


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
https://groups.google.com/d/msgid/lucee/13e9cf06-5f6e-4df6-95ac-c01d4bd152b6%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.


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/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com
https://groups.google.com/d/msgid/lucee/49911e01-297b-4bc1-9086-606f1e19206f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.