Page MenuHomePhabricator

Use user-specific passwords for accessing Analytics MariaDB replica databases
Open, MediumPublic

Description

Opening this ticket to discuss if this is possible. If the answer is "no", we can decline it for now.

I think there would be 3 major advantages to having each user authenticate to mysql individually:

  • We can audit who is accessing the data
  • We can potentially have more granular authorization rules within mysql, so, e.g., if one groups needs to store something sensitive in a schema, we can restrict access to that table.
  • No shared group passwords (just for hygiene-- although the security of this system won't be affected much, having a shared password here I believe encourages people to use shared passwords elsewhere, where it may affect their security directly).

Disadvantages:

  • Security will depend on user-chosen passwords, most likely, and users usually chose weak passwords
  • Overhead of account setup (although maybe mysql can use ldap? I've also setup password synchronization for ldap->mysql using other tools for several large organizations a while back, so something like that might be an option)

Event Timeline

csteipp raised the priority of this task from to Needs Triage.
csteipp updated the task description. (Show Details)

MariaDB has a free and open source PAM authentication module (MySQL's is enterprise-only). It can be used to provide LDAP authentication.

Creating one account per user not only it is possible, but desirable. There are 2 main problems:

  • Administration overhead, in an already short staff
  • Lack of proper group/role support, which would mean for MariaDB 10.0, code is not transparent

However, all of these will not work or improve security (but make it worse) if cluster and mysql accounts are not disabled after being unused for a while.

fgiunchedi triaged this task as Medium priority.Dec 7 2015, 2:51 PM
fgiunchedi subscribed.

+1 for this, we've wanted it for a while.

Not just for EventLogging DB, but all research/analytics MySQL DBs.

@Ottomata, what would need to happen to trial either the ldap solution, or creating user accounts in the db itself?

@jcrespo, do you know if the db servers there support TLS connections? If user connect with their ldap passwords, I'd prefer they don't login over cleartext.

what would need to happen to trial either the ldap solution,

don't know much about it...

creating user accounts in the db itself?

This could be done, the problem is managing it. I've done this in the past with puppet, but I think folks don't like this idea much (I'm not sure I do either).

The idea would be to manage the grants on the server, use the LDAP passwords. That is possible.

In theory, all eventlogging-related hosts have SSL deployed, but for backwards compatibility it is not enforced yet. But yes, it is a blocker (it is just that it may be already resolved, but requires investigation).

Ottomata renamed this task from Use user-specific passwords for accessing EventLogging database to Use user-specific passwords for accessing Analytics MariaDB replica databases.May 10 2023, 12:48 PM

Everybody be careful (I was confused for some time) when reading "Analytics MariaDB replica databases", wikireplicas already have per-user authentication. These are not wikireplicas (or the analytics-focused wikireplicas), but the analytics network private replicas of production (dbstore*) for data engineering and research usage.