How to connect to a Google cloud SQL instance from an external PHPMyAdmin installation?

Question

I am trying to connect my existing PHPMyAdmin to a second server, which is a Google Cloud SQL 2nd gen mysql server with SSL. My current setup is:

VPS A with Ubuntu 16.04, phpmyadmin and a mariadb Google Cloud SQL instance

When connected to VPS A using ssh I am able to connect to the google cloud instance using the commandline, so the connection seems possible in principle, but there must be something wrong with my phpmyadmin config:

mysql --ssl-ca=/xxx/server-ca.pem --ssl-cert=/xxx/client-cert.pem --ssl-key /xxx/client-key.pem -h xxx.xxx.xxx.xxx -u phpmyadmin -p

This is my phpmyadmin config (/var/lib/phpmyadmin/config.inc.php):

$i++;
$cfg['Servers'][$i]['verbose'] = 'VPS A';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['nopassword'] = true;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'password';

$i++;
$cfg['Servers'][$i]['verbose'] = 'gcloud';
$cfg['Servers'][$i]['host'] = 'xxx.xxx.xxx.xxx';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'phpmyadmin';
$cfg['Servers'][$i]['password'] = 'same pw I used with the commandline';
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['key'] = '/xxx/client-key.pem';
$cfg['Servers'][$i]['cert'] = '/xxx/client-cert.pem';
$cfg['Servers'][$i]['ca'] = '/xxx/server-ca.pem';
$cfg['Servers'][$i]['capath'] = NULL;
$cfg['Servers'][$i]['cipher'] = NULL;
$cfg['Servers'][$i]['extension'] = 'mysqli';

Now when I visit my phpmyadmin login page, I can select gcloud as a server, I enter the same credentials I used for the command line connection I just get:

#1045 - Access denied for user 'xxx'@'xxx.xxx.xxx.xxx' (using password: YES)

But the connection to the localhost mariadb works just fine. Where did I go wrong?

Update: reading this: https://docs.phpmyadmin.net/en/latest/config.html#example-google-ssl

I found out I had to add $cfg['Servers'][$i]['ssl_verify'] = false; but it didn't help.

Update: After updating phpmyadmin to the latest version using a PPA, I received another error, googling which lead me to this: https://github.com/phpmyadmin/phpmyadmin/issues/12929

I applied the workaround and the new error is gone, but I still can't log in:

 mysqli_real_connect(): (HY000/1045): Access denied for user 'xxx'@'xxx.xxx.xxx.xxx' (using password: YES)

Because I have two servers configured, and it counts up for each one by default. Since this part was generated by phpmyadmin I guess it's correct. — Andreas Hartmann, Apr 08 '18 at 13:20
Ok. Which generation of Google Cloud SQL are you using? Is your cloud MySQL rather than PostgradSQL? — Martin, Apr 08 '18 at 13:22
Do you have the current and valid `.pem` files at the locations specified? — Martin, Apr 08 '18 at 13:25
Yes, I am using the exact same path as I did for the command line mysql command, with which I can connect successfully. — Andreas Hartmann, Apr 08 '18 at 13:26

score 0 · Answer 1 · answered Apr 18 '18 at 11:12

There are old similar cases in Stackoverflow and all of the resolutions point to a user privileges undefinition. In my opinion the most useful post to understand the problem is this one. Taking into account that you are using PHPMyAdmin have a look also on this drupal question.

How to connect to a Google cloud SQL instance from an external PHPMyAdmin installation?

1 Answers1