VPNs/Accessing the Dev DB

  4 minute read  

VPNs exist for our Development, Experimental, Production and Shared Services environments. The VPN provides a way for developers and operations to access the environments in a secure manner, via an encrypted tunnel over the internet.

Access to the different environments is restricted to a limited number of people. If you believe you need to access one of the environments you do not have access to please reach out to the DevOps team and tag @ihr-devops who may be able to assist.

In the examples below we are using dev for the environment, you can replace this with any other environment you have access to, dev, exp, prod or ss.

Ubuntu users

If you’re using Ubuntu, you can install a plugin for Ubuntu’s native Network Manager to use your UI to connect/disconnect to/from VPNs.

  1. Install the OpenVPN package and network-manager plugins apt-get install openvpn network-manager-openvpn network-manager-openvpn-gnome

  2. In Slack, grab the VPN config file by opening a DM with intellibot and saying vpn dev config

    Configure VPN

  3. Save the configuration outputted by the vpn dev config command

    1. Save it as ~/Downloads/intellihr-dev.ovpn

  4. Set your password by sending another message saying vpn dev password <my super strong password>

    Set VPN password

  5. To test the VPN connection you can now run sudo openvpn --config ~/Downloads/intellihr-dev.ovpn. You will be prompted for a username/password

    • Username is your Slack username
    • Password is what you set via the bot command

We can now configure Network Manager and GNOME to allow easier enabling of the VPN

  1. Restart network manager sudo service network-manager restart or sudo service NetworkManager restart in newer Ubuntu version

  2. Go to your Network Settings and add a new VPN

    Create new VPN

  3. Choose Import From File... option and select the ~/Downloads/intellihr-dev.ovpn file

    Create new VPN from file

  4. You’ll now be able to connect to VPN by turning it on from your Network Setting interface

    Create new VPN from file

macOS users

  1. Download and install TunnelBlick

  2. In Slack, grab the VPN config by opening a private message with intellibot and saying vpn dev config

    Configure VPN

  3. Set your password by sending another message saying vpn dev password <my super strong password>

    Set VPN password

  4. Save the config outputted by the vpn dev config command

    1. Save it as ~/Downloads/intellihr-dev.ovpn
    2. Double click the saved file to finish the setup in TunnelBlick

Windows users

  1. In Slack, grab the VPN config by opening a private message with intellibot and saying vpn dev config

    Configure VPN

  2. Save the config outputted by the vpn dev config command into a file intellihr-dev.ovpn

  3. Set your password by sending another message saying vpn dev password <my super strong password>

    Set VPN password

  4. Download and install OpenVPN Connect Client for Windows

  5. Create a new profile by clicking on the plus icon

  6. Click on the ‘Upload Profile’ tab and then drag and drop the intellihr-dev.ovpn file created earlier

  7. The next dialog is a form where you can add more details

    1. Change the Profile Name to anything you want for example IntelliHR - DEV
    2. Type in your username as outputted by Intellibot when setting your password
    3. Click on the Save Password checkbox and type in the password you set earlier

  8. Click on Connect and the next dialog is the Missing External Certificate dialog

  9. Click on Continue and then you’ll be connected to the VPN

Accessing the Dev DB

  1. Configure and connect to the VPN by following the above steps
  2. In the DB Client of your choice, create a new DB connection
  3. Using the credentials from 1Password (“Lapis Dev RDS”), connect to the Dev DB
  4. You should now have access to all tenants as different schemas, note that to check data across all tenants, a query can be run against all schemas by appending the table name with all_ for example, to query all Business Entities, you would run the following SQL:
SELECT * FROM all_business_entities

Accessing the Prod DB

  1. Configure and connect to the VPN by following the above steps

  2. In Slack, /msg intellibot prod-db

    prod-db Command

  3. Use the VPN to connect to the details provided by intellibot

Notes

  1. Special access needs to be granted before the above intellibot command will work for you. This access is only granted on an as needed basis with approval from the CTO
  2. Destructive SQL statements are blocked, including DROP, TRUNCATE and DELETE, unless you have been granted to use them
  3. All SQL queries are logged to #alert-ihr-sql-logs in Slack
  4. It is best to use the psql client as other DB clients tend to cache and prefetch a lot of data which makes it extremely slow to connect through the proxy