Hide the value of gb_address column in public replicas if gb_autoblock_parent_id is not null
Closed, ResolvedPublic2 Estimated Story Points
Actions

Description

The gb_address should be hidden from public replicas if the value of the gb_autoblock_parent_id column is not null. This is to ensure that the IP that was autoblocked is not visible, in a similar way that the bt_address column is hidden if bt_auto is set to 1.

This probably involves updating https://gerrit.wikimedia.org/g/operations/puppet/+/de042c8e3894b03f9b28b42a391d4d1e0c30e0d2/modules/profile/templates/wmcs/db/wikireplicas/maintain-views.yaml to have a custom view.

Acceptance critera

Ensure that the gb_address column is hidden from public replicas when the gb_autoblock_parent_id column is not null

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	[WikiReplicas] Hide autoblock targets in the globalblocks table	operations/puppet	production	+23 -4

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
		Restricted Task
Resolved	kostajh	T294511 2021 Security Team wikireplicas audit
Declined	None	T284948 Raw IPs of logged-out users disclosed in wiki-replicas
Resolved	Niharika	T324492 Temporary accounts - MVP
Resolved	Madalina	T326816 [Epic] Update features for temporary accounts
Open	None	T355286 [Epic] Globally blocking a temporary account should prevent further account creations
Resolved	Dreamy_Jazz	T368949 [Epic] Implement global autoblocks from global user blocks
Resolved	Dreamy_Jazz	T374859 Globally autoblock the user's IP address when User::spreadAnyEditBlock is called
Resolved	Dreamy_Jazz	T374853 Update the GlobalBlockManager service to support global autoblocks
Resolved	fnegri	T371486 Hide the value of gb_address column in public replicas if gb_autoblock_parent_id is not null
Resolved	fnegri	T375760 update-views cookbook doesn't handle filters correctly

Event Timeline

Dreamy_Jazz created this task.Jul 31 2024, 12:22 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 31 2024, 12:22 PM

Marostegui edited projects, added Data-Services; removed DBA.Jul 31 2024, 12:42 PM

Marostegui moved this task from Backlog to Wiki replicas on the Data-Services board.

fnegri added a project: Data-Engineering.Aug 1 2024, 8:27 AM

Dreamy_Jazz added a parent task: T368949: [Epic] Implement global autoblocks from global user blocks.Sep 16 2024, 2:37 PM

Dreamy_Jazz added a project: Temporary accounts (Blockers to minor pilot wiki deployment).Sep 16 2024, 2:40 PM

Dreamy_Jazz claimed this task.Sep 16 2024, 2:45 PM

Dreamy_Jazz added a project: Trust and Safety Product Sprint (Sprint Beatboxing (Sept 16-27)).

Dreamy_Jazz set the point value for this task to 2.

Dreamy_Jazz moved this task from Priority Backlog to In Progress on the Trust and Safety Product Sprint (Sprint Beatboxing (Sept 16-27)) board.

Dreamy_Jazz added a parent task: T374853: Update the GlobalBlockManager service to support global autoblocks.Sep 16 2024, 3:04 PM

Change #1073430 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[operations/puppet@production] [WikiReplicas] Hide autoblock targets in the globalblocks table

https://gerrit.wikimedia.org/r/1073430

gerritbot added a project: Patch-For-Review.Sep 17 2024, 11:19 AM

Dreamy_Jazz moved this task from In Progress to Needs review on the Trust and Safety Product Sprint (Sprint Beatboxing (Sept 16-27)) board.Sep 17 2024, 11:31 AM

Dreamy_Jazz removed a parent task: T368949: [Epic] Implement global autoblocks from global user blocks.Sep 17 2024, 12:24 PM

Dreamy_Jazz added a project: SRE.Sep 17 2024, 1:35 PM

fnegri subscribed.Sep 17 2024, 4:55 PM

fnegri added a project: Cloud-VPS.Sep 17 2024, 5:01 PM

fnegri removed a project: Cloud-VPS.

