1613225 - Make payload_bytes_error tables accessible to all users

Status: RESOLVED FIXED

(Data Platform and Tools :: General, enhancement, P1)

Description

[Jeff Klukas [:klukas] (UTC-4)]

We decided during All Hands last week to move forward with removing the ip field from payload_bytes_error tables, which will enable us to remove access restrictions for those tables.

The basic flow for making this happen is:

• Move GeoIP coding to the first step of the Decoder (see https://github.com/mozilla/gcp-ingestion/issues/1096) so that messages heading to error output already have IP removed
• Prep a mozilla-pipeline-schemas PR that removes IP from the errors schema
• Prep a cloudops-infra PR that adapts to the new schema
• Run a batch Dataflow job that runs all the errors tables through the Decoder in order to have GeoIP info parsed out
• Verify that the errors tables no longer contain any IP addresses
• Merge all the things and recreate the table to remove the ip column

We could skip the step of actually removing ip from the table schema and rely on that always being populated as NULL. That would simplify the process.

Comment 1

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla-services/mozilla-pipeline-schemas/pull/497

Comment 2

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla-services/mozilla-pipeline-schemas/pull/498

Comment 3

[Jeff Klukas [:klukas] (UTC-4)]

:whd and I conferred and we agreed that the risk:benefit ratio is too high to justify rebuilding the error table. Instead, we'll run the GeoCity backfill and replace the contents of the table in-place, which requires no downtime.

We merged https://github.com/mozilla-services/mozilla-pipeline-schemas/pull/498 to document that the IP fields should always be null. Once the backfill is complete, :whd will PR the change to ACLs to make the table accessible to all users and we can close this out.

Comment 4

[Jeff Klukas [:klukas] (UTC-4)]

The geo backfill is complete. All entries for IP-type fields are now null as shown in the following query:

SELECT
  _TABLE_SUFFIX AS tbl,
  COUNT(*) AS n,
  COUNT(geo_country) AS country,
  COUNT(x_forwarded_for) AS xff,
  COUNT(remote_addr) AS remote_addr,
  COUNT(x_pipeline_proxy) AS x_pipeline_proxy,
FROM
  `moz-fx-data-shared-prod.payload_bytes_error.*`
  WHERE submission_timestamp > '2010-01-01'
GROUP BY
  1
ORDER BY
  1

The backfill is documented in https://github.com/mozilla/bigquery-backfill/pull/4

Comment 5

[Wesley Dawson [:whd]]

The ACL change of https://github.com/mozilla-services/cloudops-infra/pull/1817 has been merged and deployed. I confirmed that I was able to access errors data from re:dash, so I think we're done here. I still think we should implement user-facing views ala https://github.com/mozilla/bigquery-etl/issues/360#issue-494863395 on top of the raw errors tables but that work is no longer blocked on ACL/authorized views shenanigans.