MySQL secret backend - Error 1045: Access denied for user

[Chris Stevens]

I am testing the mysql secret backend for dynamic credentials on a local vagrant instance running both vault and mysql.

It works perfectly when the connection is configured with the "root" mysql user, but fails for me with any other username and combination of privileges and hosts (%, 127.0.0.1, localhost, etc).

The error looks like it is coming from the mysql server, but the host portion of the user specification has a "!" and I haven't noticed "(MISSING)" before when interacting with mysql:

* Error 1045: Access denied for user 'vault'@'%!'(MISSING) (using password: YES)

Vault version is v0.6.1 on linux.

Has anybody experienced this failure?

Full repro steps below.

Thanks!

- Chris

===

Mysql setup:

mysql  Ver 14.14 Distrib 5.6.32-78.0, for Linux (x86_64) using  6.2
DROP USER vault;
GRANT CREATE USER ON *.* TO 'vault'@'%' IDENTIFIED BY 'fizzbuzz' WITH GRANT OPTION;

Vault setup:

vault unmount mysql
vault mount mysql
vault write mysql/config/connection \
    connection_url="vault:fizzbuzz@tcp(127.0.0.1:3306)/"
vault write mysql/config/lease \
    lease=1h \
    lease_max=24h
vault write mysql/roles/testrole \
    sql="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT ALL ON *.* TO '{{name}}'@'%';"

Even though the connection is validated during setup, it doesn't work here:

vault read mysql/creds/testrole
Error reading mysql/creds/testrole: Error making API request.

URL: GET https://127.0.0.1:8200/v1/mysql/creds/testrole
Code: 500. Errors:

* 1 error(s) occurred:

* Error 1045: Access denied for user 'vault'@'%!'(MISSING) (using password: YES)

Switching the connection to "root" works every time:

vault write mysql/config/connection \
    connection_url="root:XXXXXX@tcp(127.0.0.1:3306)/"
vault read mysql/creds/testrole
Key             Value
---             -----
lease_id       mysql/creds/testrole/dd2d0255-aebe-e7b1-65e8-ea0f72e5b105
lease_duration 1h0m0s
....

[Chris Stevens]

Update:

I was finally able to get this to work, but only by configuring the mysql user with "ALL" privileges.

"CREATE USER" does not seem to be enough, even with the grant option.

I started out my testing with "ALL" privileges, so I'm not exactly sure what changed to have it work right now.

I will say that you should always write to the mysql/config/connection path when changing anything on the mysql side to force vault to get a new connection to the database server. 

[Jeff Mitchell]

Hi Chris,

I don't know myself which privileges you might need as that's also
going to be related to the creation SQL you specified (seems like
global CREATE USER or write to the mysql database however, see
http://dev.mysql.com/doc/refman/5.7/en/create-user.html).

However, % is going to be interpreted in the string as a formatting
directive when the templating is run...maybe \% would work but I'm not
sure after looking at https://golang.org/pkg/fmt. It's also possible
that you can simply leave off the @ portion of the user spec; the docs
seem to indicate it will automatically be set to % if you do.

Best,
Jeff

> --
> This mailing list is governed under the HashiCorp Community Guidelines -
> https://www.hashicorp.com/community-guidelines.html. Behavior in violation
> of those guidelines may result in your removal from this mailing list.
>
> GitHub Issues: https://github.com/hashicorp/vault/issues
> IRC: #vault-tool on Freenode
> ---
> You received this message because you are subscribed to the Google Groups
> "Vault" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to vault-tool+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/vault-tool/1af18cc0-bacb-4cdd-b1f2-361135bc9574%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.