Search code examples
mysqlgoogle-cloud-platformgoogle-cloud-sqlgoogle-cloud-stackdriver

Google Cloud SQL - Catch bad logins

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: enter image description here

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

What I missed?

UPDATE 2:

Screenshot of my Stackdrive: enter image description here

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

Solution

  • 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

    1. Go under Stackdriver → Logging section.
    2. 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.
    3. Here you will find how to get and enable advanced log queries in your logs.
    4. 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.