Wrong GRANT for shared-db relation on collocated machine

[Michał Ajduk]

Environment:
Openstack Cloud (no juju spaces and no bindings support)
Vault deployed in HA mode on machines: 10.0.11.15, 10.0.11.16, 10.0.11.17
MySQL deployed in HA mode on the the same machines

When adding shared-db relation between MySQL and Vault, Vault charm adds GRANTs to MySQL tables. The issue is that Vault leader adds grant for localhost IP (127.0.0.1) and not unit's IP:
+------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') |
+------------------------------------------------------+
| SHOW GRANTS FOR 'vault'@'10.0.11.16'; |
| SHOW GRANTS FOR 'vault'@'10.0.11.17'; |
| SHOW GRANTS FOR 'vault'@'127.0.0.1'; |
+------------------------------------------------------+

Mysql is clustered using hacluster and has VIP set up:

applications:
  hacluster-mysql:
    charm: cs:hacluster
    options:
      cluster_count: 3
      failure_timeout: 180
      failed_actions_alert_type: "ignore"
  mysql:
    charm: cs:percona-cluster
    num_units: 3
    options:
      innodb-buffer-pool-size: 512M
      vip: *mysql-vip
      wait-timeout: 180
      min-cluster-size: 3
      enable-binlogs: True
      performance-schema: True
      nagios_context: *nagios-context
      max-connections: 200
      tuning-level: safest
    to:
      - 0
      - 1
      - 2
  vault:
    charm: cs:vault
    num_units: 3
    options:
      hostname: *mysql-vip
      nagios_context: *nagios-context
      auto-generate-root-ca-cert: true
relations:
  - [ mysql, hacluster-mysql]
  - ['mysql:shared-db', 'vault:shared-db']

Vault config file is pointing to MySQL VIP hence IP 10.0.11.15 is used for connection, not 127.0.0.1

[Aurelien Lourot (aurelien-lourot)]

Hi Michal, thanks for reporting! I couldn't reproduce it quickly with the provided bundle:

Model Controller Cloud/Region Version SLA Timestamp
lourot-vault lourot-serverstack serverstack/serverstack 2.7.6 unsupported 11:15:45Z

App Version Status Scale Charm Store Rev OS Notes
hacluster-mysql active 3 hacluster jujucharms 69 ubuntu
mysql 5.7.20 active 3 percona-cluster jujucharms 291 ubuntu
vault Unknown blocked 3 vault jujucharms 40 ubuntu

Unit Workload Agent Machine Public address Ports Message
mysql/0* active idle 0 172.20.0.4 3306/tcp Unit is ready
  hacluster-mysql/2 active idle 172.20.0.4 Unit is ready and clustered
mysql/1 active idle 1 172.20.0.18 3306/tcp Unit is ready
  hacluster-mysql/0* active idle 172.20.0.18 Unit is ready and clustered
mysql/2 active idle 2 172.20.0.7 3306/tcp Unit is ready
  hacluster-mysql/1 active idle 172.20.0.7 Unit is ready and clustered
vault/0 blocked idle 3 172.20.0.22 8200/tcp Vault needs to be initialized
vault/1 blocked idle 4 172.20.0.8 8200/tcp Unknown vault version
vault/2* blocked idle 5 172.20.0.28 8200/tcp Unknown vault version

Machine State DNS Inst id Series AZ Message
0 started 172.20.0.4 d4d0ec5b-ef9f-4239-b9a7-2c2465c5e3e9 bionic nova ACTIVE
1 started 172.20.0.18 8ba6929f-5a1e-4d4a-bc6e-4005ef21ff5b bionic nova ACTIVE
2 started 172.20.0.7 4c3b59e6-e7d6-4c70-a738-916b439c0fdd bionic nova ACTIVE
3 started 172.20.0.22 f37eb059-96e7-4e6d-b8e3-c2efa8f38e1a bionic nova ACTIVE
4 started 172.20.0.8 d8856edd-3c8e-4007-b97b-6fdf440e2e8c bionic nova ACTIVE
5 started 172.20.0.28 6b0af803-fa3a-4f20-8c30-0f7f5c9d1b60 bionic nova ACTIVE

$ juju run --unit vault/0 "relation-ids shared-db"
shared-db:4
$ juju run --unit vault/0 "relation-get -r shared-db:4 - mysql/0"
...
password: ****
...
$ juju ssh vault/0
$ mysql --user=vault --password=**** --host 172.20.0.42 vault # 42 is my vip

mysql> SHOW GRANTS FOR 'vault'@'172.20.0.22';
+------------------------------------------------------------+
| Grants for vault@172.20.0.22 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'vault'@'172.20.0.22' |
| GRANT ALL PRIVILEGES ON `vault`.* TO 'vault'@'172.20.0.22' |
+------------------------------------------------------------+

# same for IPs .8 and .28 .

mysql> SHOW GRANTS FOR 'vault'@'127.0.0.1';
ERROR 1141 (42000): There is no such grant defined for user 'vault' on host '127.0.0.1'

So I suspect this might have just been fixed meanwhile. What versions of the charms are you using?

I'm setting the bug status to 'Incomplete'. Please set it back to ...