UMass Server Access - matthewcornell/wikitest GitHub Wiki

Following are procedures for accessing UMass Reich Lab server with dengue_cases database.

Overview

This document lays out the process for connecting to the virtual machine (VM) that houses the database of dengue surveillance data from the Ministry of Public Health in Thailand. The server is hosted by UMass-Amherst OIT and is managed by the Reich Lab. The VM is hosted in the LAN_BIOEPI virtual private network (VPN) at UMass-Amherst.

Obtaining a UMass network account to access the VPN

If you do not have a UMass account, please email Nick Reich and request an account. To access the LAN_BIOEPI VPN, users must either have a SPIRE ID that has been given permission to access the VPN (see next section), or have been given the credentials to a Reich Lab subsidiary account. The subsidiary accounts are owned by Nicholas Reich (SPIRE ID: nick) and have already been added to the VPN_BIOEPI group. Subsidiary accounts will be assigned to a single individual collaborator of the Reich Lab. Passwords to these accounts will not be emailed to the collaborator. Collaborators are asked to store their password in a secure fashion, following appropriate guidelines.

Permission to access the VPN

If you receive an account from Nick, it should already have permission to access the VPN. However, if it does not have access, to have an account be added to the VPN-BIOEPI group, an email request must be sent to [email protected]. This is the group that determines which users can use the VPN setup LAN_BIOEPI. UMass IT will only put in the requested users and only those users will be able to access that VPN Group and through that, the dengue database server.

Connecting to the VPN

Windows/Mac users must download the Junos Pulse VPN client from the UMass OIT website. You will need to enter your UMass account information to download the software. More info on the VPN network is available here. After downloading and installing the client, follow the following instructions to connect to the server:

  1. Open the client
  2. Click the "+"
  3. Type field should be "Policy Secure (UAC) or Connect Secure (VPN)"
  4. Name field is meaningless, so enter something here.
  5. Server URL is sslvpn.umass.edu
  6. Click "connect"
  7. Choose LAN_BIOEPI. (You could choose the checkbox to "save settings")
  8. Input your VPN account username and password (this will either be your SPIRE login, SPIRE NET ID, or the subsidiary account information provided above). Do NOT choose the checkbox to "save settings" in this step.

Linux users can use openconnect which supports pulse secure VPN. Not all versions are reported to work correctly. As of this writing, versions equal to or greater than v7.07-0 work well. After installing openconnect, the following set of commands can be executed in your shell each time you want to connect to the VPN.

sudo openconnect --juniper sslvpn.umass.edu

# When asked, enter the realm as LAN_BIOEPI
# Next, enter the username and password

These settings can be saved too, check the openconnect documentation for details. Alternative connection methods are available here. Additionally, your desktop environment's network manager might support juniper VPN out of the box. In that case, you need to create a new VPN by following the relevant connection wizard.

Getting a database account and password

Users must e-mail Abhinav Tushar from their work account with their full name. Your username will be firstname_lastname and the password will be delivered to you via a secure UMass UDrive file transfer.

Accessing the database

  1. To connect to the database your identity is verified based on the ssh connection. This will work from MS Access, command line postgres clients (psql and similar), R (using RPostgreSQL package or dplyr), as well as using scripts from the spamd repo/packages (see Use-Cases). The connection parameters are:

    • host: sphhs-bioepi01.umass.edu
    • username: fullfirstname_fulllastname
    • database name: "dengue_cases"
    • port is 6392 (or whatever else you chose for a local port)
    • password is left blank

  2. On the unix command line (assuming that you have postgres installed), the following command should prompt you for your password and then connect you to the database. Type "\q" to exit the database.

    psql -U firstname_lastname -d dengue_cases -p 6392 -h sphhs-bioepi01.umass.edu

  3. For R, using RPostgreSQL connection and direct SQL queries:

    install.packages("RPostgreSQL") # yes capitalization counts…
library(RPostgreSQL)
conn <- dbConnect(PostgreSQL(), user=fullfirstname_fulllastname, port=6392, dbname="dengue_cases")
dbGetQuery(conn, "SELECT ...")

  4. For R using dplyr, you can read more about accessing databases via R in this vignette.

    install.packages("dplyr")
library(dplyr)
dengue_db_conn <- src_postgres(dbname="dengue_cases", host="sphhs-bioepi01.umass.edu", port=6392, user=" ", password=" ")
unique_case_data <- tbl(dengue_db_conn, "unique_case_data")

Connecting to the database in a script

If you’d like to connect to the database in a script, this currently would require you to have your password written out in a source code file on your computer. We do not recommend doing this, as if this file is inadvertently compromised, it would make accessing the database much easier for the individual who had the file. For the time being, we suggest the following solution for connecting to the database in a script:

  • Store your password in a text file in your home directory.
  • Set permissions for this file to be read-write for the user and inaccessible for everyone else, e.g. chmod 600 ~/.postgresql/postgresql.key
  • Read this file into your script, store it as a variable, and pass it to the password field.
  • NOTE: if your password as a "" in it, you may need to double-escape it to be read properly.

Connecting to database using SSL

(NOTE: we are not currently supporting this method of connecting, but are leaving these instructions here in case we come back to it in the end.)

  1. Make sure you are logged into the VPN.

  2. Receive SSL certificate from Abhinav via UDrive. Your username will be the folder name. That folder has a bunch of files that must go into: ~/.postgresql/.

  3. The .key file must have limited permissions so after you copy them into that directory (which you might need to make), do:

    chmod 600 ~/.postgresql/postgresql.key

    and for good measure:

    chmod 600 ~/.postgresql/postgresql.crt
chmod 600 ~/.postgresql/root.crt

    When completed, the folder should look like this:

    SPH-NReichMac:.postgresql krzysztof$ ls -l
total 24
-rw-------  1 krzysztof  staff  1992 Jan  7 12:03 postgresql.crt
-rw-------  1 krzysztof  staff  3268 Jan  7 12:03 postgresql.key
-rw-------  1 krzysztof  staff  2171 Jan  7 12:03 root.crt

  4. In R you should be able to connect by any of the methods described above.

Database table list

See Dengue-Cases-Database for table details.