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.

In T256120#6248144, @Marostegui wrote:

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

2. 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

3. 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?

4. 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

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

In T256120#6252052, @Marostegui wrote:

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.

In T256120#6252975, @Marostegui wrote:

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

Fixed

@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

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

@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

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

@jbond anything pending here or can this be closed?