687906 - Get Metrics and Analysts read-only access on aggregated (new)TCBS data

Status: VERIFIED FIXED

(Socorro :: General, task)

Description

[Robert Kaiser]

I talked to Daniel on the all-hands about getting some way to have access to the aggregated by-day data on crash volume so we can graph e.g. per-platform, per -process-type or by-days-since-release crash volumes/rates for comparisons etc.

Daniel said that this should be doable if the metrics user has read-only access to that data in Socorro. Can we do that?

Comment 1

[[:jberkus] Josh Berkus]

In order for metrics to run analytics against the production database, we need to grant read-only access to all table to user breakpad_metrics.

Doing this by making breakpad_metrics a member of the "breakpad" group role.

Script written, just waiting for review.

Comment 3

[Paula Clemente [:paula]]

Hi,
Any update here?

Thanks.

Comment 4

[[:jberkus] Josh Berkus]

This is fairly simple to do.   It's a new user with read-only permissions from me, and a IT request for a route.

However, AFAIK it was never authorized by Laura.  So assigning it to her, and to release 2.4.2.

Comment 5

[Robert Helmer [:rhelmer]]

(In reply to [:jberkus] Josh Berkus from comment #4)
> This is fairly simple to do.   It's a new user with read-only permissions
> from me, and a IT request for a route.
> 
> However, AFAIK it was never authorized by Laura.  So assigning it to her,
> and to release 2.4.2.

Daniel and I both recall Laura saying this was fine, so a=me if that helps this along :)

Comment 6

[[:jberkus] Josh Berkus]

OK, here's the steps which are needed for this:

1) create user "metrics" which RO access to all tables

2) create pass-through for "metrics" on pgbouncer.

3) test this on stage

4) set query runtime limit of 30min for "metrics" on prod (since long-running queries interfere with replication).

5) set up 1-4 on prod.

6) request route from IT for metrics.

For the last, Daniel, we need to know what machine(s) metrics would be coming from.  Full DNS names.

Comment 7

[Daniel Einspanjer [:dre] [:deinspanjer]]

Safest to cover all our bases:
cm-metricsapp01.mozilla.org
cm-metricsetl01.mozilla.org
cm-metricsetl02.mozilla.org

Of course, those will change in Feb when we move DC, but the whole world will break then so ::shrug::

Comment 8

[[:jberkus] Josh Berkus]

Punting this to 2.4.3, since I haven't been able to test the permissions and will be PTO for a while.  Sorry!

Comment 9

[[:jberkus] Josh Berkus]

Routes requested for analysts and metrics for StageDB and Master02.

Laura: suggested permissions on tables:

User should have select access on all tables, with the following exceptions:
1. legacy (oldTCBS) tables to avoid confusion
2. email-campaign tables
3. processor-control tables
4. other socorro-admin tables
5. "email" and "url" columns in reports and reports_user_info

Please let me know if the above scheme meets with your approval.

Comment 10

[[:jberkus] Josh Berkus]

Oh, also, the new user ("analyst") will not automatically have access to new matviews unless we remember to grant it.  I see this as annoying by an inevitable consequence of wanting some data security.

Comment 11

[[:jberkus] Josh Berkus]

Initial scripts written based on the above plan.  Waiting for Staging to be ready to test 2.4.3 (Thursday) before going further with this.

Comment 12

[[:jberkus] Josh Berkus]

Database changed deployed to crash-stats-dev.  Rest of setup to be tested when changes are deployed to staging.

Comment 13

[Daniel Einspanjer [:dre] [:deinspanjer]]

Tried the following command from app01, etl01, and etl02, all of them timed out:
nc -zv socorro1.zlb.db.sjc1.mozilla.com 6432

Did someone open up a netops bug for the flows?

Comment 14

[[:jberkus] Josh Berkus]

yes, I'll reopen that bug and CC you.

Comment 15

[[:jberkus] Josh Berkus]

Please note the following restrictions on metrics/analytics access:

* The user only has read-only access.  This includes not being able to create temporary tables, unfortunately.

* The user has a query time limit of 15 minutes; queries which run longer than that will be cancelled.

* The analytics user is limited to 10 concurrent connections, total.

* You are prohibited from accessing fields which contain personally identifying information, including:
   reports.email
   reports.url
   reports_user_info.email
   reports_user_info.url
   email campaign tables
This means that "select * from reports" will fail with "permission denied".  You'll need to select specific fields.

* You have also not been grated access to the "oldTCBS" tables.  This is to prevent confusion, and because those tables are slated to go away in May.  If I could hide them from you entirely easily, I would.

Comment 16

[Daniel Einspanjer [:dre] [:deinspanjer]]

Confirmed that access works. Thanks.

Comment 17

[Matt Brandt [:mbrandt]]

Awesome - bumping to verified per comment 16