Page MenuHomePhabricator

Request new database for idp-test.wikimedia.org
Open, Needs TriagePublic

Description

> 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)

This is for a development environment so very low

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 (however this is for a development environment so ~10Mb is probably enough)

Availability constraints (can it suffer downtimes for maintenance?)

yes this can suffer downtime

Owner person or (preferred) team to contact

@jbond @MoritzMuehlenhoff

DB Name

cas-test (can use something elses if needed)

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

happy for you to issues username and passwords, need to investigate if we can separate privileges in CAS however the config suggest not

Backup policy

not required

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

idp-test1001.wikimedia.org
idp-test2001.wikimedia.org

This request is to configure a database for the development environment however assuming testing is successful we will also need a similar database for the production environment. Please let me know if i have missed anything

Event Timeline

jbond created this task.Tue, Jun 23, 11:36 AM

Thanks for the detailed ticket.
A few comments.

  1. Let's not use -, if you really want that, we can go for can_test.
  2. Can probably place this into m1, so your application would need to connect via m1-master.eqiad.wmnet (that points to a proxy).
  3. We'd need to know the grants you really need (SELECT, INSERT...)
  4. What is the impact of this database going down once it is in production?

For the initial testing maybe we can use a testing database we have (db1077.eqiad.wmnet) and once you are happy with it, create the final grants, database and such on the final m1 databases.

Marostegui moved this task from Triage to Next on the DBA board.Tue, Jun 23, 11:49 AM
jbond added a comment.Tue, Jun 23, 1:10 PM

Thanks for the detailed ticket.
A few comments.

  1. Let's not use -, if you really want that, we can go for can_test.

thats fine

  1. Can probably place this into m1, so your application would need to connect via m1-master.eqiad.wmnet (that points to a proxy).

I think thats fine

  1. We'd need to know the grants you really need (SELECT, INSERT...)

I'm struggling to find this out as the CAS app uses some spring magic. The code its self needs UPDATE, SELECT, DELETE and INSERT. however im unsure what creates the actual table. It appears that the table is defined in java and im guessing spring tries to create it if it dosn't exist. however i dont think its a good idea to give create grants. I'll dig further but wondered if perhaps you may have come accross an app like this before?

  1. What is the impact of this database going down once it is in production?

This would prevent users with 2fa from logging in to SSO protected services

For the initial testing maybe we can use a testing database we have (db1077.eqiad.wmnet) and once you are happy with it, create the final grants, database and such on the final m1 databases.

Yes i think that's fine

Kormat renamed this task from Request new database or idp-test.wikimedia.org to Request new database for idp-test.wikimedia.org.Tue, Jun 23, 1:12 PM

It should be ok to give them CREATE TABLE and even some need CREATE TEMPORARY TABLE, I think those two should be fine if they are needed. I would even guess that for migrations/upgrade they might even need ALTER table.
I have created the can_test database on db1077, and I will wait for you to let me know the username and the final grants you'd like to have there.
We can go for INSERT, UPDATE, DELETE, SELECT, CREATE if you like to start with something.

Let me know which username you'd like to use and if you are able to fine somewhere which grants exactly you'd need.

As per our IRC chat, I have changed the database to cas_test

Please ping me when db is setup but before closing this ticket to make sure backups are correctly configured, as this seems to be an important database to not lose.

@jbond users created with access from the requested hosts:

+----------------------------------------------------------------------------------------------------------------+
| Grants for cas@208.80.154.87                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cas`@`208.80.154.87` IDENTIFIED BY PASSWORD '*x' |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE TEMPORARY TABLES ON `cas_test`.* TO `cas`@`208.80.154.87`  |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.037 sec)

+----------------------------------------------------------------------------------------------------------------+
| Grants for cas@208.80.153.25                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cas`@`208.80.153.25` IDENTIFIED BY PASSWORD '*x' |
| GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE TEMPORARY TABLES ON `cas_test`.* TO `cas`@`208.80.153.25`  |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.036 sec)

The database is also empty and ready to get tables:

root@db1077.eqiad.wmnet[(none)]> use cas_test;
Database changed
root@db1077.eqiad.wmnet[cas_test]> show tables;
Empty set (0.037 sec)
root@cumin2001:~# host 208.80.154.87
87.154.80.208.in-addr.arpa domain name pointer idp-test1001.wikimedia.org.
root@cumin2001:~# host 208.80.153.25
25.153.80.208.in-addr.arpa domain name pointer idp-test2001.wikimedia.org.

Once this is tested and ready to move to production m1, I will work on the .sql files to keep track of the new grants for the dbproxies IPs.
@jbond remember that in production you'll need to connect via the proxies (m1-master.eqiad.wmnet), for now you can directly connect to db1077.eqiad.wmnet

Change 607476 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] idp_test: enable u2f jpa

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

Change 607476 merged by Jbond:
[operations/puppet@production] idp_test: enable u2f jpa

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

jbond added a comment.Wed, Jun 24, 2:49 PM

Once this is tested and ready to move to production m1, I will work on the .sql files to keep track of the new grants for the dbproxies IPs.
@jbond remember that in production you'll need to connect via the proxies (m1-master.eqiad.wmnet), for now you can directly connect to db1077.eqiad.wmnet

Thanks however im getting the following error when trying to create table U2FDevice_Registration

Error message: The MariaDB server is running with the --read-only option so it cannot execute this statement

Should be fixed now.

jbond added a comment.Thu, Jun 25, 8:40 AM

Should be fixed now.

Thanks although I'm now getting

"Error message: CREATE command denied to user 'cas'@'208.80.154.87' for table 'U2FDevice_Registration'"

Looks like you only gave CREATE TEMPORARY grants but looks like i also need create

