nixos/mysql: ensureUsers should allow "with grant option"

[aanderse]

Issue description

Currently (18.09/19.03) services.mysql.ensureUsers has no ability to create permissions which allow the "with grant option" to allow full database admin access rights like root:

MariaDB [(none)]> show grants for 'root'@'localhost';
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION

Steps to reproduce

Create any mysql user using ensureUsers and note there is no way to add "with grant option".

Technical details

Please run nix-shell -p nix-info --run "nix-info -m" and paste the
results.

• system: "x86_64-linux"
• host os: Linux 4.14.78, NixOS, 18.09.1127.c341f56928c (Jellyfish)
• multi-user?: yes
• sandbox: yes
• version: nix-env (Nix) 2.1.1
• channels(root): "nixos-18.09.945.9779809e050"
• nixpkgs: /nix/var/nix/profiles/per-user/root/channels/nixos

[florianjacob]

Did not know this existed, out of sheer interest, may I ask about your use case for this?

It seems like the GRANT OPTION can actually be listed like GRANT GRANT OPTION ON … TO …, only with ALL PRIVILEGES it is required to add it to the end using WITH GRANT OPTION. If I understood it correctly, the GRANT OPTION is as granular as other permissions, rightt? So something like ensureUsers.user.withGrantOption = true; as separate option does not make sense as well, I guess.

Maybe a solution would be to move from the comma-separated permission string to an actual permission list, or better an enum to typecheck for spelling mistakes. Then we could allow "*.*" = ["ALL PRIVILEGES", "GRANT OPTION"] and in the implementation we detect that specific combination and convert it to GRANT ALL PRIVILEGES WITH GRANT OPTION, while in all other cases it is GRANT ${concatStringsSep ", " permissions}.

[aanderse]

Did not know this existed, out of sheer interest, may I ask about your use case for this?
Where I work we have a specific database admin role which is supposed to be the gatekeeper of access for other developers. We've only relatively recently started migrating servers to NixOS so I didn't want to shake things up too much by telling the database admins I could take care of granting access for them. I needed to grant the database admins access to create accounts and great access to others and I found out NixOS couldn't do this for me.

The syntax of how this would look is the real challenge. An awesome part of NixOS is that it hides implementation details from the user. With my pending PR to add ensureUsers to postgresql I think we should really strive for this. I'm not completely happy with my current user interface because the implementation details between pgsql and mysql differ and I haven't hidden that from the user (yet?).

Maybe a solution would be to move from the comma-separated permission string to an actual permission list, or better an enum to typecheck for spelling mistakes.

I think you might have a good idea with a list of enums as we could then abstract between pgsql and mysql for a common user interface. I'm not a pgsql guy at all, I just wanted to see parity between the two databases and also have a declarative way to create pgsql databases (in order to cleanup module code and get rid of some root access in preStart scripts). We'll need someone with some pgsql expertise to help on this I assume. You're interested in working on the mysql side in part at least?

[florianjacob]

Your use case makes sense, I'd totaly use these options to provision real users as well if that is required, and not only users for services.

I don't have this use case though, and many other things I still need to upstream that I actually use and therefore are of higher priority for me, sorry. My proposal isn't that hard to implement though, it's just time-consuming to find the nooks & crannies, to test and to properly deprecate / handle the old string way. If anyone else wants to tackle this first, I'll hapilly review the changes.

[aanderse]

This has jumped back on my radar as something that would be useful. I'll give some thought to what you proposed. The unfortunate part is that it does break compatibility, so i'll also give some consideration to other solutions which would remain backwards compatible with what is already in place.

@ryantm as someone who assigned this to yourself does it mean you have any interest at all? If so, any thoughts?

[ryantm]

@aanderse Since my company uses MySQL, I have interest in improving the declarative user and database definitions, but right now I'm busy with other things at work.

I removed myself as an assignee to avoid signaling that I'm actively working on it.

[aanderse]

I no longer believe this to be the right answer. See #84146 for discussion.