IAM based authentication for CloudSql and how to connect to CloudSql with private ip from local machine.

Google Cloud CloudSQL natively supports IAM integration. Hence, users can login to the Cloud SQL using their email cloud identity and the user permissions to the databases can be managed. This process uses short lived authentication tokens and requires no passwords. The users’ login activity is tracked in the logs.

Implementing Cloud SQL IAM database authentication to all your databases makes the user management of databases safer, secure, and efficient.

Let’s discuss how we can set up IAM database authentication in the Cloud Sql.

First step is to enable IAM based authentication on the CloudSql instance , below is the command that can be used to do so

cloudsql.iam_authentication=on

From the console , we can also enable it by setting up the flag

IAM authentication allows users to connect to CloudSql using their cloud Identity. Any database level authorizations need to be taken care of within the database itself.

i.e. granting read access or write access to tables , schemas, databases still needs to be granted as database commands rather than IAM roles.

Once the IAM authentication flag is set tot on , the next step is to grant “Cloud SQL Instance user” role to users or groups . This role allows users to authenticate into the cloudsql using their cloud identity.

Once the role is granted the next step is to add the user or group to the CloudSql instance as below,

Navigate to ‘Console → SQL → Select Instance → Users → Add user account→ Cloud IAM → Enter email address → Add’

At this poin users can connect to CloudSql and login into the Database. Still this wont grant users the access neede to query the tables or schemas. This only covers the authentication part , the authorization access needs to be granted on the database side.

Lets see how we can create a role and grant the same to a cloud identity or group.

CREATE ROLE role_ro;

\connect databaseName;

GRANT CONNECT ON DATABASE databaseName TO role_ro;

GRANT USAGE ON SCHEMA schemaName TO role_ro;

GRANT SELECT ON ALL TABLES IN SCHEMA schemaName TO role_ro;

Once the role is created the next step is to grant the role to the user or group.

GRANT role_ro to user@example.com

Connecting to the CloudSQL using IAM Database Authentication

The secure way to connect to a Cloud SQL instance is via CloudSQL Auth proxy or using Java/Python Native client.

In this blog we will discuss about how we can use Auth proxy to connect to a CloudSQL instance with both public and private ip’s.

First we need to install the latest version Cloud SQL auth proxy binary, then authenticate to the Google Cloud IAM.

$ gcloud auth login

Once the login is successful , let initiate the proxy connection

• Instance with Public IP

./cloud_sql_proxy -enable_iam_login -instances=PROJECT:REGION:CLOUDSQL_INSTANCE_NAME=tcp:port

Once the connection is established, now we can use cloud identity to connect to the database.

psql “host=127.0.0.1 port=$CLOUDSQLPROXYPORT dbname=$DBNAME user=user@example.com sslmode=disable”

The above command gets the credentials from the below environment variable.

GOOGLE_APPLICATION_CREDENTIALS

Since we are connecting to the CloudSQL instance publicly, it will expect a SSL certificate — so, thesslmode parameter is set to disable.However, the Cloud SQL Auth proxy does provide an encrypted connection.

• Instance with Private IP

The above method works the same way for privateIp instances if you have a VPN connection to the GCP network or a dedicated interconnect to the gcp network.

If you don’t have a vpn or internet connection , you can not initiate a proxy and connect to CloudSql from your machine. There is a work around for testing purpose only is to create a VM within the same network as CloudSql instance and use CloudIAP tunneling to shh into the VM. From the VM we can initiate the Cloudproxy. Again this is a stop gap and should only be used for dev environments.

For TCP connections, GCP’s Identity-Aware Proxy (IAP) enables access to GCE VMs from the internet through their private IP. When an attempt is made to establish an HTTPS encrypted tunnel to the proxy, the proxy performs an authentication and authorization check. After successful authentication and authorization, traffic from the client is forwarded on Google’s internal network to the VM instance by the proxy. In other words, the proxy acts as a gatekeeper for incoming traffic that is exposed to the outside world while the VM only has to allow for connections from the proxy on the Google internal network.

Tunnel Configuration

To configure the IAP tunnel I am assuming that you have the following

• A GCP project you have ownership of with a running GCE VM and for Windows VMs, login and password — make sure that you disable the assignment of a public IP when you create the VM — for Windows nodes make sure that you don’t forget to specify username and password after the VM has been created
• The Google Cloud SDK installed on the system you want to connect from
• If you are behind a corporate proxy you may have to whitelist the domain ‘tunnel.cloudproxy.app’ used by IAP for TC

Let us first create a firewall rule that allows traffic from the IAP to the VM. IAP uses the range 35.235.240.0/20 as a source address for forwarding traffic. we allow for ports 22 (ssh).

gcloud compute firewall-rules create allow-ingress-from-iap \ — direction=INGRESS \ — action=allow — rules=tcp:3389,tcp:22,tcp:5901 \ — source-ranges=35.235.240.0/20

The above command allows for connections from the proxy to all nodes in your project. If you don’t want to open up the firewall for all VMs in your project you can attach a tag to the VMs you want the rule to apply to and specify that tag with ‘ — target-tags=TAG’ as an additional option.

Now the user needs to be granted an IAM policy that allows them to establish tunneled connections. For that we need to grant the role iap.tunnelResourceAccessor to authorized users and/or user groups. In this example this role is set at the project level. This allows users to establish connections to all VMs in the project to which the above firewall rules apply.

gcloud projects add-iam-policy-binding iap-access-test \

— member=user:user@example.com \

— role=roles/iap.tunnelResourceAccessor

The user also needs to have the compute.viewer role

gcloud projects add-iam-policy-binding iap-access-test \

— member=user:user@example.com \

— role=roles/compute.viewer

Connecting with SSH: Linux-Linux on GCE

If you want to connect to a Linux node via the port forwarding over ssh use the below command.

gcloud compute ssh iap-test-ubuntu — project iap-access-test \

— zone us-central1-a — ssh-flag “-L 5901:localhost:5901”

Now that you will be able to ssh into the VM which is in the same VPC network as the CloudSql instance, you can follow the cloudSql proxy setup from the previous section and be able to test your connections and db setup without logging into the VM.