VPN access for SUMO Frankfurt read-replica

[bookshelfdave]

Prior to the AWS migration the SUMO team had access to a read only version of the DB. They used this instance for ad-hoc querying. Now that we are in AWS we need to decide if we should:

A. Provide a limited number of users VPN access to the SUMO Frankfurt read-replica DB. (give the team what they had before). B. Provide an alternative (more secure) method to access this data.

Below is some background information from Guigs describing their specific use cases:

The instance is used for:

• Moderating content that is not accessible from the Django administration dashboards
• Collecting metrics for projects each quarter
• Measuring the impact the current community and staff on the support community

These are an example of the regularly used inquiries, however, a large number of queries are unique to the question we are trying to answer. For example, the most recent request was to gather all of the questions and metadata for those questions to gauge the support volume of the support forum. https://github.com/turtleloveshoes/Kitsune-SQL-queries/wiki

Those that need to be added, which I have common queries stored locally, weekly:

• Who is currently participating in Army of Awesome
• What is the activity for recent contributors?
• What are the number of contributions in the forums and questions forums in the last x days, what are they?
• What questions and corresponding tags
• Are we growing? What is the community's impact?
• Who is spamming in Private Messages?
• How many people are impacted by ""?

Even though there are a number of the community dashboards in kitsune answer many questions and the search functionality on the site can be limited to certain sections of the website. Access to the database also gives the option to dig deeper into support issues, without styling or web page limitations. (ie exporting csv file makes it easy to use the data in other analytical tools at Mozilla)

[bensternthal]

@glogiotatidis I captured the reqs from guigs in this issue.

[rtanglao]

thanks ben for the requirements capture! does it have to be sql forever? long term can we put it somewhere like tableau or something more user friendly than raw sql? i'm probably dreaming :-) until then i'll continue to use CSV and raw SQL

[bensternthal]

We are noodling with alternatives, giving folks unfettered access to a r/o version of the DB aint a great idea.

[turtlelovesshoes]

Can you elaborate a little bit more on why it might not be, please. Thank you.

[glogiotatidis]

For this month Rachel emailed me the commands, I run them and emailed the results back.

I wrote a script to automate the procedure. It's in sumo-encrypted/data-extract along with instructions.

[glogiotatidis]

I's thinking that maybe we can get Kibana access to the SUMO team? Would the data there in combination with the Kibana querying and graphing frontend answer their needs?

[bookshelfdave]

@glogiotatidis I've created an issue to look into Kibana

[bookshelfdave]

@turtlelovesshoes @rtanglao We're going to work with IT to setup VPN access for you, I'll drop the bugzilla bugs into this issue.

TODO:

• [ ] request VPN tunnel between IT supported OpenVPN and Frankfurt VPC
  • allow port 3306 to RDS instance(s) in Frankfurt
• [ ] request a new group for SUMO support staff or use an existing LDAP group
• [ ] create read-only database user(s) for SUMO support staff

Related:

• (old SUMO RDS VPN peering) https://bugzilla.mozilla.org/show_bug.cgi?id=1431493

[bookshelfdave]

VPN peering request: https://bugzilla.mozilla.org/show_bug.cgi?id=1475002

[bookshelfdave]

meeting with Dave Miller on July 18th to discuss networking options.

[bookshelfdave]

@jgmize and I met w/ Dave Miller, we're going to use the existing tunnel, but we need a second subnet in order to spin up the RDS replica. You can see progress here.

[jgmize]

per https://bugzilla.mozilla.org/show_bug.cgi?id=1475002#c12 a second subnet has been assigned. next actions:

• [x] add new subnet to existing VPC
• [x] configure VPC gateway to advertise new subnet via BGP across VPN tunnel
• [x] add new subnet to RDS
• [ ] launch RDS instance as replica of existing sumo RDS DB in VPC using the 2 subnets that will route via VPN tunnel
• [ ] modify AWS ACL to allow port 3306 from VPN tunnel to RDS instances
• [ ] file ACL bug with IT allowing port 3306 to both subnets from OpenVPN for members of sumo LDAP group
• [ ] test & troubleshoot connectivity with RO RDS credentials from OpenVPN
• [ ] securely share RO DB configuration & credentials with designated staff

[bookshelfdave]

Users to add to VPN group:

• George Roter
• Roland Tanglao
• Krishna Char
• Susy Struble
• Krishna Chari
• Henrik Mitsch
• backend/sre: jgmize metadave pmac giorgos jpetto (for testing/connectivity)

[bookshelfdave]

We tried creating a read-replica today in us-west-2 using the new "bridge" VPC, but received the following message:

The DB instance and EC2 security group are in different VPCs. The DB instance is in vpc-ee77dc97
and the EC2 security group is in vpc-ea93e58f (Service: AmazonRDS; Status Code: 400; Error
 Code: InvalidParameterCombination; Request ID: 426cfe37-a503-46f9-8b11-24032df53738)

An issue has been opened with AWS support.

[jgmize]

Per AWS support creating a read replica in a different VPC in the same region as the master is not supported, so we will need to take a different approach.

[jgmize]

IT has agreed to use the original approach of routing the Frankfurt subnets along a new VPN tunnel with the understanding that this is an interim solution: https://bugzilla.mozilla.org/show_bug.cgi?id=1475002#c15

[bookshelfdave]

additional info to setup the VPN tunnel has been sent to netops as part of https://bugzilla.mozilla.org/show_bug.cgi?id=1475002#c15

[bookshelfdave]

new query-only account created and limited to VPN-specific CIDR. I'll forward along the credentials shortly.

[bookshelfdave]

creds forwarded to @turtlelovesshoes

[bookshelfdave]

@turtlelovesshoes are you able to connect to the database?