DoC Computing Support Group


Differences between revisions 1 and 2
Revision 1 as of 2011-10-25 20:12:46
Size: 1528
Editor: ldk
Comment:
Revision 2 as of 2011-10-27 15:46:20
Size: 4369
Editor: ldk
Comment:
Deletions are marked like this. Additions are marked like this.
Line 10: Line 10:
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: 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):
Line 12: Line 12:
ssh -L 12345:db.doc.ic.ac.uk:5432 shell1.doc.ic.ac.uk {{{ssh -L 12345:db.doc.ic.ac.uk:5432 shell1.doc.ic.ac.uk}}}
Line 17: Line 17:
psql --host localhost --port 12345 -U postgreUserName --dbname=postgresDatabaseName {{{psql --host localhost --port 12345 -U postgreUserName --dbname=postgresDatabaseName}}}
Line 19: Line 19:
''To be completed'' 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]]
Line 22: Line 22:
''To be completed'' 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 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.

You should then specify a data-source of the following form:

{{{jdbc:postgresql://db.doc.ic.ac.uk/databaseYouWantToAccess?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory}}}

Make the obvious substitutions in the above URI and specify the correct user-name and password as parameters for the 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 CheckDoCDBNoSSL {
    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/databaseYouWantToAccess?&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory";

        Connection db = null;
        try {
            db = DriverManager.getConnection(uri, "userToConnectAs", "DoCPostgreSQLPasswordForThatUser");
        } catch ( java.sql.SQLException e )
        {
            System.out.println( "Problem making a connection." + e );
        }
 if ( db != null ) {
  System.out.println("Successfully connected to db.doc with SSL.");
 }
    }
}
}}}

Compile it like so (we're assuming you're using Debian/Ubuntu; adjust JAR file location as required):

{{{javac -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL.java}}}

Run it like so(as before, adjust JAR file location as required):

{{{java -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL}}}

PostgreSQL connections from off-site

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.

Use the college VPN service

If you first connect using the college VPN service, then your PostgreSQL connection will be considered as being from on-site. In this case, you will not need to use SSL encryption.

Use SSH tunnelling

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

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 -U postgreUserName --dbname=postgresDatabaseName

This assumes that the PostgreSQL client 'psql' is installed locally. You can use plink under Windows to run the same SSH port-forwarding (plink documentation here

SSL Connection without Authentication

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

You should then specify a data-source of the following form:

jdbc:postgresql://db.doc.ic.ac.uk/databaseYouWantToAccess?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Make the obvious substitutions in the above URI and specify the correct user-name and password as parameters for the 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 CheckDoCDBNoSSL {
    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/databaseYouWantToAccess?&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory";

        Connection db = null;
        try {
            db = DriverManager.getConnection(uri, "userToConnectAs", "DoCPostgreSQLPasswordForThatUser");
        } catch ( java.sql.SQLException e )
        {
            System.out.println( "Problem making a connection." + e );
        }
        if ( db != null ) {
                System.out.println("Successfully connected to db.doc with SSL.");
        }
    }
}

Compile it like so (we're assuming you're using Debian/Ubuntu; adjust JAR file location as required):

javac -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL.java

Run it like so(as before, adjust JAR file location as required):

java -cp /usr/share/java/postgresql.jar:. CheckDoCDBNoSSL

SSL Connection with Authentication

To be completed

 
 

guides/databases/postgres-ssl (last edited 2015-10-04 18:09:06 by ldk)