keep in mind this is more than one database. all of this:

-- phadmin@phab1001
-- phuser@phab1001
-- phabricatorphd@phab1001
-- phstats@phab1001
-- phmanifest@phab1001

    ON *.* TO 'phadmin'@'10.64.16.8'
    ON `phabricator%`.* TO 'phadmin'@'10.64.16.8';
    ON *.* TO 'phuser'@'10.64.16.8'
    ON `phabricator_cache`.`cache_markupcache` TO 'phuser'@'10.64.16.8';
    ON `phabricator%`.* TO 'phuser'@'10.64.16.8';
    ON `phabricator_cache`.`cache_markupcache` TO 'phuser'@'10.64.16.8';
    ON *.* TO 'phabricatorphd'@'10.64.16.8'
    ON `phabricator_cache`.`cache_markupcache` TO 'phabricatorphd'@'10.64.16.8';
    ON `phabricator%`.* TO 'phabricatorphd'@'10.64.16.8';
    ON `phabricator_cache`.`cache_markupcache` TO 'phabricatorphd'@'10.64.16.8';
    ON *.* TO 'phstats'@'10.64.16.8'
GRANT SELECT ON `phabricator_maniphest`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_user`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_project`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_differential`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_herald`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_policy`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_dashboard`.* TO 'phstats'@'10.64.16.8';
GRANT SELECT ON `phabricator_auth`.* TO 'phstats'@'10.64.16.8';
    ON *.* TO 'phmanifest'@'10.64.16.8'

Hello Data-Persistence,

we have the existing Phabricator servers phab1001.eqiad.wmnet (10.64.16.8, 2620:0:861:102:10:64:16:8) and phab2001.codfw.wmnet (10.192.32.147, 2620:0:860:103:10:192:32:147).

They have access to a whole bunch of Phabricator DBs (Phabricator uses many databases) and there are multiple users as well (phstats, phusers, etc) (see T315713#8170414 and how phab1001 appears in modules/profile/templates/mariadb/grants/production-m3.sql.erb.

Then we have new servers that are going to replace them, phab1004.eqiad.wmnet (10.64.16.101, 2620:0:861:102:10:64:16:101) and phab2002.codfw.wmnet (10.192.32.54, 2620:0:860:103:10:192:32:54).

What we would like from you for now is:

• check the existing privileges. Are there wildcards in there that mean other servers are already covered? Or is it really limited to just the current production servers?

The ideal outcome for us would be:

• first we can make sure that new servers can NOT speak to prod databases (while we are setting things up)
• we/you can prepare a change to allow connections from new hosts and at the same time disallow old hosts
• at a time we agree on we make that flip but if we have to we can revert

The alternative would be that we allow both new and old hosts at the same time (maybe that is the case now?) but then we have to be extra careful about not running services on more than one host. But we can also do that.

Hey @Dzahn

So the new hosts would be able to connect to the hosts as long as they use m3-master.eqiad.wmnet. We allow connections to the db only thru the proxy, but of course they'd need to be allowed on the proxy firewalls.
So to answer your question, if they have the firewall rules already allowed, they'd be able to connect to the database if they've configured m3-master.eqiad.wmnet. If they attempt to connect to the db directly, they'd fail.

Does that answer your question?

@Marostegui Thank you. Phabricator machines use m3-master.eqiad.wmnet or m3-slave.eqiad.wmnet to connect, not to the db directly.

Where are the firewall rules for dbproxies configured? I don't recall doing that step myself, but I do recall sending patches for the actual GRANTS template and that you have another manual step to deploy that.

You should be ok:

root@phab1004:~# telnet m3-master.eqiad.wmnet 3306
Trying 10.64.48.42...
Connected to dbproxy1016.eqiad.wmnet.
Escape character is '^]'.
]
5.5.5-10.4.22-MariaDB-log��l�,Z^MCJ+7�qS/.8nB=LDRPmysql_native_password^CConnection closed by foreign host.

So there's nothing else needed from your side I reckon. If they connect thru the proxy, there're no grants needed to be added at this point. When we didn't have the proxies, there was the need to add the grants.
However, we can try it to be 100% sure if you install the mysql client on phab1004 and we can manually give it a go.

Thank you! So I like the part where I don't have to worry about the grants, that's nice.

But on the other hand I was kind of hoping it would _not_ just work for the new host without any action of any kind because I was worried every migration that we accidentally have 2 services talk to the same DB and corrupt the data

