External Campus Database Access

A guide on how to allow for external campus database access

Use Case

A partner required data access from their instance of campus to use in Google's Data Studio.

Google Data Studio has a MySQL Connector. The connector itself requires a named connection end point. This endpoint has to be publicly accessible.

To allow for this the solution below was implemented

Solution

Google Data studio can't connect via a VPN to then gain access to the Relational Database Service.

Amazon web services allows for a Relational Database Service to be publicly accessible. We can further secure by allowing only Data Studio IP addresses to access from outside the VPN.

How to

1. Identify the correct AWS RDS Instance
   1. Navigate to the relevant server in forge, the database service will be named the same as the server name
   2. Ensure you have SSH access to the server, add certificate if not
   3. Navigate to the application env file to obtain the all access login for the database in the service as well as the database name.
2. Log in to the relevant database with details above and add a read only user with a secure password. How to create a read-only MySQL user?
3. Enable public access of the AWS Relational Database service
4. Create a new Zoho Password with the required details
   1. AWS RDS address
   2. Port Number
   3. Database Name
   4. User Name
   5. Password
5. These details can be provided to the Google Data Studio MySQL Connector to establish a read only connection.

Notes

This was valid on and before 2022/08/11 TODO - figure out how to limit outside VPN traffic to DB's via Firewall rules