Google Cloud SQL - Catch bad logins

Question

I have an existing MySQL (version 5.7) instance hosted (managed) by Google Cloud SQL. I want to get a notification when someone is trying to connect my database with a bad username\password.

My idea was to look for it on the Google Stackrive logs, but it's not there.

There is an option to collect this information?

UPDATE 1:

I tried to connect the instance with gcloud but unfortunately, it's not working.

$ gcloud sql connect mydb
Whitelisting your IP for incoming connection for 5 minutes...done.
ERROR: (gcloud.sql.connect) It seems your client does not have ipv6 connectivity and the database instance does not have an ipv4 address. Please request an ipv4 address for this database instance.

It's because the database is accessible only inside the internal network. I searched for flags like --internal-ip but didn't find one.

However, I was guessing that it's not making any difference if I'll try to access the database from my DB editor (workbench). So I did it:

Searching for the query that @Christopher advised - but it's not there.

What I missed?

UPDATE 2:

Screenshot of my Stackdrive:

Even if I remove this (resource.labels.database_id="***") condition - the result is the same.

ERROR: (gcloud.sql.connect) It seems your client does not have ipv6 connectivity and the database instance does not have an ipv4 address. Please request an ipv4 address for this database instance. This is caused because you are trying to connect to the Cloud SQL instance using the gcloud command when you have configured a private IP. You will not be able to connect to your Cloud SQL instance with MySQL workbench either since they are not in the same network. — Christopher Rodriguez Conde
If you would like to connect using the gcloud command you will need to enable a public IP or use the Cloud SQL proxy or a Google Compute Instance in the same VPC subnet. Finally, if you are connecting from an external source, you will need to follow this steps. — Christopher Rodriguez Conde
After that, you should be prompt to connect to your Cloud SQL instance. If you enter the wrong credentials, and follow the steps provided in the solution you should be able to see a log with the following message: [TIMEFRAME][Note] Access denied for user 'USERNAME'@'[IP]' (using password: YES). Please make sure to change [PROJECT_ID] and [CLOUD_SQL_INSTANCE] with the proper values of your project ID and Cloud SQL instance. I follow the steps and I can assure you the logs are showing properly. I hope it really helps. — Christopher Rodriguez Conde
Workbench is working since I'm joining the internal network with a VPN. When I'm using the right credentials - I can see my tables. The strange thing is that bad login not written to the log. — No1Lives4Ever
Could you add an image about the logs you are seeing when using advanced log queries? Please make sure to sanitize the image before uploading it to avoid leaking sensitive information. I don't really know how the logs are produced when using Workbench. I tried using the gcloud command with Cloud Shell and logs are showing just fine. I hope it helps — Christopher Rodriguez Conde

Christopher Rodriguez Conde Christopher Rodriguez Conde · Accepted Answer · 2020-02-04T12:45:56

There is an option to collect this information?

One of the best options to collect information about who is trying to connect to your Google Cloud SQL instance with wrong credentials is Stackdriver Logging.

Before beginning

To reproduce this steps, I connected to the Cloud SQL instance using the gcloud command:

gcloud sql connect [CLOUD_SQL_INSTANCE]

I am not entirely sure if using the mysql command line something will change along the lines, but in case it does, you should only look for the new log message, and update the last boolean entry (from point 4 on).

How to collect this information from Stackdriver Logging

Go under Stackdriver → Logging section.
To get the information we are looking for, we will use advanced log queries. Advanced log queries are expressions that can specify a set of log entries from any number of logs. Advanced logs queries can be used in the Logs Viewer, the Logging API, or the gcloud command-line tool. They are a powerful tool to get information from logs.
Here you will find how to get and enable advanced log queries in your logs.
Advanced log queries are just boolean expressions that specify a subset of all the log entries in your project. To find out who has enter with wrong credentials into your Cloud SQL instance running MySQL, we will use the following queries:

    resource.type="cloudsql_database" 
    resource.labels.database_id="[PROJECT_ID]:[CLOUD_SQL_INSTANCE]" 
    textPayload:"Access denied for user"

Where [PROJECT_ID] corresponds to your project ID and [CLOUD_SQL_INSTANCE] corresponds to the name of the Cloud SQL instance you would like to supervise.

If you notice, the last boolean expression corresponding to textPayload uses the : operator.

As described here by using the : operator we are looking for matches with any sub string in the log entry field, so every log that matches the string specified, which in this case is: "Access denied for user".

If now some user enters the wrong credentials, you should see a message like the following appear within your logs:

[TIMEFRAME][Note] Access denied for user 'USERNAME'@'[IP]' (using password: YES)

From here is a matter of using one of GCP products to send you a notification when a user enters the wrong credentials.

I hope it helps.