diff options
Diffstat (limited to 'apps/mariadb/roles.nix')
| -rw-r--r-- | apps/mariadb/roles.nix | 250 |
1 files changed, 250 insertions, 0 deletions
diff --git a/apps/mariadb/roles.nix b/apps/mariadb/roles.nix new file mode 100644 index 0000000..2971242 --- /dev/null +++ b/apps/mariadb/roles.nix @@ -0,0 +1,250 @@ +{ config, lib, pkgs, ... }: +let + inherit (builtins) + elemAt filter isAttrs isList length trace ; + inherit (lib) + attrNames concatMapStrings concatMapStringsSep concatStrings + concatStringsSep filterAttrs flatten mapAttrsToList mkIf mkOption + optionalString replaceStrings splitString types ; + inherit (types) + attrsOf either listOf str submodule ; + + explicit = filterAttrs (n: v: n != "_module" && v != null); + + inherit (config.nixsap.apps.mariadb) roles; + basicRoles = filterAttrs (_: v: isAttrs v) roles; + topRoles = filterAttrs (_: v: isList v) roles; + allRoles = attrNames roles; + sqlList = concatMapStringsSep ", " (i: "'${i}'"); + + concatMapAttrs = f: attrs: concatStrings (mapAttrsToList f attrs); + + schemaName = object: elemAt (splitString "." object) 0; + isSchema = object: + let p = splitString "." object; + n = length p; + in (n == 1) + || (n == 2 && (elemAt p 1) == "%") + || ((elemAt p 1) == "%" && (elemAt p 2) == "%"); + + tableName = object: elemAt (splitString "." object) 1; + isTable = object: + let p = splitString "." object; + n = length p; + in (n == 2 && (elemAt p 1) != "%") + || (n > 2 && (elemAt p 2) == "%"); + + columnName = object: elemAt (splitString "." object) 2; + isColumn = object: + let p = splitString "." object; + n = length p; + in (n > 2 && (elemAt p 2) != "%"); + + grant = role: privileges: + { + schemas = concatMapAttrs (priv: objects: + concatMapStrings (o: + let + db = schemaName o; + p = "${replaceStrings [" "] ["_"] priv}_priv"; + in '' + SELECT 'GRANT ${priv} ON `${db}`.* TO \'${role}\';' + FROM information_schema.schemata -- Not really used, but for syntax and locks + WHERE NOT EXISTS ( + SELECT 1 FROM db + WHERE db.host = ${"''"} -- role, not user + AND db.user = '${role}' + AND '${db}' LIKE db.db + AND db.${p} = 'Y' + ) LIMIT 1; + '') (filter isSchema (flatten [objects])) + ) (explicit privileges); + + tables = concatMapAttrs (priv: objects: + concatMapStrings (o: '' + SELECT CONCAT('GRANT ${priv} ON `', t.table_schema, '`.`', t.table_name, '` TO \'${role}\';') + FROM information_schema.tables t + WHERE t.table_schema LIKE '${schemaName o}' + AND t.table_name LIKE '${tableName o}' + AND NOT EXISTS ( + SELECT 1 FROM mysql.tables_priv + WHERE tables_priv.host = ${"''"} -- role, not user + AND tables_priv.user = '${role}' + AND tables_priv.db = t.table_schema + AND tables_priv.table_name = t.table_name + AND FIND_IN_SET('${priv}', tables_priv.table_priv) > 0 + ); + '') (filter isTable (flatten [objects])) + ) (explicit privileges); + + columns = concatMapAttrs (priv: objects: + let colObjs = filter isColumn (flatten [objects]); + in optionalString ([] != colObjs) ('' + SELECT CONCAT ('GRANT ${priv}(', + GROUP_CONCAT(DISTINCT c.column_name SEPARATOR ','), + ') ON `', c.table_schema, '`.`', c.table_name, '` TO \'${role}\';') + FROM information_schema.columns c WHERE ( + '' + concatMapStringsSep " OR " (o: + '' + ( c.table_schema LIKE '${schemaName o}' AND + c.table_name LIKE '${tableName o}' AND + c.column_name LIKE '${columnName o}') + '') colObjs + + + '' + ) AND NOT EXISTS ( + SELECT 1 FROM columns_priv + WHERE columns_priv.host = ${"''"} -- role, not user + AND columns_priv.user = '${role}' + AND columns_priv.db = c.table_schema + AND columns_priv.table_name = c.table_name + AND columns_priv.column_name = c.column_name + AND FIND_IN_SET('${priv}', columns_priv.column_priv) > 0 + ) GROUP BY CONCAT(c.table_schema, c.table_name); + '') + ) (explicit privileges); + }; + + refreshRolesSQL = + let + sql = concatMapAttrs (role: privileges: '' + ${(grant role privileges).schemas} + ${(grant role privileges).tables} + ${(grant role privileges).columns} + '') basicRoles; + in pkgs.writeText "refresh-roles.sql" sql; + + refreshRoles = pkgs.writeBashScriptBin "refreshRoles" '' + set -euo pipefail + + doze() { + difference=$(($(date -d "08:00" +%s) - $(date +%s))) + if [ $difference -lt 0 ]; then + sleep $((86400 + difference)) + else + sleep $difference + fi + } + + while true; do + while ! mysql -e ';'; do + sleep 5s + done + tmp=$(mktemp) + trap 'rm -f "$tmp"' EXIT + mysql -N mysql < ${refreshRolesSQL} >> "$tmp" + mysql -v mysql < "$tmp" + doze + done + ''; + + addRoles = '' + ${concatMapStrings (r: "CREATE ROLE IF NOT EXISTS '${r}';\n") (attrNames roles)} + + ${concatStrings + (mapAttrsToList (role: subroles: '' + ${concatMapStringsSep "\n" (r: "GRANT '${r}' TO '${role}';") subroles} + '') topRoles) + } + ''; + + revokeRoles = '' + ${concatMapAttrs (role: subroles: '' + SELECT CONCAT('REVOKE \''', role, '\' FROM \''', user, '\';') FROM roles_mapping + WHERE user = '${role}' + AND role NOT IN (${sqlList subroles}); + '') topRoles + } + + SELECT CONCAT('DROP ROLE \''', user, '\';') FROM user WHERE is_role='Y' + ${optionalString (allRoles != []) "AND user NOT IN (${sqlList allRoles})"} + ; + ''; + + roleType = + let + objects = mkOption { + type = either str (listOf str); + default = []; + example = [ + "%bleep.%.created\_at" + "%bob\_live\_sg.brand\_type" + "%bob\_live\_sg.catalog%" + "%bob\_live\_sg.supplier.status" + "bar.%" + "beep" + "foo.%.%" + ]; + }; + basicRole = submodule { + options.nixsap = { + "ALL" = objects; + "ALTER" = objects; + "CREATE" = objects; + "DELETE" = objects; + "DROP" = objects; + "INDEX" = objects; + "INSERT" = objects; + "SELECT" = objects; + "SHOW VIEW" = objects; + "UPDATE" = objects; + }; + }; + topRole = listOf str; + in either basicRole topRole; + +in { + options.nixsap.apps.mariadb = { + roles = mkOption { + type = attrsOf roleType; + default = {}; + description = '' + Defines MariaDB roles. A role can be a "basic" one or a "top" + one. The basic roles are granted of regular privileges like SELECT + or UPDATE, while the top roles are granted of other roles. For basic + roles MySQL wildcards ("%" and "_") can be used to specify objects + to be granted on, including databases, tables and columns names. A + script runs periodically to find all matching objects and grants on + them. Objects are denoted as "schema[.table[.column]]". + ''; + example = { + top_role = [ "basic_role" ]; + basic_role = { + SELECT = [ + "%bob\_live\_sg.brand\_type" + "%bob\_live\_sg.catalog%" + "%bob\_live\_sg.supplier.created\_at" + "%bob\_live\_sg.supplier.id\_supplier" + "%bob\_live\_sg.supplier.name%" + "%bob\_live\_sg.supplier.status" + "%bob\_live\_sg.supplier.type" + "%bob\_live\_sg.supplier.updated\_at" + ]; + }; + monitoring = { + SELECT = [ + "%.%.created_at" + ]; + }; + }; + }; + }; + + config = { + nixsap.apps.mariadb.configure = optionalString (roles != {}) addRoles; + nixsap.apps.mariadb.configure' = revokeRoles; + + systemd.services.mariadb-roles = mkIf (basicRoles != {}) { + description = "refresh MariaDB basic roles"; + after = [ "mariadb.service" "mariadb-maintenance.service" ]; + wantedBy = [ "multi-user.target" ]; + path = [ pkgs.mariadb ]; + serviceConfig = { + ExecStart = "${refreshRoles}/bin/refreshRoles"; + User = config.nixsap.apps.mariadb.user; + Restart = "always"; + }; + }; + }; +} + |