@jbond is the scope of this task done or is there anything else left?

Change 608623 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] mariadb::ferm: move firewall rules to there own profile

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608623

Change 608624 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] mariadb::core_test: open mysql port for ipd_test on db1077 server

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608624

Change 608639 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Open port to misc dbs for idp-test servers

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608639

Change 608624 abandoned by Jbond:
mariadb::core_test: open mysql port for ipd_test on db1077 server

Reason:
use https://gerrit.wikimedia.org/r/c/operations/puppet/ /608639

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608624

Change 608639 merged by Jcrespo:
[operations/puppet@production] mariadb: Setup db1077 as a misc::idp_test database server

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608639

The proposed refactoring could broke the core/misc separation. We have deployed a far-from-ideal misc::idp_test (which I still have to refine and add extra monitoring + tendril/zarcillo movement), and korma'ts section classification changes, but this should at least unblock the testing.

Re: Backups I was told they are not needed.

Change 608820 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Add monitoring to temporary test ipd database db1077

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608820

Change 608820 merged by Jcrespo:
[operations/puppet@production] mariadb: Add monitoring to temporary test ipd database db1077

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608820

jcrespo assigned this task to jbond.Wed, Jul 1, 10:49 AM

The host has been added the missing monitoring and open ports, as well as updated on tendril and zarcillo.

If this is all done, this can be resolved from DBAs point of view @jbond. When the database testing finishes, please file a new ticket to decommission/drop the test db and set up a final one on m1.

jbond closed this task as Resolved.Thu, Jul 2, 9:05 AM

Thanks @jcrespo Thanks for helping this is all set up and ready for the ticket to close however this database will be required perminently. idp-test.wikimedia.org, which uses this database, will continue to exist as an environment to test out new features

Change 608623 abandoned by Jbond:
mariadb::ferm: move firewall rules to there own profile

Reason:
more generic refactor here https://gerrit.wikimedia.org/r/c/operations/puppet/ /608895

https://gerrit.wikimedia.org/r/c/operations/puppet/ /608623

Marostegui reopened this task as Open.Mon, Jul 6, 5:22 AM

Thanks @jcrespo Thanks for helping this is all set up and ready for the ticket to close however this database will be required perminently. idp-test.wikimedia.org, which uses this database, will continue to exist as an environment to test out new features

This is different from what it was originally requested (or what I understood).
db1077 is not guaranteed to be up at all times and its data might be lost or wiped. It is a testing host we use for several things (reimages, schema changes, version changes etc).
If this database is not just used for a few days but it is expected to be up, then we need to change its location and we probably need to place it in the same place we place the production final database, but with a different name (maybe the _test one).
Please advise.

jbond added a comment.Mon, Jul 6, 10:29 AM

@Marostegui sorry for the confusion in the initial request. To clarify we would like this database to be available at all times but with very low commits in terms of reliability and availability. i.e. we don't need backups, if the data is lost that's not a problem as its only test data and we can just recreate it. further if the host is unavailable for short periods of time due to re-imagining or HW failure then that is acceptable. Let me knof if you need any other information

thanks

Let's move this to a "more stable" place then. This host is not guaranteed to be up really, we use it for many things, it can fail, it can be down for days, it is just a sandbox for us.
I would suggest we move this database under the name cas_staging into m1, along with the final one. If that's ok with you, I can create the same grants on m1-master.eqiad.wmnet and the empty database (unless you want me also to copy the existing data) and you can point your development environment there?

jbond added a comment.Mon, Jul 6, 11:03 AM

@Marostegui that sounds good to me, no need to copy the current data, just let me know when its in place and ill update my config.

Thanks

@jbond the emtpy cas_staging database has been created on m1.
You need to point your application to:

m1-master.eqiad.wmnet and use the database cas_staging (you'd need to recreate the tables).
The user/password is the same one that you had in db1077.

I have tested it manually:

root@cumin1001:/home/marostegui# mysql --ssl-verify-server-cert=false -hdbproxy1012 cas_staging -ucas -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 248919
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@dbproxy1012[cas_staging]> show tables;
Empty set (0.001 sec)

cas@dbproxy1012[cas_staging]> Ctrl-C -- exit!
Aborted
root@cumin1001:/home/marostegui# mysql --ssl-verify-server-cert=false -hdbproxy1014 cas_staging -ucas -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 249031
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@dbproxy1014[cas_staging]> show tables;
Empty set (0.002 sec)

cas@dbproxy1014[cas_staging]>
cas@dbproxy1014[cas_staging]> show grants;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for cas@10.64.16.18                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cas`@`10.64.16.18` IDENTIFIED BY PASSWORD '*x'           |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON `cas_staging`.* TO `cas`@`10.64.16.18` |
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

If you want me to copy the tables/data from db1077, let me know.

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

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

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

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

Change 610264 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] idp: update database to use m1

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

Change 610264 merged by Jbond:
[operations/puppet@production] idp: update database to use m1

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

Change 610275 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] mariadb::misc: Add ipt6ables rules for idp_test and cleanup

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

Change 610275 merged by Jbond:
[operations/puppet@production] mariadb::misc: Add iptables rules for idp_test and cleanup

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

Change 610284 had a related patch set uploaded (by Jbond; owner: John Bond):
[operations/puppet@production] apereo_cas: disable TLS hostname verification

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

Change 610284 merged by Jbond:
[operations/puppet@production] apereo_cas: disable TLS hostname verification

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

Marostegui moved this task from Next to In progress on the DBA board.Thu, Jul 9, 5:16 AM

@jbond were you able to check if the connection works fine and the application can reach the DB via the proxy?

Marostegui moved this task from In progress to Done on the DBA board.Mon, Jul 13, 7:14 AM