COMP3310 - Assignment 2 (2004)

A Secure Filtering Database Application Proxy in Java

note: some important changes:

• there is now another test server available from all labs - see below
• parts 1 and 2 have been increased in value and part 3 decreased - see below

An application proxy is a program that runs on a machine usually attached to two networks - an internal network and an external network - that allows connectivity for a particular application between clients on one network and a real server on the other. An example is the wwwcache.anu.edu.au Web cache that acts as an HTML proxy between HTML browsers within the ANU's networks and HTML servers outside.

In this case, the application we want to proxy for is the PostGreSQL database server. The proxy application accepts connections from PostGreSQL clients on behalf of the real PostGreSQL server and then connects to the real server to pass the queries etc. across.

The important thing to note is that this is all taking place at the application layer. No packets are passing through the proxy machine at the network or transport layers - as would be the case if the proxying machine were a router.

This assignment is in three parts:

• the first part requires you to write an application proxy for PostGreSQL,
• the second part is to augment it with some filtering to restrict access to the PostGreSQL database based on client IP address and database username
• the third part requires further augmentation by adding in a secure socket layer (SSL) front-end to the proxy (ie. between client and proxy)

For testing and development of this assignment, a PostGreSQL database server has been set up on "comp3310", a machine accessible only from the secondary network available in N112 (on IP address 192.168.10.254). This database server allows connection to a database named "comp3310" (sorry COMP6331 and ENGN4535 students - comp3310 is the only database set up!).

Access to this database can be made from any machine in N112 when it has it's secondary network interface configured correctly as per Lab 4 (see Lab 4). In week 9, a DHCP server will be set up for this secondary network, so these interfaces will be properly configured after a reboot.

Three database user accounts have been set up:

• comp3310 - password "comp3310"
• tutors - password "MarkWell"
• bob - password "ethernet"

A commandline tool, psql can be used to connect to this database as any of these users and perform queries etc.

For example: psql -h comp3310 -U bob comp3310 should request a password (above) after which you can perform simple queries such as select * from marks;. Use backslash "q" to quit from psql. Some online help is available from within psql, as well as command completion.

Full online documentation for PostGreSQL version 7.2.1 is available at http://cs.anu.edu.au/doc/postgresql-doc/html/index.html. For documentation for the current version installed in the lab (version 7.4.2) look at postgresql-doc/html/index.html.

Note: a second PostGreSQL server has been set on on "csitexam.anu.edu.au", accessible from all labs, but not from off-campus. You can use this server for development, but you should also test against the "comp3310" server (only accessible from machines in N112) before submitting.

Part One

The first part of the assignment, worth 40% (note: was 30% - changed 25/05/2004) of the assignment mark, is to implement a proxy server for PostGreSQL. It needs to listen for incoming connections, accept them, then, for each incoming connection, create a connection to the database server, then pass data back and forth (bi-directionally) between the client and server. When one end closes, close the other!

The port to listen on and the hostname and portnumber of the server need to be specified from a configuration file (you can decide format of the file, but it should be plain text and follow a standard convention. Your proxy can assume the default PostGreSQL port of 5432 if it is not otherwise specified.

Your proxy server should be multi-threading (as per the multi-threading web server exercise in Lab 2).

You will need to determine how to handle bi-directional communications between the client and the server.

Testing

Your proxy can be tested in a number of ways. Generally, use the psql command line tool to connect to the database via the proxy either from the same machine as the proxy is running on, or on another machine in N112. Use the ethereal tool (possibly on yet another machine) to make sure the packets are flowing properly.

Part Two

What we are interested in is to allow only some database users to connect to the database from certain IP addresses. As it is, PostGreSQL makes no provision for restricting user access based on host IP address - this is where our second part comes in - we can do this through the proxy. An example might be where a less trusted host needs to access some data from the database, so we create a special user account for these accesses and give this user the least amount of privileges necessary for this host to access. The second part of the assignment is worth a further 50% (note: was 40% - changed 25/05/2004) of the assignment mark.

The format of the messages between the PostGreSQL server (backend) and clients (frontends) such as psql is reasonably well documented at: http://cs.anu.edu.au/Student/comp3310/postgresql-doc/html/protocol.html.

Also, using ethereal or tcpdump can reveal much about what goes on between the frontend and backend.

What we need is for our proxy to examine each message from the frontend to the backend looking for a message specifying a database username. When we see this message, we want to look up the username in a table and check what IP address(es) this username can come from. If it is allowed, then continue on as before, otherwise, send the backend a quit message, and send the frontend an appropriate access denied message type.

Clearly, this requires being able to determine the IP address of the connecting client.

For each such attempt, a file should be opened, read in and then closed. This allows dynamic updating of the rules as to who can connect in from where without having to stop and restart the proxy, or sending it a SIGHUP signal.

The format of the file containing username/IP number pairs is as follows:

username1:a.b.c.d/x # comment
username2:e.f.g.h/y
...

Lines not meeting this format can be ignored. Users can be listed more than once with different IP addresses, although it is not an error to list the same IP address for the same user more than once. The part of the IP address after the forward slash is the number of bits of the IP address to match. If there is no matching entry for a specific user for the clients IP address, then that user is implicitly denied access.

A valid access file is:

tutors:192.168.112.0/24 # matches all machines in N112!
bob:192.168.112.8/29 # only matches n112lt08, n112lt09, .. n112lt15 and no others
bob:192.168.112.16/29 # only matches n112lt16, n112lt17, .. n112lt20 and no others
comp3310:0.0.0.0/0 # matches everything

If an intrusion attempt is made (a user is denied), your proxy should close the connection to the backend and emulate the message that the backend would send for an invalid access attempt then close the connection to the frontend (client). Your proxy should also, optionally, log the intrusion attempt, either into a log file or using the syslog facility.

Part 3 - Secure Socket Layer security

Use the Secure Socket Layer (SSL) Java Class to encrypt incoming connections to the proxy, then pass the queries on, via the filter, to the database server unencrypted. A use for this would be to relieve the burden from the database server CPU of doing the SSL encryption itself - allowing database work to proceed more quickly. This third part of the assignment is worth the remaining 10% (note: was 30% - changed 25/05/2004) of the marks for this assignment.

Submission

This assignment is worth 20% of the final assessment.

This assignment is designed to assess:

• your ability to write and debug a medium-level network application in Java
• your ability to use network analysis techniques to discover intricacies of an existing protocol

This assignment is due by 10:00am on Monday 31st May, 2004 (beginning of week 13). It should be submitted electronically, using the submit facility. You should submit the first part of your assignment as part1.java at least one day before submitting the second part (as part2.java) then at least one more day before submitting part 3 (part3.java).

The correct submit command would be:

submit comp3310 Assign2 part1.java

submit comp3310 Assign2 part2.java

submit comp3310 Assign2 part3.java

(use comp6331 or engn4535 if you are enrolled in one of those courses).

Alternatively, if you need to submit multiple java files (eg. some classes that you have written) then create a Java ARchive (.jar) file and submit that. Help on using the jar command to create .jar files can be found by typing jar --help.