3

I have an issue with a java BasicDataSource connection pool.

My ProxySql is correctly configured on all my DataProc Compute engine, if I try to connect to my CloudSql instance with sqlclient it works correctly. When I try to connect to my CloudSql from my connection pool, configured with this connection string: pooljdbc:mysql://127.0.0.1/my_db?serverTimezone=UTC, I get this error:

Cannot create PoolableConnectionFactory (Access denied for user 'myuser'@'localhost' (using password: YES))
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'myuser'@'localhost' (using password: YES))
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)

And is correct because the proxySQL tcp connection is on 127.0.0.1 and not on localhost (unix socket).

I don't understand why the BasicDataSource try to connect on localhost and not on 127.0.0.1 as I have configured in the connection string.

Any help is really appreciated.

Claudio
  • 642
  • 3
  • 12

1 Answers1

0

The string 'myuser'@'localhost' is referring to your login identity, not the server connection string; if you ran your connection attempt from a machine called foo-instance, for example, even if you use a connection string specifying a remote IP address, your login would be 'myuser'@'foo-instance'. While direct connections using your sqlclient will resolve the source hostname differently based on the server hostname specified, this is a quirk that shouldn't be relied on. In particular, in your case the ProxySQL service will be acting on your behalf, so the source hostname will come from the ProxySQL instance, not from your connection string.

You may want to share more details on your ProxySQL configuration, but if you're using defaults, your PROXYSQL_HOSTNAME would be set to localhost; you could try ensuring your PROXYSQL_HOSTNAME is set to 127.0.0.1 instead.

Interestingly, based on cursory testing, it appears creating user grants identified by 127.0.0.1 is more restrictive to login host, while creating the grant for localhost allows both 127.0.0.1 and localhost for connection hostnames:

MariaDB [(none)]> create user 'dhuo'@'127.0.0.1' identified by 'dhuopass';
...
$ mysql -h localhost -pdhuopass
ERROR 1045 (28000): Access denied for user 'dhuo'@'localhost' (using password: YES)
$ mysql -h 127.0.0.1 -pdhuopass
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...
MariaDB [(none)]> drop user dhuo@127.0.0.1
MariaDB [(none)]> create user 'dhuo'@'localhost' identified by 'dhuopass';
...
$ mysql -h localhost -pdhuopass
Welcome to the MariaDB monitor.  Commands end with ; or \g.
$ mysql -h 127.0.0.1 -pdhuopass
Welcome to the MariaDB monitor.  Commands end with ; or \g.

Generally it'd probably work best to configure your user grants to be identified as coming from localhost instead of 127.0.0.1 to have the best chance of supporting either inbound hostname.

Dennis Huo
  • 10,517
  • 27
  • 43
  • Thanks Dennis for your answer, I tried to set PROXYSQL_HOSTNAME as env variable to localhost but nothing is changed. Is it correct to set PROXYSQL_HOSTNAME as env variable? If no, Where I can find this setting for CloudProxySQL installed on each dataproc machine? – Claudio May 23 '19 at 09:55
  • Which ProxySQL are you using? Dataproc doesn't install ProxySQL in any of its init actions or base images, are you using this? https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration Normally people shouldn't need ProxySQL, but I assume you have specific reasons for using it – Dennis Huo May 23 '19 at 17:11
  • I'm using this: https://github.com/GoogleCloudPlatform/dataproc-initialization-actions/tree/master/cloud-sql-proxy Init script on each my dataproc cluster machine. I'm using proxy to connect to GCP CloudSql that have public ip. My dataproc cluster is created when I need and then destroyed, so I don't know the DataProc machines ip addresses and I can't configure manually on the CloudSql instance. – Claudio May 24 '19 at 07:20
  • Oh I see, so you're not using ProxySQL, you're using CloudSQL Proxy. How are you setting up your connection pool in code, and why do you need to connect to 127.0.0.1 instead of localhost? – Dennis Huo May 24 '19 at 20:04
  • Hi Dennis, I istall the proxy with this script: https://github.com/klaus82/gcp_scripts/blob/master/install-sql-proxy.shhttps://github.com/klaus82/gcp_scripts/blob/master/install-sql-proxy.sh What I need is to connect to CloudSql from my Dataproc Compute engine, on 127.0.0.1 or localhost doesn't matter. I tried to create a user on CloudSql with localhost (as you suggested) but doesn't work. – Claudio May 27 '19 at 07:27