8504
Comment:
|
9939
|
Deletions are marked like this. | Additions are marked like this. |
Line 3: | Line 3: |
If you want to connect directly to the PostgreSQL server from outside the college network, you ''must'' use SSL encryption. Here are four possible ways to address this. | If you want to connect directly to the PostgreSQL server from outside the departmental Class B network, you ''must'' use SSL encryption. In other words, if the IP address from which you connect is not of the form {{{146.169.X.Y}}}, SSL encryption is required. Here are some possible ways to address this. |
Line 5: | Line 7: |
{{{#!wiki comment College VPN does not work since /etc/postgresql/8.3/db/pg_hba.conf only allows 146.169.0.0 |
|
Line 8: | Line 11: |
}}} | |
Line 10: | Line 14: |
This is similar to using VPN but at an application-level. Use an SSH client to create a tunnel from your computer outside the network to db.doc.ic.ac.uk through one of the externally-accessible SSH servers: shell1.doc.ic.ac.uk -- shell4.doc.ic.ac.uk. Here is the syntax from a Linux terminal (the same command can be run in a Mac OS X terminal): | Use an SSH client to create a tunnel from your computer outside the college network to db.doc.ic.ac.uk through one of the externally-accessible SSH servers: shell1.doc.ic.ac.uk - shell4.doc.ic.ac.uk. Here is the syntax from a Linux terminal (the same command can be run in a Mac OS X terminal): |
Line 14: | Line 18: |
After you authenticate, the above command will set up a tunnel from port 12345 on your local computer to port 5432 (upon which the PostgreSQL service listens on db.doc.ic.ac.uk). You can then configure the PostgreSQL client on your local computer to | After you authenticate, the above command will set up a tunnel from port 12345 on your local computer to port 5432 (upon which the PostgreSQL service listens) on db.doc.ic.ac.uk. You can then configure the PostgreSQL client on your local computer to |
Line 17: | Line 21: |
{{{psql --host localhost --port 12345 -U postgreUserName --dbname=postgresDatabaseName}}} | {{{psql --host localhost --port 12345 --username pgUser --dbname theDB}}} |
Line 19: | Line 23: |
This assumes that the PostgreSQL client 'psql' is installed locally. You can use [[http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html|plink]] under Windows to run the same SSH port-forwarding (plink documentation [[http://the.earth.li/~sgtatham/putty/0.58/htmldoc/Chapter7.html|here]] | (Replace {{{pguser}}} and {{{theDB}}} with the appropriate DoC PostgreSQL user-name and database respectively). |
Line 21: | Line 25: |
== SSL Connection without Authentication == | The above {{{psql}}} command assumes that the PostgreSQL command-line client application of that name is installed locally. You can use [[http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html|plink]] under Windows to run the same SSH port-forwarding (plink documentation [[http://the.earth.li/~sgtatham/putty/0.58/htmldoc/Chapter7.html|here]]). == SSL Connection without Validation == |
Line 24: | Line 30: |
Suppose that you want to (or must use) SSL encryption but you do not care about authenticating the SSL certificate for db.doc.ic.ac.uk. First of all, you will need the PostgreSQL jdbc driver in your Java classpath. Download the JAR file from http://jdbc.postgresql.org/ or if you are using Debian or Ubuntu, 'apt-get install libpg-java' (which installs /usr/share/java/postgresql.jar). Update your CLASSPATH environment variable to reference the relevant JAR file location or include it in the '-cp' argument of your java/javac invocations. |
Suppose that you want to (or must) use SSL encryption but you do not care about validating the SSL certificate for db.doc.ic.ac.uk. First of all, you will need the PostgreSQL jdbc driver in your Java classpath. Download the JAR file from http://jdbc.postgresql.org/ or if you are using Debian or Ubuntu, '{{{apt-get install libpg-java}}}' (which installs /usr/share/java/postgresql.jar). Update your CLASSPATH environment variable to reference the relevant JAR file location or include it in the '-cp' argument of your java/javac invocations. |
Line 29: | Line 35: |
{{{jdbc:postgresql://db.doc.ic.ac.uk/theDB?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory}}} | {{{ jdbc:postgresql://db.doc.ic.ac.uk/theDB?ssl=true& sslfactory=org.postgresql.ssl.NonValidatingFactory }}} (That is one line: it has been line-wrapped above so that it is fully displayed). |
Line 42: | Line 52: |
public class CheckDoCDBNoSSL { | public class CheckDoCDB1 { |
Line 59: | Line 69: |
db = DriverManager.getConnection(uri, "pgUser", "pgUserPasseword"); | db = DriverManager.getConnection(uri, "pgUser", "pgUserPassword"); |
Line 72: | Line 82: |
Change the obvious strings ({{{pgUser}}} and {{{pgUserPasseword}}}), save it under the file-name {{{'CheckDoCDBNoSSL.java'}}} and compile it like so (we assume that you are using Debian/Ubuntu; adjust JAR file location as required): | Change the obvious strings ({{{theDB}}}, {{{pgUser}}} and {{{pgUserPassword}}}), save it under the file-name {{{'CheckDoCDB1.java'}}} and compile it like so (we assume that you are using Debian/Ubuntu; adjust JAR file location as required): |
Line 74: | Line 84: |
{{{javac -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL.java}}} | {{{javac -cp /usr/share/java/postgresql.jar:. CheckDoCDB1.java}}} |
Line 78: | Line 88: |
{{{java -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL}}} | {{{java -cp /usr/share/java/postgresql.jar:. CheckDoCDB1}}} |
Line 81: | Line 91: |
== SSL Connection with Authentication == For our more-discerning clientèle, we can offer you the SSL certificate for db.doc.ic.ac.uk. This allows you to verify that your off-site database client is truly talking to db.doc.ic.ac.uk. As a first step, please e-mail help@doc.ic.ac.uk requesting the certificate if you want to go down this path. If you know of a straight-forward way to query a PostgreSQL server for its SSL certificate, then (a) you can get the certificate directly and (b) please let us know! |
== SSL Connection with Validation == For our more-discerning clientèle, we can offer you the SSL certificate for db.doc.ic.ac.uk. This allows you to be more sure that your off-site database client is truly talking to db.doc.ic.ac.uk. You can download the certificate in X.509 form [[attachment:db.doc.ic.ac.uk.crt|here]] and in DER form [[attachment:db.doc.ic.ac.uk.crt.der|here]]. |
Line 85: | Line 95: |
We assume that you have obtained the SSL certificate (in .crt form) and have it in the file 'db.doc.ic.ac.uk.crt'. You need to add that certificate to a local key store in order that your Java application accepts that certificate when making a connection. | We assume that you have obtained the SSL certificate (in X.509 form) and have it in the file 'db.doc.ic.ac.uk.crt'. You need to add that certificate to a local key store in order that your Java application accepts that certificate when making a connection. |
Line 87: | Line 97: |
First, convert the certificate to DER format using the [[http://www.openssl.org/|openssl]] tool: | If you only downloaded the X.509 [[attachment:db.doc.ic.ac.uk.crt.der|db.doc.ic.ac.uk.crt certificate]], you will need to convert it to DER format using the [[http://www.openssl.org/|openssl]] tool: |
Line 91: | Line 101: |
(If you are unable to do this, we can give you the DER-format file directly). | (Note: you do not have to do the above: you can [[attachment:db.doc.ic.ac.uk.crt.der|download the certificate in DER-format file directly]]). |
Line 97: | Line 107: |
If you are creating this key store for the first time, you will be asked to specify a password. This password is solely for the keystore and should be distinct from any other password referenced in this document. |
If you are creating this key store for the first time, you will be asked to specify a password. This password is solely for the key store and should be distinct from any other password referenced in this document. |
Line 101: | Line 110: |
Line 113: | Line 121: |
public class CheckDoCDB { | public class CheckDoCDB2 { |
Line 122: | Line 130: |
} catch ( java.lang.ClassNotFoundException e ) { |
} catch ( java.lang.ClassNotFoundException e ) { |
Line 141: | Line 148: |
db = DriverManager.getConnection(uri, "pgUser", "pgUserPasseword"); | db = DriverManager.getConnection(uri, "pgUser", "pgUserPassword"); |
Line 144: | Line 151: |
"authenticated SSL."); | "SSL with validation."); |
Line 154: | Line 161: |
As before, change the strings ({{{pgUser}}} and {{{pgUserPasseword}}}). You should also adjust ''/home/userName/.keystore'' to refer to the local path of the created key store and ''keystorePassword'' to be the password string that you specified for the key store. Save the above code under the file-name {{{'CheckDoCDB.java'}}} and compile it like so: | As before, change the parameters as required ({{{theDB}}}, {{{pgUser}}} and {{{pgUserPassword}}}). You should also adjust ''/home/userName/.keystore'' to refer to the local path of the created key store and ''keystorePassword'' to be the password string that you specified for the key store. Save the above code under the file-name {{{'CheckDoCDB2.java'}}} and compile it like so: |
Line 156: | Line 163: |
{{{javac -cp /usr/share/java/postgresql.jar:. CheckDoCDB.java}}} | {{{javac -cp /usr/share/java/postgresql.jar:. CheckDoCDB2.java}}} |
Line 160: | Line 167: |
{{{java -cp /usr/share/java/postgresql.jar:. CheckDoCDB}}} | {{{java -cp /usr/share/java/postgresql.jar:. CheckDoCDB2}}} |
Line 162: | Line 169: |
=== Further information === Please see [[http://jdbc.postgresql.org/documentation/83/ssl-client.html|the JDBC documentation]] for more details. |
== Trouble-shooting == * Have you specified the correct data source URI? * Have you specified the correct user-name? * Does that user have access to the specified database? * Did you specify the correct password for the user? * Are you referencing the PostgreSQL jdbc driver correctly in your Java classpath? * Did you correctly configure and reference a local Java key store? * Have you checked that all the above parameters work correctly for non-SSL connections? * Have you tried adding the following option (for extra debugging information) when invoking your Java application? {{{-Djavax.net.debug=ssl}}} Remember that you can use the command {{{psql --host db.doc.ic.ac.uk --username pgUser --dbname theDB}}} on a DoC Linux computer to eliminate the SSL aspect and check that the basic connection parameters are correct. == Further information == Please see the [[http://developer.postgresql.org/pgdocs/postgres/ssh-tunnels.html|PostgreSQL]] and [[https://jdbc.postgresql.org/documentation/92/ssl-client.html|JDBC]] documentation for more details. |
PostgreSQL connections from off-site
If you want to connect directly to the PostgreSQL server from outside the departmental Class B network, you must use SSL encryption. In other words, if the IP address from which you connect is not of the form 146.169.X.Y, SSL encryption is required. Here are some possible ways to address this.
Use SSH tunnelling
Use an SSH client to create a tunnel from your computer outside the college network to db.doc.ic.ac.uk through one of the externally-accessible SSH servers: shell1.doc.ic.ac.uk - shell4.doc.ic.ac.uk. Here is the syntax from a Linux terminal (the same command can be run in a Mac OS X terminal):
ssh -L 12345:db.doc.ic.ac.uk:5432 shell1.doc.ic.ac.uk
After you authenticate, the above command will set up a tunnel from port 12345 on your local computer to port 5432 (upon which the PostgreSQL service listens) on db.doc.ic.ac.uk. You can then configure the PostgreSQL client on your local computer to connect to localhost:12345. Here is the syntax from a Linux terminal:
psql --host localhost --port 12345 --username pgUser --dbname theDB
(Replace pguser and theDB with the appropriate DoC PostgreSQL user-name and database respectively).
The above psql command assumes that the PostgreSQL command-line client application of that name is installed locally. You can use plink under Windows to run the same SSH port-forwarding (plink documentation here).
SSL Connection without Validation
We assume that you are using Java; please adapt the following as required for other programming languages.
Suppose that you want to (or must) use SSL encryption but you do not care about validating the SSL certificate for db.doc.ic.ac.uk. First of all, you will need the PostgreSQL jdbc driver in your Java classpath. Download the JAR file from http://jdbc.postgresql.org/ or if you are using Debian or Ubuntu, 'apt-get install libpg-java' (which installs /usr/share/java/postgresql.jar). Update your CLASSPATH environment variable to reference the relevant JAR file location or include it in the '-cp' argument of your java/javac invocations.
You should then specify a data-source of the following form:
jdbc:postgresql://db.doc.ic.ac.uk/theDB?ssl=true& sslfactory=org.postgresql.ssl.NonValidatingFactory
(That is one line: it has been line-wrapped above so that it is fully displayed).
Replace theDB in the above URI with the name of the database to which you wish to connect. Having established the URI, you must now specify the appropriate user-name and password as parameters to make a successful database connection. Here is a simple Java example illustrating these concepts:
import java.util.Properties; import java.io.IOException; import java.lang.ClassNotFoundException; import java.net.Socket; import java.lang.reflect.Constructor; import javax.net.ssl.SSLSocketFactory; import java.sql.*; public class CheckDoCDB1 { public static void main(String[] args) { try { Class pgClass = Class.forName("org.postgresql.Driver"); } catch ( java.lang.ClassNotFoundException e ) { System.out.println( "Could not find org.postgresql.Driver class " + "- please check your classpath." ); System.out.println( e ); } String uri = "jdbc:postgresql://db.doc.ic.ac.uk/theDB?&ssl=true" + "&sslfactory=org.postgresql.ssl.NonValidatingFactory"; Connection db = null; try { db = DriverManager.getConnection(uri, "pgUser", "pgUserPassword"); if ( db != null ) { System.out.println("Successfully connected to db.doc using " + "unauthenticated SSL."); db.close(); } } catch ( java.sql.SQLException e ) { System.out.println( e ); } } }
Change the obvious strings (theDB, pgUser and pgUserPassword), save it under the file-name 'CheckDoCDB1.java' and compile it like so (we assume that you are using Debian/Ubuntu; adjust JAR file location as required):
javac -cp /usr/share/java/postgresql.jar:. CheckDoCDB1.java
Run it like so (as before, adjust JAR file location as required):
java -cp /usr/share/java/postgresql.jar:. CheckDoCDB1
SSL Connection with Validation
For our more-discerning clientèle, we can offer you the SSL certificate for db.doc.ic.ac.uk. This allows you to be more sure that your off-site database client is truly talking to db.doc.ic.ac.uk. You can download the certificate in X.509 form here and in DER form here.
We assume that you have obtained the SSL certificate (in X.509 form) and have it in the file 'db.doc.ic.ac.uk.crt'. You need to add that certificate to a local key store in order that your Java application accepts that certificate when making a connection.
If you only downloaded the X.509 db.doc.ic.ac.uk.crt certificate, you will need to convert it to DER format using the openssl tool:
openssl x509 -in db.doc.ic.ac.uk.crt -out db.doc.ic.ac.uk.crt.der -outform der
(Note: you do not have to do the above: you can download the certificate in DER-format file directly).
Now import the certificate into a local key store:
keytool -keystore ~/.keystore -alias doc_postgresql -import -file db.doc.ic.ac.uk.crt.der
If you are creating this key store for the first time, you will be asked to specify a password. This password is solely for the key store and should be distinct from any other password referenced in this document.
Having done the above, you can now make your database connection code check that you trust the presented SSL certificate before connecting. We revisit the previous code accordingly:
import java.util.Properties; import java.io.IOException; import java.lang.ClassNotFoundException; import java.net.Socket; import java.lang.reflect.Constructor; import javax.net.ssl.SSLSocketFactory; import java.sql.*; public class CheckDoCDB2 { public static void main(String[] args) { System.setProperty("javax.net.ssl.trustStore", "/home/userName/.keystore"); System.setProperty("javax.net.ssl.trustStorePassword", "keystorePassword"); try { Class pgClass = Class.forName("org.postgresql.Driver"); } catch ( java.lang.ClassNotFoundException e ) { System.out.println( "Could not find org.postgresql.Driver class " + "- please check your classpath." ); System.out.println( e ); } String trustStore = System.getProperty("javax.net.ssl.trustStore"); if (trustStore == null) { System.out.println("javax.net.ssl.trustStore is not defined"); } else { System.out.println("javax.net.ssl.trustStore = " + trustStore); } String uri = "jdbc:postgresql://db.doc.ic.ac.uk/theDB?ssl=true"; Connection db = null; try { db = DriverManager.getConnection(uri, "pgUser", "pgUserPassword"); if ( db != null ) { System.out.println("Successfully connected to db.doc using " + "SSL with validation."); db.close(); } } catch ( java.sql.SQLException e ) { System.out.println( e ); } } }
As before, change the parameters as required (theDB, pgUser and pgUserPassword). You should also adjust /home/userName/.keystore to refer to the local path of the created key store and keystorePassword to be the password string that you specified for the key store. Save the above code under the file-name 'CheckDoCDB2.java' and compile it like so:
javac -cp /usr/share/java/postgresql.jar:. CheckDoCDB2.java
Run it like so:
java -cp /usr/share/java/postgresql.jar:. CheckDoCDB2
Trouble-shooting
- Have you specified the correct data source URI?
- Have you specified the correct user-name?
- Does that user have access to the specified database?
- Did you specify the correct password for the user?
- Are you referencing the PostgreSQL jdbc driver correctly in your Java classpath?
- Did you correctly configure and reference a local Java key store?
- Have you checked that all the above parameters work correctly for non-SSL connections?
- Have you tried adding the following option (for extra debugging information) when invoking your Java application?
-Djavax.net.debug=ssl
Remember that you can use the command psql --host db.doc.ic.ac.uk --username pgUser --dbname theDB on a DoC Linux computer to eliminate the SSL aspect and check that the basic connection parameters are correct.
Further information
Please see the PostgreSQL and JDBC documentation for more details.