1735174 - Change stage replica to production

Status: NEW

(Cloud Services :: Operations: Releng, task)

Description

[Sarah Clements [:sclements]]

We used to have a read-only replica of production back when Treeherder was using AWS and heroku and when the project was moved over to gcp, oremj was hesitant to have a replica of prod. However, there are cases where its more useful for devs to be able to test against user-created data that's only created on the production deployment. :cvlaas, would it be possible to switch from a stage replica to production (or even keep the existing stage, but have it point to production as its master)?

Comment 1

[chris valaas [:cvalaas]]

Jira'd at: https://mozilla-hub.atlassian.net/browse/SVCSE-134

Comment 2

[Sarah Clements [:sclements]]

Thanks!

Comment 3

[chris valaas [:cvalaas]]

Is this regarding the treeherder cloud sql database treeherder-prod-prod-v1? In that case, there already is a replica treeherder-prod-prod-v1-replica. I should be able to help with allowing access to it from stage if that's what you're after.

Comment 4

[Sarah Clements [:sclements]]

(In reply to chris valaas [:cvalaas] from comment #3)

Is this regarding the treeherder cloud sql database treeherder-prod-prod-v1? In that case, there already is a replica treeherder-prod-prod-v1-replica. I should be able to help with allowing access to it from stage if that's what you're after.

I'm guessing that's the right one. I didn't realize a replica already existed for prod. Can you give myself, Aryx, and anyone on the perf team (cc'd Beatrice, Alexandru, and Andra) who wants access to it for local development purposes? And then we probably don't need the stage replica which I believe is only used for local development (and the stage deployment should continue to point to the staging cloud sql database or whatever oremj set up).

Comment 5

[chris valaas [:cvalaas]]

Sure. I'll have to allow your home (I assume?) IPs and assign you usernames/passwords.
I can also require TLS/SSL if your connection method can support that (every connection will have to use SSL after enabling). If it can't support SSL, then we'll have to stick with plain text, would that be a concern?
Is there any sensitive/secret data in this DB?

Comment 6

[Sarah Clements [:sclements]]

(In reply to chris valaas [:cvalaas] from comment #5)

Sure. I'll have to allow your home (I assume?) IPs and assign you usernames/passwords.
I can also require TLS/SSL if your connection method can support that (every connection will have to use SSL after enabling). If it can't support SSL, then we'll have to stick with plain text, would that be a concern?
Is there any sensitive/secret data in this DB?

There isn't anything sensitive but lets require TLS/SSL connections. People should only need to be using it in the docker container for local development with the django server, which supports that. I don't think you'd need our home IPs (at least I don't recall anyone ever asking for that with gcp or when we used AWS).

Comment 7

[chris valaas [:cvalaas]]

If we turn on SSL then STMO will need to use it, too. There are only 2 IPs currently allowed to connect to the replica and they (according to the labels) belong to STMO. Although I don't know if they're actively used.

Or we could create a new read replica (separate from the existing one), enable SSL on that, and use that for your dev connections.

The stage replica database doesn't need your IPs at the moment because it allows any IP to connect to it, which isn't a terrific security posture. Since this is prod we may want to restrict source IPs to an allowlist.
Or, we can open it up to anyone if you think the risks are acceptable.
If we enable SSL and use client certificates, then allowing from any IP seems acceptable; but maybe security should weigh in?

Comment 8

[Sarah Clements [:sclements]]

I know in the past we enabled SSl and only gave a select group of people access (basically company employees and interns). And the idea here isn't to open it up to anyone - we'd still keep it for a select group and they'd need to file a bug or jira ticket for access. What are the security risks you're concerned about? (I'm fine if infosec weighs in, just want to make sure I completely understand the risks).

Comment 9

[chris valaas [:cvalaas]]

Given that there's nothing sensitive in the DB, then I think just enabling SSL is probably sufficient.
Restricting to source IPs is a second factor that probably isn't necessary here. And managing IP allowlists is annoying.

Stage being open to the whole internet and not encrypted means that anyone along the way could see the login credentials and use them to gain access. Whether or not that's a problem or not depends on how sensitive the data is, how hard it is to recreate if it's deleted, and whether malicious changes to the data is something you need to care about.

Comment 10

[Sarah Clements [:sclements]]

Right, we wouldn't ever want to not use SSL for that reason (and Django for instance, requires that for connections) - that much I'm clear on. I was more wondering why the IP allowlist would be necessary, but it sounds like its not needed for our purposes. Also this would be for a replica even if someone somehow had gotten hold of someone else's credentials they wouldn't be able to perform any delete actions, right?

So it sounds like prod replica with SSL connections is good to go?

Comment 11

[chris valaas [:cvalaas]]

Attachment: server-ca.pem

[i'll send this another way]

Comment 12

[chris valaas [:cvalaas]]

Cloud SQL only allows 10 client certs. Do you want to share one cert between devs or create individual ones? And do you want one username/password for mysql or a shared login there?
Aaaalllssssooo, according to the doc I just read, using SSL with a public IP requires allowlisting IPs: https://cloud.google.com/sql/docs/mysql/authorize-ssl#using_authorized_networks

We can try it without, but it may be a requirement.