1184167 - Remove MySQL permissions 'Insert_priv, 'Update_priv' and 'Delete_priv' from 'th_user' on stage/prod

Status: RESOLVED WONTFIX

(Tree Management :: Treeherder: Infrastructure, defect, P2)

Description

[Ed Morley [:emorley]]

In bug 1182994 comment 9 it was noticed that the user "th_user", which is used for our read_host targetted datasource queries ([1]) has more than just select privs:
Select_priv, Insert_priv, Update_priv, Delete_priv

We should probably just remove everything other than "Select_priv" - after first checking on stage it doesn't break anything.

Comment 1

[Ed Morley [:emorley]]

[1] https://github.com/mozilla/treeherder/search?q=read_host

Comment 2

[Ed Morley [:emorley]]

If I make the changes on stage (and then do a |flush privileges;| so they get picked up), will they stick?
Or will puppet/... overwrite them?

Comment 3

[Kendall Libby [:fubar] (he/him)]

puppet does not control mysql privs, so you're gtg.

Comment 4

[Ed Morley [:emorley]]

Great, thank you :-)

Comment 5

[Ed Morley [:emorley]]

Stage...

Execute:
> SELECT `Host`, `Insert_priv`, `Update_priv`, `Delete_priv` FROM `mysql`.`user` WHERE `User` = "th_user"

+ --------- + ---------------- + ---------------- + ---------------- +
| Host      | Insert_priv      | Update_priv      | Delete_priv      |
+ --------- + ---------------- + ---------------- + ---------------- +
| 10.X.X.X_ | Y                | Y                | Y                |
| 10.X.X.X_ | Y                | Y                | Y                |
+ --------- + ---------------- + ---------------- + ---------------- +
2 rows

UPDATE `mysql`.`user`
SET
`Insert_priv` = "N",
`Update_priv` = "N",
`Delete_priv` = "N"
WHERE `User` = "th_user";

Execute:
> SELECT `Host`, `Insert_priv`, `Update_priv`, `Delete_priv` FROM `mysql`.`user` WHERE `User` = "th_user"

+ --------- + ---------------- + ---------------- + ---------------- +
| Host      | Insert_priv      | Update_priv      | Delete_priv      |
+ --------- + ---------------- + ---------------- + ---------------- +
| 10.X.X.X_ | N                | N                | N                |
| 10.X.X.X_ | N                | N                | N                |
+ --------- + ---------------- + ---------------- + ---------------- +
2 rows

But I then get an access denied on |flush privileges;|.

Reading more, we should just be using the GRANT commands etc.

Comment 6

[Ed Morley [:emorley]]

(In reply to Ed Morley [:emorley] from comment #5)
> Reading more, we should just be using the GRANT commands etc.

...which th_admin may have access to use, even if it can't use |flush privileges;|. The permissions error was:
Error Code: 1227. Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

Comment 7

[Ed Morley [:emorley]]

I've filed bug 1207991 for Heroku. As for stage/prod, that will become irrelevant once we move to Heroku and isn't urgent anyway.

Comment 8

[Ed Morley [:emorley]]

Wontfix since moving to Heroku.