@Arnoldokoth could you add a short description of what this task is about?

We can give it only SELECT grant

@Marostegui Yeah, that would be okay.

@Marostegui Yeah, that's exactly what I need but not on the primary database host. We are setting up a new failover VM for VRTS in codfw (vrts2001). The current VRTS instance (otrs1001) connects to the m2-master host in eqiad. So we need a new user that can connect to m2-slave in codfw and said user should only have SELECT grants (I'm not sure if this is implied by the fact that it connects to the secondary host).

@Arnoldokoth for consistency, I'd rather create the user everywhere (so on the master and the replicas).
Which username you'd like it to have?

@Marostegui Yeah, that's okay. I'd like the username vrts.

Change 867001 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] production-m2.sql.erb: Add new user

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

@Arnoldokoth I have created the user vrts and it is allowed to connect with SELECT grant to otrs via the proxies. I have left the password at this file so you can puppetize it:

root@cumin1001:/home/aokoth# ls -lh
total 4.0K
-rw-r--r-- 1 root root 16 Dec 12 06:29 vrts

Not sure if you might need to do firewall changes to be able to connect though.
Please test it and let me know if it works.

Change 867001 merged by Marostegui:

[operations/puppet@production] production-m2.sql.erb: Add new user

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

Hi @Marostegui We did some testing and the outcome was we found we could connect to the m2-master on both hosts but can't connect to the m2-slave on either server. Despite the fact that the new user has read-only grants, we would prefer that the non-active host connects to the secondary database server as well.

aokoth@otrs1001:~$ mysql -h m2-slave.eqiad.wmnet -u otrs -p otrs -P 3323
ERROR 1045 (28000): Access denied for user 'otrs'@'10.64.16.39' (using password: YES)

aokoth@vrts2001:~$ mysql -h m2-slave.codfw.wmnet -u vrts otrs -p -P 3323
ERROR 1045 (28000): Access denied for user 'vrts'@'10.192.32.133' (using password: YES)

Actually, just scratch everything I've said so far... We might not need a new database user. Could we just have the current user otrs be able to connnect to m2-slave in both data centers?

aokoth@otrs1001:~$ mysql -h m2-slave.eqiad.wmnet -u otrs -p otrs -P 3323
ERROR 1045 (28000): Access denied for user 'otrs'@'10.64.16.39' (using password: YES)

Hi @Marostegui

I just talked to Arnold about this and we tested the connections and discussed.

So this should be the same thing that we did for Phabricator in T315713#8388257.

There you said that if we just m3-slave instead of m3-master it's automatically readonly.

So we changed the hostname we connect to, and then realized we also have to change the port from default to 3323 and then you fixed grants
and we could connect to the slave servers with our EXISTING user but everything is readonly.

So we don't actually need or want a new user, what we want is that the existing user, otrs, can connect to m2-slave.eqiad (m2 instead of m3 because that's where VRTS is) from otrs1001, and to m2-slave.codfw from vrts2001.

So exactly how we did it for Phabricator.

The otrs user already exists on both hosts and is available also on m2-slave.eqiad.wmnet:3322 you should be able to connect to it just with the same password.
The one in codfw is also available to connect thru m2-slave.codfw.wmnet:3322 and it is already existing on both places.

Sorry about the confusion with the port: the right one is 3322 (I am just so used to work with m3 that I said 3323 by default). Sorry about that.

I am going to remove the vrts I created then

oooh! Thanks, @Marostegui

So this was just because we were talking to the wrong port then:

aokoth@otrs1001:~$ mysql -h m2-slave.eqiad.wmnet -u otrs -p otrs -P 3323
ERROR 1045 (28000): Access denied for user 'otrs'@'10.64.16.39' (using password: YES)

@Arnoldokoth Can you confirm if the above works for you with -P 3322 ?

P.S. Yes, we would also like to rename the otrs user to vrts but we did not want to make this even more confusing. So maybe in a separate step later.

Thanks @Marostegui I can confirm that this works on otrs1001. I trust that it will work on the other host but I have to install the mysql-client. I will test on vrts2001 after making the necessary Puppet changes.

In T324142#8471417, @Dzahn wrote:

---

P.S. Yes, we would also like to rename the otrs user to vrts but we did not want to make this even more confusing. So maybe in a separate step later.

Yeah, renaming is essentially creating a new user with the same grants and IPs and once confirmed, deleting the other one. A rename per se isn't supported