Request new database for idp.wikimedia.org
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	jbond
	Nov 20 2020, 11:59 AM

Description

This is to request a production equivilent for the DB created in https://phabricator.wikimedia.org/T256120

> Brief summary (or pointer to a task) indicating how it is intended to be used (application or service)
Can i request a new database to store 2FA tokens registered via Apereo CAS (idp.wikimedia.org)

Previewed queries per second (worse case secenario)

The expected qps will be relatively low essentially every time a user who has opted into U2F on cas will cause ~2 queries

Total space needed and growth provision

Each device in json seems to take up about 500k. rough estimates suggest 1Gb would allow us to support 16000 users

Availability constraints (can it suffer downtimes for maintenance?)

Ideally not, downtime to this DB would prevent users with a u2f token from logging in

Owner person or (preferred) team to contact

@jbond @MoritzMuehlenhoff

DB Name

cas

User or users' names and their grants needed (recommended to separate admin accounts from service accounts, with limited rights)

Please mirror granst given to idp-test[12]001 for cas-test as per https://phabricator.wikimedia.org/T256120

Backup policy

Yes, im not sure of the options but at least daily. Data loss is not the end of the world it would just require every U2f user to re-register there device

From which ips the services will be accessed from (mysql client locations)

idp1001.wikimedia.org
idp2001.wikimedia.org

Please let me know if i have missed anything

Details

Due Date: Dec 30 2020, 11:00 PM

	Subject	Repo	Branch	Lines +/-
	idp - db: update firwall rules to add idp.wikimedia.org sources	operations/puppet	production	+7 -0
	production-m1.sql: Add cas grants	operations/puppet	production	+10 -0

Customize query in gerrit

Related Objects

Mentioned Here: T256120: Request new database for idp-test.wikimedia.org

Event Timeline

jbond created this task.Nov 20 2020, 11:59 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 20 2020, 11:59 AM

jbond triaged this task as Medium priority.Nov 20 2020, 12:00 PM

jbond added projects: SRE, CAS-SSO, DBA, User-jbond.

@jbond What is your preferred delivery date for this?

LSobanski moved this task from Triage to Refine on the DBA board.Nov 20 2020, 12:31 PM

In T268327#6636565, @LSobanski wrote:

@jbond What is your preferred delivery date for this?

This is an improvement to a current service so there are no blockers on my side but by the end of the Q would be mice

LSobanski set Due Date to Dec 30 2020, 11:00 PM.Nov 20 2020, 12:42 PM

• Marostegui moved this task from Refine to Ready on the DBA board.Nov 24 2020, 8:19 AM

• Marostegui claimed this task.Nov 24 2020, 8:42 AM

• Marostegui moved this task from Ready to In progress on the DBA board.

Change 643214 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m1.sql: Add cas grants

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

gerritbot added a project: Patch-For-Review.Nov 24 2020, 8:59 AM

@jbond we already have a cas user that has access to cas_staging database. Do you want to re-use that user/password or use a different one for the new casdatabase?

In T268327#6644232, @Marostegui wrote:

@jbond we already have a cas user that has access to cas_staging database. Do you want to re-use that user/password or use a different one for the new casdatabase?

I think its fine to use the same username/password but tagging @MoritzMuehlenhoff in case he disagrees (moritz this is for the u2f registrations)

In T268327#6644534, @jbond wrote:

In T268327#6644232, @Marostegui wrote:

@jbond we already have a cas user that has access to cas_staging database. Do you want to re-use that user/password or use a different one for the new casdatabase?

I think its fine to use the same username/password but tagging @MoritzMuehlenhoff in case he disagrees (moritz this is for the u2f registrations)

Ack, seems also fine to me

Change 643214 merged by Marostegui:
[operations/puppet@production] production-m1.sql: Add cas grants

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

Database created named: cas
Grants are the same as the ones created for T256120: Request new database for idp-test.wikimedia.org
Which are:

GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, INSERT, SELECT, UPDATE ON `cas`.*

Remember to always connect via m1-master.eqiad.wmnet

root@cumin1001:/home/marostegui# mysql --ssl-verify-server-cert=false -h m1-master.eqiad.wmnet -ucas -p cas
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 62600470
Server version: 10.4.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

cas@m1-master.eqiad.wmnet[cas]>

@jbond please test and close this task if everything looks ok.
@jcrespo I have added this database to the dump user.

Maintenance_bot removed a project: Patch-For-Review.Nov 24 2020, 11:10 AM

Change 643239 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] idp - db: update firwall rules to add idp.wikimedia.org sources

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

Change 643239 merged by Jbond:
[operations/puppet@production] idp - db: update firwall rules to add idp.wikimedia.org sources

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

Maintenance_bot removed a project: Patch-For-Review.Nov 24 2020, 12:10 PM

@Marostegui I had to add a firewall rule but all looks good now, can be closed from my end. thanks