Salt: mysql_grants.present broken with `database: somedatabase.*`

[boltronics]

Description of Issue/Question

The Debian repositories were updated to 2018.3.0 and our MySQL grants were no longer applied correctly.

On a 2017.7.4 host:
[DEBUG ] Grant Query generated: GRANT ALL PRIVILEGES ON `mycompany`.* TO %(user)s@%(host)s args {'host': 'localhost', 'user': 'mycompany'}

On a 2018.3.0 host:
[DEBUG ] Grant Query generated: GRANT ALL PRIVILEGES ON `mycompany`.`*` TO %(user)s@%(host)s args {u'host': u'localhost', u'user': u'mycompany'}

We have no table named *. It is supposed to be treated as a glob.

The mysql execution module is at fault. This is why:

$ grep "is not '" mysql.py
        if dbc is not '*':
        if table is not '*':
    if dbc is not '*':
    if table is not '*':

It looks like the introduction of unicode_literals in commit 2e8b86a944c02285a7145a017e86d6573290c02c cause the above comparisons to always fail since they now compare a str type to a unicode type. I assume these should be using != instead? I'll put together a PR shortly.

Setup

Grant all privileges on mycompany.* for mycompany:
  mysql_grants.present:
    - grant: all privileges
    - database: mycompany.*
    - user: mycompany
    - host: localhost

Steps to Reproduce Issue

The key is adding a grant for a user to all tables associated with a database using <database>.* syntax.

Versions Report

```Salt Version:
Salt: 2018.3.0

Dependency Versions:
cffi: Not Installed
cherrypy: Not Installed
dateutil: 1.5
docker-py: Not Installed
gitdb: Not Installed
gitpython: Not Installed
ioflo: Not Installed
Jinja2: 2.7.2
libgit2: Not Installed
libnacl: Not Installed
M2Crypto: Not Installed
Mako: 0.9.1
msgpack-pure: Not Installed
msgpack-python: 0.4.6
mysql-python: 1.2.3
pycparser: Not Installed
pycrypto: 2.6.1
pycryptodome: Not Installed
pygit2: Not Installed
Python: 2.7.6 (default, Nov 23 2017, 15:49:48)
python-gnupg: Not Installed
PyYAML: 3.10
PyZMQ: 14.0.1
RAET: Not Installed
smmap: Not Installed
timelib: Not Installed
Tornado: 4.2.1
ZMQ: 4.0.5

System Versions:
dist: Ubuntu 14.04 trusty
locale: UTF-8
machine: x86_64
release: 4.14.0-0.bpo.3-amd64
system: Linux
version: Ubuntu 14.04 trusty

```

[Ch3LL]

thanks for the PR!! as this is fixed with your PR I will close this issue here but please let me know if you need me to re-open. Thanks :)

[edusperoni]

Can this be ported to the 2018.3 branch?

[tbronchain]

Still having this problem using the official repo. We have a system with automated deployments along several environments and updated all our recipes and scripts for 2018.3, thus manually forcing the previous version wouldn't be productive.

Is there any plan on publishing the fix to the debian/ubuntu repository? Any date set?
Thanks.

[boltronics]

@tbronchain Looks like you'll need to wait until 2018.3.1 is tagged and then packaged. No idea what else is planned for the point release, but I know a few things broke that need fixing. Some of it I haven't even finished investigating myself. This issue/PR just addresses my obvious problem - it's important to have a good staging environment!

I have two different techniques for upgrading while at the same time avoiding release issues.

1. For my non-critical home setup where I run a Xen host with a bunch of virtual machines all running Salt minions, I use an Apt pinning file under /etc/apt/preferences.d/ so I only upgrade Salt packages when I'm ready to do so, and can always force a downgrade. I also use a caching proxy so I can still use old packages if a given release (or the entire repo) disappears.

2. At my workplace, in addition to pinning I have my own Apt repository hosted in S3, and build my own releases. If I need something patched, I can include the patch in a new package release of a given Salt version. It has been the case in the past where a point release included a security fix but also broke some important functionality we require, and it was weeks before a corrected release was out. Hosting your own repository is the safest bet for production environments IMO.

You could also do something similar if you deploy from git. where you could just branch from an upstream tagged release and use that. It would be less work up front but I prefer to have everything on my servers packaged where possible.

For now, hopefully your environment configurations were all stored in git so you can just roll back and pin the old Salt version. HTH!

[tbronchain]

@boltronics thanks a lot for the quick reply!
I will consider your suggestions.
However, our setup gives us an additional issue. We are using Vagrant in development with the built-in salt plugin, which seems to use the latest salt release only on non windows hosts. So far, I can't find any clean way to work around this.

[boltronics]

You'll need to override the default Vagrant bootstrap script. From our dev Vagrantfile:

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|

  # ... normal config here ...

  # https://www.vagrantup.com/docs/provisioning/salt.html
  config.vm.synced_folder "../../salt/roots/", "/srv/salt/"
  config.vm.provision :salt do |salt|
    ## Install options
    salt.bootstrap_script = "../../salt/bootstrap.sh"

    ## Minion options
    salt.minion_config = "../../salt/minion"
    salt.minion_id = "vagrant-base-stretch-amd64"
    salt.masterless = true

    ## Execute states
    salt.run_highstate = true

    ## Output control
    salt.colorize = true
    salt.log_level = "warning"
    salt.verbose = true
  end
end

Then bootstrap.sh is just a bash script that deploys the repository keys, /etc/sources.list.d/ contents and and pinning data, and runs apt-get update and installs the required Salt packages. It shares 90% of the code with our salt-cloud deploy script.

I've tried to upstream various Salt bootstrap script improvements, and the devs aren't interested. Since it's not written in Ruby (and rightfully so since it runs on the VM which may not even have Ruby!), the Vagrant devs don't want anything more than the most basic shell script. Sad really.

[emersonveenstra]

This isn't fixed in 2018.3.2

check test mysql grant:
  mysql_grants.present:
    - grant: all privileges
    - database: "test.*"
    - user: test
    - host: '%'

Running that file gives this in the database:

+-------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*[censored]' |
| GRANT ALL PRIVILEGES ON `test`.`*` TO 'test'@'%'                                            |
+-------------------------------------------------------------------------------------------------------------+

setting escape: False fixes the escaping, but that has its own problems (see #48507)

Both master and minion are running 2018.3.2 on Ubuntu 16.04

[zerthimon]

@emersonveenstra This is a special problem with all privileges, that's a separate bug.
MySQL returns a list of privileges that translate to all privileges and the code tries to compare it to all privileges string