This requires a change to the wiki replicas view definition. I'm happy to apply the patch to the wiki replicas hosts after it's merged, but I would like to have explicit approval from Data-Persistence and Data-Engineering before merging that patch.

The process for this kind of change is a bit blurry. The table at https://wikitech.wikimedia.org/wiki/Portal:Data_Services/Admin/Wiki_Replicas#Who_admins_what suggests the Data-Engineering team is responsible but I'm not sure if that's up-to-date.

@Ladsgroup can you provide a +1 for Data-Persistence ?
@joanna_borun @BTullis can you help me finding someone who can +1 it for Data-Engineering ?

In T371486#10160897, @fnegri wrote:

@Ladsgroup can you provide a +1 for Data-Persistence ?

BTW Amir is out until the 30th according to the calendar.

Dreamy_Jazz moved this task from Backlog to In progress on the Temporary accounts (Blockers to minor pilot wiki deployment) board.Sep 23 2024, 3:57 PM

One issue is gb_address in replica will no longer have an index, which will significantly degrade labs users querying such column. In related block_target table, we introduced alternative views that has indexes on IP address column and does not included autoblocks. However, existing tools needs to switched to new views since we introduced a breaking change.

P.S. Personally I think alternative views are hacks, not a proper permanent solution (which should be proper tables, instead of views, for sanitized copies of databases). However such long-term solution is clearly out of scope of this task.

Ahoelzl subscribed.Sep 24 2024, 5:51 PM

Change #1073430 merged by FNegri:

[operations/puppet@production] [WikiReplicas] Hide autoblock targets in the globalblocks table

https://gerrit.wikimedia.org/r/1073430

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

an-redacteddb1001.eqiad.wmnet (FAIL)
- Ran Puppet agent
- The maintain-views run failed, see OUTPUT of 'maintain-views ...' above for details

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

an-redacteddb1001.eqiad.wmnet (FAIL)
- Ran Puppet agent
- The maintain-views run failed, see OUTPUT of 'maintain-views ...' above for details

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

an-redacteddb1001.eqiad.wmnet (FAIL)
- Ran Puppet agent
- The maintain-views run failed, see OUTPUT of 'maintain-views ...' above for details

The cookbook is failing for a bunch of different reasons that should be investigated separately, I'm trying to find an incantation that works for this task.

In T371486#10179767, @fnegri wrote:

The cookbook is failing for a bunch of different reasons that should be investigated separately, I'm trying to find an incantation that works for this task.

Thanks. Fingers crossed you can find a way to get this through.

taavi subscribed.Sep 26 2024, 2:24 PM

Maintenance_bot removed a project: Patch-For-Review.Sep 26 2024, 2:31 PM

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri completed:

an-redacteddb1001.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1017.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1018.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1019.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1020.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1013.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1014.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1015.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

clouddb1016.eqiad.wmnet (PASS)
- Ran Puppet agent
- Ran 'maintain-views --replace-all --auto-depool --databases centralauth'

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

an-redacteddb1001.eqiad.wmnet (FAIL)
- Ran Puppet agent
- The maintain-views run failed, see OUTPUT of 'maintain-views ...' above for details

Cookbook cookbooks.sre.wikireplicas.update-views run by fnegri: Started updating wiki replica views

Cookbook cookbooks.sre.wikireplicas.update-views started by fnegri executed with errors:

an-redacteddb1001.eqiad.wmnet (FAIL)
- Ran Puppet agent
- The maintain-views run failed, see OUTPUT of 'maintain-views ...' above for details

fnegri closed subtask T375760: update-views cookbook doesn't handle filters correctly as Resolved.Sep 26 2024, 4:34 PM

This task is Resolved, as the view change has been applied to all wiki replicas hosts (an-redacteddb1001 and clouddb10[13-20]*).

The table globalblocks only exists in two databases: centralauth (s7) and labswiki (s6). I was not able to update the view in labswiki because of a bug in the maintain-views script: T375780: maintain-views fails on labswiki

However, I checked and the globalblocks table in the labswiki db is empty, so the view cannot expose any private info. The private info we want to hide is stored in the centralauth database.