Jjust because puppet fires everything up on a new host and an admin applies the puppet role (maybe forgetting to add some hiera value to disable the service at first.

In T315713#8189139, @Dzahn wrote:

Thank you! So I like the part where I don't have to worry about the grants, that's nice.

But on the other hand I was kind of hoping it would _not_ just work for the new host without any action of any kind because I was worried every migration that we accidentally have 2 services talk to the same DB and corrupt the data

Jjust because puppet fires everything up on a new host and an admin applies the puppet role (maybe forgetting to add some hiera value to disable the service at first.

Is there a way in puppet that any new host starts talking to m3-slave first? That way it would be just RO

In T315713#8190804, @Marostegui wrote:

Is there a way in puppet that any new host starts talking to m3-slave first? That way it would be just RO

Yes, i'll try to add that in puppet and make it the default. That way new servers have to knowingly switch from RO to RW. That's a good idea.

Change 829244 had a related patch set uploaded (by Dzahn; author: Dzahn):

[operations/puppet@production] phabricator: ensure only the one active_server connects to rw mysql

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

Change 829244 merged by Dzahn:

[operations/puppet@production] phabricator: ensure only the one active_server connects to rw mysql

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

Mentioned in SAL (#wikimedia-operations) [2022-09-06T21:39:52Z] <mutante> phabricator - passive hosts in codfw switched to readonly DB access (m3-slave, not m3-master) T315713

Excellent - thank you Daniel

@Marostegui I am reopening this because I think we need to ask for grant changes after all:

I can do this from phab1001:

mysql -h m3-slave.eqiad.wmnet -P 3323 -u phstats -D phabricator_project -p

but the equivalent in codfw (any of the phab hosts including the old one) gets denied with the identical puppetized credentials.

mysql -h m3-slave.codfw.wmnet -P 3323 -u phstats -D phabricator_project -p

ERROR 1045 (28000): Access denied for user 'phstats'@'10.192.32.54' (using password: YES)

(Just an example, there are other phab databases, but this one is always just using m3-slave in eqiad all this time).

The short form is we need what is in modules/profile/templates/mariadb/grants/production-m3.sql.erb (259 lines) but where it now uses 10.64.16.8 (phab1001) we also need 10.64.16.101 (phab1004) and 10.192.32.54 (phab2002) to be allowed.

BUT in codfw we only want to use m3-slave (RO) while in eqiad we want to use m3-slave (RO) at first and later switch to m3-master (when we switch phab from 1001 to 1004).

And I am not sure if we need all the dbproxy lines for codfw for this. Probably not because m3-slave is an alias for a dbserver directly.

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

[operations/puppet@production] production-m3.sql.erb: Add grants

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

Change 832389 merged by Marostegui:

[operations/puppet@production] production-m3.sql.erb: Add grants

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

Oh, I assumed that part (that it's not using a dbproxy in this case) was meant to be.

Thanks for pointing out it wasn't and of course fixing it!

@dduvall Right now I must be missing something because I still get "access denied" for different users I tried but based on what Manuel pasted above.. can you now just try deploying again?

I can confirm it works (from 2002) with the "phstats" user and the credentials from /etc/phab_project_changes.conf

What I can't confirm yet is that it works with the users mentioned in /etc/phabricator/config.yaml including the "phdphabricator" user that appeared in the errors from the last deployment attempt.

No worries Daniel, I will get this sorted this week

Daniel, while working on the grants, we've noticed something strange with db1117 (m3-slave), so we are going to rebuild it (it shouldn't take long), but please coordinate with me before going for the switch.

@Dzahn also, to confirm, phab1004 needs to have exactly the same grants as phab1001, right? I see it does on mysql but not on the tracking file, so I am going to fix that.

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

[operations/puppet@production] production-m3.sql.erb: Add phab1004 missing grants

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

Change 834508 merged by Marostegui:

[operations/puppet@production] production-m3.sql.erb: Add phab1004 missing grants

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

Mentioned in SAL (#wikimedia-operations) [2022-09-23T09:26:52Z] <jynus> stopping db1117:s3 for maintenance T315713

Assuming it's related, if not please ignore this comment:
After ssh'ing onto phab1001, I cannot connect to MariaDB anymore:

$:acko\> ssh phab1001
Linux phab1001 4.19.0-20-amd64 #1 SMP Debian 4.19.235-1 (2022-03-17) x86_64
Debian GNU/Linux 10 (buster)
phab1001 is a Phabricator (Main) Server (phabricator)
Backed up on this host: srv-repos
Bare Metal host on site eqiad and rack B4
aklapper@phab1001:~$ mysql
ERROR 1130 (HY000): Host '10.64.16.8' is not allowed to connect to this MariaDB server
aklapper@phab1001:~$

It is related yeah, we are rebuilding it

@Aklapper this is now fixed:

aklapper@phab1001:~$ mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 323
Server version: 10.4.25-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.

mysql:phstats@m3-slave.eqiad.wmnet [phabricator_maniphest]>

@Dzahn I think this is now sorted. It was a big mess, so if you can do some good double checking I would appreciate it.

@Marostegui Sorry for the delay here. I made some tests and found this:

I just came back from holidays. I will work on this this week. Thanks for the detailed report. All these grants are soooo messy :(

Thank you, Manuel. Take your time to get back from holiday:) Yes, I agree they are very messy. I think people had good intentions to create a separate user for everything but it might be too much. Especially if the different passwords are at the end stored in the same repo and the same config files. I am not sure what's easier for you.. copy all the existing stuff or coordinate to simplify it though. I am open to either.

In T315713#8334341, @Dzahn wrote:

@Marostegui Sorry for the delay here. I made some tests and found this:

from host	to host	user	database	works?
phab1001	m3-slave.eqiad	phstats	phabricator_user	☑️
phab1004	m3-slave.eqiad	phstats	phabricator_user	☑️
phab2001	m3-slave.codfw	phstats	phabricator_user	❌ (denied)
phab2002	m3-slave.codfw	phstats	phabricator_user	☑️
phab1001	m3-slave.eqiad	phmanifest	phabricator_maniphest	☑️
phab1004	m3-slave.eqiad	phmanifest	phabricator_maniphest	☑️
phab2001	m3-slave.codfw	phmanifest	phabricator_maniphest	☑️
phab2002	m3-slave.codfw	phmanifest	phabricator_maniphest	☑️
phab1001	m3-slave.eqiad	phabricatorphd	phabricator_system	☑️
phab1004	m3-slave.eqiad	phabricatorphd	phabricator_system	☑️
phab2001	m3-slave.codfw	phabricatorphd	phabricator_system	❌ (denied)
phab2002	m3-slave.codfw	phabricatorphd	phabricator_system	❌ (denied)
phab1001	m3-slave.eqiad	phuser	phabricator_application	☑️
phab1004	m3-slave.eqiad	phuser	phabricator_application	☑️
phab2001	m3-slave.codfw	phuser	phabricator_application	❌ (denied)
phab2002	m3-slave.codfw	phuser	phabricator_application	❌ (denied)
phab1001	m3-slave.eqiad	phadmin	phabricator_policy	☑️
phab1004	m3-slave.eqiad	phadmin	phabricator_policy	☑️
phab2001	m3-slave.codfw	phadmin	phabricator_policy	❌ (denied)
phab2002	m3-slave.codfw	phadmin	phabricator_policy	❌ (denied)

Can you repeat this first test now?

The second one (the timeouts) might be strange as I believe those hosts/users are already in place and working?

I just finished decom'ing phab2001, so we can remove that from the production grants and all tests and just forget about it.

In T315713#8373801, @Marostegui wrote:

Can you repeat this first test now?

The second one (the timeouts) might be strange as I believe those hosts/users are already in place and working?

@Marostegui Thank you! all of the above works now:)

The table is shorter because I am now ignoring phab2001 which has been decom'ed. If you want to you can also delete any grants that are specific for phab2001.

fwiw, there is also the "cross DC" connection where codfw connects to m3-slave.eqiad and vice versa. In most cases this does not work but in one case it did also work. Unsure if we also want to test all those. Of course I am not going to let the application do cross-DC connects.

@Marostegui The explanation for the timeouts is that the port is different beween m3-master and m3-slave.

If I drop the -P 3323 from my command then everything works now as well when connecting to m3-master.

This also explains why we kept having problems connecting from codfw all this time. We never puppetized the option to change the mysql port. It just knows host name, user, database name, password.

Will have to do that in the application config / puppet / erb templates / Hiera.

But this ticket is resolved. Unless you want to remove phab2001 and the ability to make cross-DC connects.

Thanks again, I am aware Phabricator is a special case with all those databases and users.

I will remove grants from phab2001 yes.