CODEWITHSUNDEEP
ansible
Jeremy Kendall
Jeremy Kendall

Reputation: 2869

Ansible mysql_user priv - "invalid privileges string: Invalid privileges specified"

I'm using the mysql_user module to attempt to add a user and privileges. I'm getting an "invalid privileges string" error on the following task.

- name: Add api user
  mysql_user:
    name="user_name"
    host="{{ item }}"
    password={{ mysql_password }}
    priv={{ mysql_project_database }}.console_users:"SELECT (col_one, col_two)"
  with_items:
    - "%"
  tags:
    - mysql-user

I'm running the task from the command line like so:

ansible-playbook playbook.yml -i inventory/develop -vv --tags=mysql-user

Which results in the following error:

failed: [192.168.37.100] => (item=%) => {"failed": true, "item": "%"}
msg: invalid privileges string: Invalid privileges specified: frozenset(['SELECT (COL_ONE', ' COL_TWO)'])

I'm not 100% sure, but I think the issue is related to the space in the privilege. I can't seem to escape it or solve it with quotes (I've seen that suggested a few places).

I have tried:

I'm currently running ansible 1.8.4 installed via Homebrew on Mac OSX 10.10.2.

The playbook is provisioning an Ubuntu 14.04 box and MySQL 5.5.41-0ubuntu0.14.04.1 (Ubuntu)

Surely I'm missing something obvious. Any advice?

UPDATE

Debug output:

TASK: [console | Add api user] ************************************************
<192.168.37.100>
<192.168.37.100> host=% password=VALUE_HIDDEN name=user_name priv=db_name.console_users:"SELECT (col_one, col_two)"
<192.168.37.100> IdentityFile=/Users/jeremykendall/.ssh/id_rsa ConnectTimeout=10 PasswordAuthentication=no KbdInteractiveAuthentication=no User=deploy ForwardAgent=yes PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey StrictHostKeyChecking=no
<192.168.37.100>
<192.168.37.100> IdentityFile=/Users/jeremykendall/.ssh/id_rsa ConnectTimeout=10 'sudo -k && sudo -H -S -p "[sudo via ansible, key=sxqmfmrnzwqhgohgejmdydblzjczuvyr] password: " -u root /bin/sh -c '"'"'echo SUDO-SUCCESS-sxqmfmrnzwqhgohgejmdydblzjczuvyr; LANG=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /usr/bin/python /home/deploy/.ansible/tmp/ansible-tmp-1425679033.3-193113161035706/mysql_user; rm -rf /home/deploy/.ansible/tmp/ansible-tmp-1425679033.3-193113161035706/ >/dev/null 2>&1'"'"'' PasswordAuthentication=no KbdInteractiveAuthentication=no User=deploy ForwardAgent=yes PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey StrictHostKeyChecking=no
failed: [192.168.37.100] => (item=%) => {"failed": true, "item": "%"}
msg: invalid privileges string: Invalid privileges specified: frozenset(['SELECT (COL_ONE', ' COL_TWO)'])

FATAL: all hosts have already failed -- aborting

Upvotes: 1

Views: 6487

Answers (4)

Felix
Felix

Reputation: 301

This is currently an open issue in the Ansible MySQL module.

It started after the module began validating the privileges against a hardcoded list in the module. The validation is done by checking whether the list of new privileges is a subset of the list of valid privileges. Of course, SELECT, UPDATE, etc. are in the list. However SELECT(columnname) obviously is not. The validation logic is simply not smart enough.

This explains why it worked before and now no longer does.

You can find the issue report including a fix here: https://github.com/ansible/ansible-modules-core/issues/1120

UPDATE 30.06.: fix has been committed to the Ansible repository and should land in the next stable release: https://github.com/ansible/ansible-modules-core/commit/4ee18957dc95db427fec5a0cd66b6257b549213b

Upvotes: 5

Karol Nowak
Karol Nowak

Reputation: 662

Looks like handling of spaces might have changed recently. My run of mysql_user started failing in a recent ansible version because of ", SUPER"; fixed by changing to ",SUPER".

Upvotes: 0

Helldorado
Helldorado

Reputation: 1

It's work for me. 

# cat stackoverflow_28908155.yml
---

    - hosts: localhost
      user: ansible

      tasks:
        - name: Add api user
          mysql_user: >
            name="combo"
            host="{{ item }}"
            password="bogFanJoadFojBacUlvimudFilpUrcac"
            priv=dashboard.metrics:"SELECT (report_id, category)"
          with_items:
            - "%"
          tags:
            - mysql-user

    # mysql -e 'desc dashboard.metrics'
    +-----------+---------------+------+-----+---------+----------------+
    | Field     | Type          | Null | Key | Default | Extra          |
    +-----------+---------------+------+-----+---------+----------------+
    | id        | int(11)       | NO   | PRI | NULL    | auto_increment |
    | report_id | int(11)       | NO   | MUL | NULL    |                |
    | category  | varchar(255)  | YES  |     | NULL    |                |
    | name      | varchar(255)  | YES  |     | NULL    |                |
    | value     | decimal(12,6) | YES  |     | NULL    |                |
    +-----------+---------------+------+-----+---------+----------------+

    # ansible-playbook stackoverflow_28908155.yml

    PLAY [localhost] **************************************************************

    GATHERING FACTS ***************************************************************
    ok: [localhost]

    TASK: [Add api user] **********************************************************
    changed: [localhost] => (item=%)

    PLAY RECAP ********************************************************************
    localhost                  : ok=2    changed=1    unreachable=0    failed=0

    # mysql -Bse 'select Host,User,Password from mysql.user where User="combo";'
    %       combo   *5E916E80BFCBBEEDFEB614DEF49616F7F124D6EE

Upvotes: 0

Matt Parker
Matt Parker

Reputation: 284

I've got something like this in my playbooks working OK:

priv=db1.table_name1:SELECT,UPDATE(visible,refid,user_signature)/db2.table_name2:SELECT,INSERT

any good? So for yours:

priv={{ mysql_project_database }}.console_users:SELECT(col_one,col_two)

Upvotes: 0

Related Questions