Closed Bug 1098527 Opened 11 years ago Closed 11 years ago

open tableau access to Tiles AWS redshift service

Categories

(Infrastructure & Operations Graveyard :: NetOps, task)

Product:
Infrastructure & Operations Graveyard
Component:
NetOps
Type:
task
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: clarkbw, Assigned: relud)

References

Details

(Whiteboard: [data: onboard])

We'd like to connect the Mozilla Tableau service ( http://dataviz.mozilla.org/ ) directly to the (Content Services - Tiles) AWS Redshift database however it appears there are routing / network connection issues that prevent this from happening. Tableau needs a username / password for read-only access to the AWS Redshift service, something that's possible if the Tableau service could connect to the Redshift db. Can we open up a network path such that the two shall meet?
Hi, Can you state your request using either FQDN and/or IP addresses for both the source and the destination hosts, and please indicate the ports and protocols you need open. Sorry, I just don't know what the AWS Redshift database is, or which ports you need open to access it. Thanks.
(In reply to Dave Curado :dcurado from comment #1) > Hi, > Can you state your request using either FQDN and/or IP addresses for both > the source and the destination hosts, and please indicate the ports and > protocols you need open. mostlygeek, can you answer this?
Flags: needinfo?(bwong)
Assigning to :relud as he's working with :sheeri on getting things connected. He'll comment on progress :)
Assignee: network-operations → dthornton
Flags: needinfo?(bwong)
the hosts behind dataviz.mozilla.org (dataviz.zlb.scl3.mozilla.net) need outbound access to tiles-prod-redshift.prod.mozaws.net (which is in aws, with no vpn connection to mozilla). I also need public ip addresses to whitelist in the aws security group (aka firewall) for redshift so that dataviz can reach redshift. redshift is a special kind of hosted postgres cluster. The public ip address of redshift may change, but the dns tiles-prod-redshift.prod.mozaws.net should always point to it.
Looks like the Tableau machines themselves do not have public IPs assigned to them. They are part of the metrics domain, and are behind a NAT, which resolves to 63.245.214.163 (according to whatsmyip.org). That will work for production, failover and stage.
thanks a ton sheeri!
Sheeri, can you give me a list of all the Tableau machines on SCL3? When security policies are applied in Juniper SRX firewalls, those policies are checked before the NAT process happens. So, first we have to let all those Tableau machines send packets to prod-redshift.prod.mozaws.net in a security policy, then the NAT process will change their src ip to 63.245.214.163. As for prod-redshift.prod.mozaws.net, and its ability to have its IP address change, that's... a problem. The way we use the SRX firewall, it works off of IP addresses, not domain names. So if/when that IP address changes, this security policy will fail. Sheeri -- you've seen that happen before with other services where the IP address changed. It's an easy fix, but still... Finally: the ports. What ports do you need open? Thanks.
port 5432, and the redshift ip shouldn't change often, to my understanding. just when the redshift cluster is replaced (ideally never). so I think filing a ticket to get the ip updated would be acceptable on our end.
firewall is now opened on the redshift side
So, for the 2 scl3 machines behind 63.245.214.163 NAT: tableau1.stage.metrics.scl3.mozilla.com aka 10.22.25.1 tableau1.metrics.scl3.mozilla.com aka 10.22.27.136 Oh! and I forgot the prod failover box... The production failover box is in phx, with a NAT of 63.245.216.231 and the machine is tableau1.metrics.phx1.mozilla.com aka 10.8.100.128
prod failover NAT added to redshift whitelist
Translating this request into something more succinct... Please create a security policy so that 10.22.25.1, 10.22.27.136, and 10.8.100.128 can send packets to 54.201.128.218 on tcp port 5432. =-) OK, let me do this now...
From SCL3 to AWS the policy should be in place. Can you test and let me know if it works? I'll do the PHX1 policy now. From zone: metrics, To zone: untrust Source addresses: tableau1.stage: 10.22.25.1/32 tableau1: 10.22.27.136/32 Destination addresses: tiles-prod-redshift-aws: 54.201.128.218/32 Application: postgres IP protocol: tcp, ALG: 0, Inactivity timeout: 43200 Source port range: [0-0] Destination port range: [5432-5432]
OK, the policy is in place in PHX1 as well... Please confirm when you can. Thanks. From zone: metrics, To zone: untrust Source addresses: tableau1: 10.8.100.128/32 Destination addresses: tiles-prod-redshift-aws: 54.201.128.218/32 Application: postgres IP protocol: tcp, ALG: 0, Inactivity timeout: 43200 Source port range: [0-0] Destination port range: [5432-5432]
Status: NEW → ASSIGNED
Just a quick status update. Haven't tested the connection yet. Currently Tableau requires users to develop in the client application before submitting to the staging and production server environments. We tried to use the SSH tunnel to make this happen but hit too many roadblocks. Next steps: @relud is going try to prioritize a VPN connection. @scabral is upgrading (at end of week) to the latest version of Tableau which has a Mac client and will relieve some of the issues surrounding the Windows VM / Tableau client SSH tunneled connection. Once we can get a client application connected we can begin testing the server connection. Expect that to be around the end of the week or when @relud gets the VPN connection setup.
we're now allowing inbound access from the mozilla vpn, and getting outbound from the vpn via https://bugzilla.mozilla.org/show_bug.cgi?id=1100661 please check 1100661 to make sure the right users get access.
(In reply to Daniel Thornton [:relud] from comment #16) > please check 1100661 to make sure the right users get access. can you cc me on bug 1100661 please? I don't have access right now. Thanks!
cc'd
this should be working, but I need someone to verify
(In reply to Daniel Thornton [:relud] from comment #18) > cc'd Daniel, can you cc me on https://bugzilla.mozilla.org/show_bug.cgi?id=1100661 as well? Thanks.
cc'd
FWIW Tableau has been upgraded, though we're still working on figuring out how to connect to it from our Mac desktops (oddly enough I can connect to our failover, which is on 8.2, but not production, which is on 8.2).
Blocks: 1105402
Josephine was able to access tableau using the mozilla vpn recently, and to my knowledge it is working. Marking this as resolved.
Status: ASSIGNED → RESOLVED
Closed: 11 years ago
Resolution: --- → FIXED
Whiteboard: [data: onboard]
Product: Infrastructure & Operations → Infrastructure & Operations Graveyard
You need to log in before you can comment on or make changes to this bug.