Maybe you can delete it from a host and leave it for a week or two to see what breaks.

Change #1151655 had a related patch set uploaded (by FNegri; author: FNegri):

[operations/puppet@production] wikireplicas: delete obsolete maintain* users

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

Maybe you can delete it from a host and leave it for a week or two to see what breaks.

Sounds good, I'll delete it from clouddb1013 (web replica) and clouddb1017 (analytics replica) with

DROP USER maintainviews;
DROP USER maintainindexes;

I think the patch https://gerrit.wikimedia.org/r/1151655 can be merged safely. There are also credentials that must be deleted in puppetserver1001:/srv/git/private/.

Predictably I was wrong, and maintain-views is indeed using that user:

root@clouddb1017:~# maintain-views --database enwiki
Traceback (most recent call last):
  File "/usr/local/sbin/maintain-views", line 822, in <module>
    sys.exit(main())
             ^^^^^^
  File "/usr/local/sbin/maintain-views", line 743, in main
    db_connections[instance] = pymysql.connect(
                               ^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 907, in _request_authentication
    auth_packet = self._read_packet()
                  ^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'maintainviews'@'localhost' (using password: YES)")

If I remove user and pass from the pymysql.connect() parameters, it can connect as root. But if they are present, it does connect through the socket (not via TCP), but then authenticates with the provided user and pass.

Using a less-privileged user might be something that we want to keep, but I'm confused by the fact that you can (optionally) specify a user/password when connecting through a unix_socket, it doesn't seem to be clearly documented.

While we decide if we want to remove these users or not, I manually recreated the users and grants on both clouddb1013 and clouddb1017.

I'm confused by the fact that you can (optionally) specify a user/password when connecting through a unix_socket

This seems to be intended behavior for users defined with username@localhost, even if not documented too clearly in the MariaDB and pymysql docs. If you specify a unix_socket but don't specify a user/password, it will authenticate with password-less auth using your unix username, if you specify both a unix_socket and a user/password it will connect through the unix socket, then authenticate with the user/password credentials.

The remaining question is then: do we want to keep these two users, as an additional protection against unintended/malicious actions? Or is the extra complexity not worth it and we should just let the scripts authenticate as root with password-less auth? Yet another option could be to create unix users maintainviews and maintainindexes and use password-less auth but not as root. Or to share a single user for both scripts.

Change #1151655 abandoned by FNegri:

[operations/puppet@production] wikireplicas: delete obsolete maintain* users

Reason:

The users are still in use, see discussion in task.

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