GCP | How to access Cloud SQL private IP using Cloud SQL Auth Proxy and Identity-Aware Proxy (IAP)?

cloud sql in gcp is a very great fully managed database service that allows you to create mysql postgresql or microsoft sql databases fully managed and with minimal maintenance and management efforts from your side this service allows you to just not worry about your database solution and instead focus on building your applications and environment but connecting to a cloud sql instance is something that can be tricky at times and it can be extremely dangerous at other times as well in this video i will show you how you can easily and securely connect to your cloud sql database instances without sacrificing the security and agility of the platform and of the solution and just before i start on this please don't forget to like the video to help other people who would like to know more about connecting to cloud sql securely and easily to help them find this video easily and quickly so as you know gcp is a massive cloud platform that provides you with many services and tools to do your work and achieve your requirements and one of these services is cloud sql cloud sql is a managed database offer and service that lets you create mysql postgresql and microsoft sql databases in the cloud with a fully managed mood and approach you will get rid of the maintenance backup and patching work and only focus on maintaining the data integrity and access to these instances and tables this is an ideal option for cloud native solutions and implementations or to be used with other infrastructure as a service applications and needs such as virtual machines in compute engine or kubernetes engine even and so on of course when you want to use this you will need to access the instance somehow the database instance i mean and here where it will get tricky and sometimes bad for you you see when you create a cloud sql instance it comes with a default ip address by default but no source ip networks or no white lesser ips will be allowed or pre-configured until you do so from the connections tab basically you will need to whitelist the public ip from where the connection will come from and while this adds some sort of security it is not a perfect solution because if you use a dynamic ip address such as if you're connecting from your home um or maybe if you're having like a 5g or 4g connection then you will need to constantly keep managing that white listing rule and whitelisting syringe the connections tab just to make sure that you are allowing the right ip address and you are not locked out of the instance and even with this you are allowing an ip address that it's not only for you it's a it's an adding ip address that allows multiple other people or multiple other entities so it's not exactly 100 secure and it's not hundred percent matching to your own ip and to your own network and sometimes you may just tell yourself that let me add all or let me allow anywhere or anything you know just to get rid of this headache of constantly updating the rule and constantly changing or tracking your ip address to make sure you're updating the rule and all and this is the point where it will be bad and it will be a very bad idea actually while indeed you are using a managed cloud sql instance from google but you are still responsible for its security and access control and all of this and it's your responsibility to ensure proper access to the database and to have this proper access serum on the instance if your instance has public ip and if this public ip gets compromised or discovered by attackers or hackers then it's still a microsoft sequel it's still my sequel and it's still possibly sequel at the end and people will just be able to execute attacks against these database services and eventually breaking in and damaging your data and information so what's the solution for this well if you're using a select ip address then maybe the connections over a public ip would be possible or if you're also connecting from compute engine instance then again you would whitelist the public ip of the vm and you just keep using the public ipv cloud sql but you are now sure that only your vm will be able to access that cloud sql maybe also you can whitelist the net gateway ip address if you're using a net gateway but then what if there was another solution that does not use public ip addresses at all what if you don't even want to use public ips in your scenarios anyway maybe if this is something is not acceptable for you at all and your only acceptable solution is by connecting to private ip address of the instance well in this case you will have an answer to your problem with these two settings or with these two things in gcp first of all you enable and set up your instance to use a private ip address instead of the public ip address this does involve two steps or two items first of all you remove the public ip of the cloud sql instance the next step is basically you set up a an automated or an automatic vpc peering between your cloud sql instance and the compute engine vpc where your workloads are there or will be used to connect and access this cloud sql instance the next step here is to provision a new gce vm or even you can extend this and utilize a vm on your on-prem if you wanted to do this but then you deploy an application called cloud sql oauth proxy cloud sql auth proxy is an application developed by google to allow you to connect to cloud sql instances securely and without the need for authorized networks or without the need to configure ssl to use ssl in the connection now for cloud sql auth proxy to work you will need to have it deployed in a place with direct connectivity to the cloud sql it does not change the connectivity planes it does not bring up new connectivity options or anything the cloud sql proxy must see the cloud sql instance through a normal ip connection so that it allows you to connect to the cloud sql as well now the point of using the proxy here is that even when you are using the public ip of cloud sql you won't need to manually configure the authorized networks or anything and this is the first scenario because you can also use it with the private ip address only and without the public ip address and just connecting back to the previous point if you don't need or your requirements don't allow you to use public ip addresses then this is the answer for you and in general this is the connectivity architecture for cloud sql oauth proxy another point for this to work is you need to have the right impermissions assigned and this can be through a service account or it can be through a service account of the vm that is running this in compute engine but the option or the the serve that i'll show you will involve creating a service account and downloading the json file for the service account and then use to connect with cloud sql proxy cloud sql oauth proxy is a binary that you will download and install on any supported operating system in here i will show you how you can use it on a linux vm but then this is the same concept that you can use and apply on other operating systems such as windows linux or even mac and this is the download or the about page for this and if you just go to the right side index and you go to the download and install you'll find the instructions for whatever os that you're using so these are for linux these are for mac you have for windows and then you get to also have a a docker image so that you can run it in a container which is a very awesome feature actually of this so in my case i will just copy this command and then i'll run it in my vm and then i will execute this one so that i change it into an executable file and then i will move on to the next step which is configuring my instance to or configuring this to connect to my cloud sql instance so going to my ssh session which is this one so i bring it here this is my ssh session and all i have to do here is just executing this command basically just pasting this downloading this and then changing that file into an executable one so i will do chair mode plus x cloud and now if i do ls i will find the file that i have downloaded okay so with this i am now ready to connect to the cloud sql instance or to configure the cloud sql proxy to connect to the cloud sql instance but before i do this let me actually show you around in my environment so that you can understand what's going on and how things will work and all of this so going back to the admin code to the cloud console sorry this is my cloud sql instance and as you can see there is the private ip address there is no public ip address here and this might be more clear if you go to the landing page of the cloud sql service you will see the field of the public ip address empty and there is only the private ip address now this is very important because i'm going to connect to this through a vm in compute engine through the private ip address as you can see here and that connection will happen through the instance connection name value this is the one so this is a very important value that you need to copy from now so i will copy this just to keep it stored in the clipboard so let me take you now to the vpc vpc network and then vpc network peering you will find a single peering that has been created for me while i was enabling the private ip address on the cloud sql instance and if you go back to the introduction of this video i mentioned that cloud sql proxy needs an ip connection to the cloud sql instance this is why i'm using the private ip address and this is why there is this connection or the vpc peering it's just that there should be a network connectivity and the rest will happen through the api now if you go to the vpc networks you will see a single vpc here with a subnet in this region so with this vpc peering this subnet is able to talk to that cloud sql through the vpc pairing one more thing or actually two more things that i need to show you more than this if you go to the uh i am an admin and then if you go to service accounts you will find a single service account here that i have created manually and i have also created a key for this service account this key will become very relevant now because it will provide the authorization information that will be required by cloud sql proxy to be able to access this cloud sql instance if i take you to the i am page you see this service account now has a single role which is cloud sql client which is the required role for this to connect to the cloud sql instance the final thing that i want to show you here is an api that is called cloud sql api or cloud sql admin api and this admin api is also required you should have enabled this it should be enabled it's required for cloud sql to work and establish the connection between your whatever instance or whatever vm to the cloud sql instance in gcp so these are the few prerequisites that you should have i already copied the cloud sql connection name which will be relevant and important now so if i take you back to the ssh session here and when i start typing the command which is very simple so uh dot slash and then cloud you know just pressing tab for autocomplete then instances equals the connection name that i have just copied a moment ago to the clipboard and then i should type after this equals right after this it should be as a single word so it's a single word here equals and then i want to type tcp then colons and then the ip which i want to receive connections for or basically this group that i want to to listen for so if i just don't type anything if i just type the port it means that this is running in a vm and it will only accept connections that are coming from this vm to route them to cloud sql if i type zeros then columns and then 3306 it means that this cloud sql instance will listen on any incoming connection or it will accept any incoming connection and it will route it to the cloud sql instance just don't confuse this with opening this to the public internet or anything this is not related to this at all it's just telling that the cloud sql instance here the cloud sql proxy instance that it should accept connections on its network interface that's all what is telling this instance to do so the next argument here is ip dash ip underscore address i think address types equals private this is telling the cloud sql instance to just connect or to just look for the private ip address of the cloud sql instance and don't assume that there is a public ip address it it just saves time i think and some calls in the apis to determine if there is a public ip address or not and then the next one is the credentials file this is the credentials file here which which will provide the authorization information so i should type credential file equals the file name now i hope that i typed everything correct and when i press enter you will see now the connection or the cloud sql proxy instance will start listening for incoming connections and once we connect to this it will route us to the cloud sql instance in gcp in the backend and you see now it's saying ready now for new connections and if you want to validate this you can simply connect to the ip of this instance on the port 3306 but then there is one single trick that i have done on this project which is this vm that i'm connected to it does not have a public ip address so it's only having an internal ip without a public ip address so there is no way for me to even connect to this instance and access the cloud sql database that is behind this instance now so this is where iap comes in and this is where you will be able to connect to your cloud sql instance without even a public ip address and i have already enabled iap on this project if i take you to the iap admin identity or proxy in a new tab you will find it's enabled and you will find that my user it's my user should already have access because i have ownership there so i am the owner and i can already access the vm the firewall rules are already set up and everything is in place to allow iap connectivity and to enable a people and to enable me to access this vm through iap so now all i have to do here is to just set up an iap tunnel by opening command prompt and then that command is i hopefully by now it's very common for you which is gcloud compute start iap dash tunnel and just me reveal this name so the instance name is sql dash oauth dash proxy testing and then the port that i want to connect to which is 3306 which is the port that is being used by the cloud sql auth proxy and then dash dash local localhost then also 3306 actually i didn't type equals here and then um just out of making sure there are no errors or anything this instance is in europe quest 1b so i will type zune equals europe can't try properly today so this is the command to establish the iap tunnel and it is now listening on this connection or on this port now in order to validate this all i have to do here is open the mysql workbench and then connect to localhost and i should be able to access the instance here so cloud sql with cloud sql oauth proxy i'm not sure if this is a very long name but then when i just click the test connection it's going to ask me for the password and i already have the password somewhere here um okay this is the password you remember this and it should give me that the the connection is successfully established now if you want to validate further you know just to see what's going on there so i'm going to connect to this one and just to confirm that i am connected through the whole thing here you will see in one of the databases in schemas there is the a database or a table called test cloud sql it's a database and if i take you to cloud sql here you should find the same database there so this is the same instance that i'm connected to and all of this is happening through the private ip address without exposing anything or any component to the internet with a public ip address so this is the same database that i have in the mysql workbench and that's all you can see how easy and secure it is to access your cloud sql databases without even sacrificing the security on the account of connectivity and also by bringing some other useful features and stuff as well if you are a developer or if you have developers in your organization you can even let them connect to their own systems as if the database is installed in their systems by replicating this same example actually or by just deploying cloud sql proxy directly on their system if you want to keep using public ip addresses of cloud sql also i don't recommend doing this and again what's best in this is that you can take it further by implementing this in a docker container or a docker image or even you can put it behind a or you can put it as part of a managed instance group you can guarantee then the availability of the proxy vm and the availability of this and you can ensure that it is healthy all of the time with automatic health checks and even automatic scalability if at any point in time a lot of pressure and a lot of requests came in so that you can scale this horizontally even finally if you have any questions or if you have any comment about this video or any of the other content please don't hesitate to put it in the comments section and i will be more than happy to discuss it with you and to provide any feedback or answer any question for you anytime also please don't forget to like this video and subscribe to get more content and click that bell button so that you can get notified whenever i put any new videos and also if you are an existing google workspace admin or if you are considering to be one then please check out my course anademi google workspace admin the complete course it is a comprehensive course that will cover a lot of topics such as users and groups management device management data security and privacy and a lot of stuff actually you can get it from the link below in the video description at a discounted price and once you do that you will get lifetime access with constant updates and new content all of the time finally thanks a lot for your time and thanks a lot for watching until i see you again in a new video stay safe and take care

Channel: The Cloud Nerd

Views: 18,672

Keywords: gcp, google cloud, google cloud platform, google cloud sql, cloud sql, mysql, postgresql, pgsql, ms sql, microsoft sql server, sql server, identity-aware proxy, iap, gcp security, google cloud security, iap tunnel, how to connect to cloud sql, gcp tutorial, gcp guide, how to, cloud sql auth proxy, cloud sql auth, sql auth proxy, salehram, the cloud nerd, thecloudnerd, cloudnerd, acloudnerd, a cloud nerd

Id: rebyg9_eTHM

Length: 22min 16sec (1336 seconds)

Published: Tue Apr 19 2022