Forem: Sarath P M

Secure CloudSQL Connectivity with Google Cloud SQL Auth Proxy as Systemd

Sarath P M — Sun, 21 Jan 2024 05:54:10 +0000

Tired of juggling database security and accessibility? Enter Google Cloud SQL Auth Proxy, your gateway to secure and effortless connections to your Google Cloud SQL instances, without exposing external IPs. This guide will guide you through installing and configuring Cloud SQL Auth Proxy as Systemd on Ubuntu in two ways.

Unfamiliar with Cloud SQL Proxy? No worries! Check out the official documentation here: https://cloud.google.com/sql/docs/mysql/sql-proxy.

1. One-Click Script Installation:

This method is perfect for speed and simplicity. Just grab the script, customize it with your details, and run it!

Git Repo: https://github.com/sarath-pm/gcp-cloudsqlproxy-systemd

Step 1: Preparing the Environment:

To kickstart the process, ensure your Ubuntu environment is up-to-date by running the following commands:

sudo apt update && sudo apt install wget && sudo apt install git

Step 2: Clone the magic script:

git clone https://github.com/sarath-pm/gcp-cloudsqlproxy-systemd.git

Step 3: Navigate and Run the script:

cd gcp-cloudsqlproxy-systemd
chmod +x cloudsqlproxy.sh
./cloudsqlproxy.sh INSTANCE_CONNECTION_NAME PORT

Check for Permission Slips: Make Sure Your Service Account has required roles to Access Cloud SQL

That's it! If the cloudsqlproxy service is running, you've successfully installed Cloud SQL Auth Proxy.

2. Manual Installation for the Savvy:

Prefer to take the scenic route? This method gives you more control over the configuration.

Step 1: Preparing the Environment:

To kickstart the process, ensure your Ubuntu environment is up-to-date by running the following commands:

sudo apt update && sudo apt install wget

Step 2: Downloading and Installing Cloud SQL Proxy:

Now, let's get the Google Cloud SQL Proxy and make it executable:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy
sudo cp ~/cloud_sql_proxy /usr/local/bin

These commands fetch the proxy binary, make it executable, and place it in the /usr/local/bin directory for easy access.

Step 3: Creating the Systemd Service:

Next, we create a systemd service unit file for the Cloud SQL Proxy:

sudo vi /lib/systemd/system/cloudsqlproxy.service

Add the following content, replacing the placeholders INSTANCE_CONNECTION_NAME and PORT with your actual values:

[Unit]
Description=Google Cloud SQL Auth Proxy

[Service]
Type=simple
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:PORT
Restart=always
StandardOutput=journal
User=root

[Install]
WantedBy=multi-user.target

Ensure your compute engine bears the necessary Service Account permissions to access Cloud SQL. Alternatively, append credential_file=/path/default-account.json & to the ExecStart line in the cloudsqlproxy.service file, as illustrated below:
ExecStart=/usr/local/bin/cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:PORT -credential_file=/path/default-account.json &

This configuration specifies the service details, working directory, and the Cloud SQL instance connection parameters.

Step 4: Reloading and Starting the Service:

After creating the systemd service, reload the systemd manager configuration, start and enable the Cloud SQL Auth Proxy service:

sudo systemctl daemon-reload
sudo systemctl start cloudsqlproxy
sudo systemctl enable cloudsqlproxy

Step 5: Verification:

To ensure everything is running smoothly, check the status of the service:

sudo systemctl status cloudsqlproxy

If the service is active and running, congratulations! You've conquered manual installation!

Let's Connect!

Now, your applications can waltz effortlessly with your Google Cloud SQL database! Don't hesitate to leave questions or share your own adventures with Cloud SQL Auth Proxy in the comments below. Happy connecting! ✨
🌐 LinkedIn
🚀 GitHub

Deploying Adminer on Google App Engine (GAE) with Private and Public IP Connectivity

Sarath P M — Fri, 17 Nov 2023 14:26:17 +0000

Adminer

Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Unlike phpMyAdmin, it offers a single-file deployment, making it highly convenient for target servers. Adminer supports various databases, including MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Elasticsearch, MongoDB, and more through plugins. Its simplicity and broad compatibility make Adminer an efficient choice for database management.

This guide will walk you through deploying Adminer on Google App Engine standard environment, allowing you to connect to SQL servers using both Private and Public IPs.

Prerequisites

Before getting started, make sure you have the following:

A Google Cloud Platform account.
Basic knowledge of GCP services.

Step 1: Set up a GCP Project

Open the Google Cloud Console.
Create a new project or select an existing one.

Step 2: Enable App Engine API

Navigate to the App Engine section in the Google Cloud Console.
Enable App Engine for your project if it's not already enabled.

Step 3: Download the Adminer

You'll deploy Adminer as a service of your App Engine Standard application, so you must download the source code for Adminer. Follow these steps:

Open the Cloud Shell Terminal by navigating to the Cloud Shell or press g+s to open the Cloud Shell using the shortcut. Alternatively, you can use the Google Cloud SDK installed on your local machine.
In the terminal, create a new directory named Adminer and enter the following commands to download Adminer version 4.8.1 and save it as index.php.

mkdir Adminer
cd Adminer
wget https://github.com/vrana/adminer/releases/download/v4.8.1/adminer-4.8.1.php -O index.php

To use a different version of Adminer, use the links to available versions on the Adminer Downloads page.

Step 3: Prepare the Adminer files for deployment

In the directory that you created, named Adminer, create a new file named app.yaml file for App Engine configuration:

For Public IP connection to SQL server use the below app.yaml file.

service: adminer
runtime: php82

handlers:
- url: /.*
  script: index.php

For Private IP connection to SQL server use the below app.yaml file.

service: adminer
runtime: php82

handlers:
- url: /.*
  script: index.php

vpc_access_connector:
 name: projects/PROJECT_ID/locations/REGION/connectors/CONNECTOR_NAME

Where PROJECT_ID is your Google Cloud project ID, REGION is the region your connector is in, and CONNECTOR_NAME is the name of your Serverless VPC Access connector.

If you are deploying Adminer as the first and only application in App Engine, change the value for service from Adminer to default.

Normally, you would deploy phpMyAdmin as a service of an existing application and provide a name for the service. However, if you haven't yet deployed an application, then you are required to use the service name "default". That's fine for the purposes of this tutorial if you're trying out phpMyAdmin on App Engine.

Step 4: Deploy the application to App Engine:

Deploy the application by running the following command from within the Adminer directory where your app.yaml file is located:

gcloud app deploy --version=VERSION_ID

Replace VERSION_ID with the latest version

Step 5: Log in to Adminer

Once the deployment is complete, you can access Adminer in your web browser at
https://adminer-dot-[YOUR_APP_ID].appspot.com

Step 6: Clean Up (Optional)

If you want to clean up App Engine resources after testing:

gcloud app versions delete VERSION_ID

Replace VERSION_ID with the version you want to delete

Conclusion

Congratulations! You have successfully deployed Adminer on Google App Engine. Access your Adminer instance by navigating to the provided App Engine URL, and start managing your databases with ease.

Feel the freedom to tailor the deployment to suit your unique specifications. Enjoy your coding adventures, and don't forget to show some love for open source!

Extend your support to the ingenious mind behind Adminer, Jakub Vrána, by contributing to the dance of creativity on Patreon. Your generosity keeps the rhythm alive!

Let's Connect!

Feel the vibe and join the conversation. Your questions, ideas, and thoughts are not just welcomed but cherished. 🚀
🌐 LinkedIn
🚀 GitHub

Git Cheatsheet

Sarath P M — Sat, 03 Sep 2022 13:35:05 +0000

Hey there, fabulous folks! Behold, a swift and snappy cheat sheet for your Git command adventures:

git config

Setup author name and mail to be used for all commits in current repository, Devs commonly use --global flag to set config options for current user.
git config --global user.name "username"
git config --global user.email "usermail@example.com"

git clone

Clone the remote repository with HTTPS or with SSH. This should create a new child directory for the project sources.
git clone <REPO CLONE URL>

git status

Shows the status of your current branch. It will tell you which files you have modified, which files are stages for commit and which files are not. It will also let you know if your local branch is behind, or not up to date with, its remote tracking branch. This command is particularly useful in combination with git add when you only want to commit certain files.

git branch

Create a branch, or show a list of local or remote branches. With git branch, you can add options to do branch-related things:

git branch new-branch-name: Create a branch off of the current branch named new-branch-name

-d or --delete: Delete a branch. e.g. git branch -d feature-branch-name

-D: Force delete a branch. This is used when you want to delete a branch that has changes that weren't pushed to the server or merged. e.g. git branch -D feature-branch-name

-a or --all: Display all local and remote-tracking branches.

git fetch

Fetches branches from the server, along with their history and information. You can also use git fetch origin to update your remote-tracking branches.

git pull

Update your working directory by performing a git fetch, followed by a git merge. This is useful if you want to bring your branch up to date with the remote-tracking branch.
You can also use the --rebase option to perform a rebase rather than a merge.

git checkout

This command has a few functions. It can be used to create a new branch off of the current branch, it can be used to checkout a branch from the server, or switch to another branch.

git checkout develop: you will use this a lot. It will switch you from whatever branch you are currently in to the develop branch. If you have changes in your current branch, you will need to either stash them or undo your changes before you switch branches.

git checkout branch-on-server: If you want to work on another persons branch (assuming they already pushed their branch to the remote repository), you can use this command to create a copy of the branch on your local machine. You will need to do git fetch first to grab the information about the branch.

git checkout -b new-branch-name: create a new branch off of the current branch and switch to that branch. If you are in develop, this will create a new branch from develop. This command is synonymous to git branch new-branch-name && git checkout new-branch-name

git add

You made changes to your branch, and you want to stage your changes for committing. You will need to add them to what is known as the index. Here are two ways to do this:

git add /filepath/file.f: this will add the single file.

git add .: this will add all the changed files. Be careful here; make sure you aren't adding unwanted changes by reviewing your changes using git status. If you accidentally staged unwanted files, take a look at git reset described below.

git commit

You want to make a commit! Awesome! Here's how you do it:

git commit: This will make a commit of the files you have staged using git add, and will open a VIM editor (or whichever editor you have configured in your .gitconfig) to enter your commit message.

git commit --amend: This is used to modify the most recent commit. It combines changes in the staging environment with the latest commit, and creates a new commit. This new commit replaces the latest commit entirely.

git commit -a: This will make a commit and add all changed files, and will open an editor to enter your commit message. This is the same as doing git add . && git commit.

git commit -m "commit message": Skip the editor and put your entire message in the command line!

git commit -a -m "commit message": Combine them all! Fab.

git push

Once you make a commit, you will need to push your changes to the server to make it visible to the world. Once you push, your branch and commit(s) will be visible to others.

git push branch-name or git push origin HEAD: Push your branch upstream. You can use either version; using origin HEAD is simply a more verbose way of pushing. It signifies you are pushing the tip (HEAD) your branch to the same name on the remote origin.

git push origin -f branch-name or git push origin +branch-name: ❗ ❗ ❗ BE CAREFUL: This is a potentially scary command, and you should only use it if you really know what you are doing. It will overwrite the branch you specified on the remote repository with your local copy of the branch. ❗ ❗ ❗

git log

Show the history of all branches and their commits. Useful for seeing the most recent commit and getting commit hashes.

git log: Show all commits on this branch, along with the commit hash, author, date and message.

git log --oneline: Show a simplified version of the above command, showing only the commit hash and commit message.

git log --graph: Show a graph of the commit history for your branch. (AWESOME)

git log --max-count <n>: Limit the number of commits shown.

git log --oneline --graph --max-count 50: Combined them all! Woo!

git rebase

Rebasing is a bit more advanced, but incredibly useful when you need it. When you perform a rebase, you are changing the base of your branch. In essence, a rebase will look at each commit on your branch and update the code to make it seem like you've been working off the new base all along. Sometimes, a rebase will barf if it encounters a situation in which it tries to update a piece of code that you have already modified. In this case, it doesn't know which version of the code to use, so it leaves it to you to resolve them manually.
Although they have similar uses, a rebase differs from a merge in that a rebase updates your branch by tweaking each commit, and merge will update your branch by creating a new commit at the tip of your branch. Usually, there are standards and practices employed in a project or team around which method is preferred. Have a discussion with your team about which workflow they prefer.

git stash

Stashing allows you to save your current unstaged changes and bring your branch back to an unmodified state. When you stash, your changes are pushed onto a stack. This is especially useful if you need to quickly switch to another branch without having to commit your incomplete changes.

git stash: stash your unstaged changes.

git stash pop: unstash your changes.

git stash drop: drop your stashed changes. Careful, you will lose your changes!

git stash push <path>: stash a single file

git stash -m <message>: add a message to your stash

git reset

Git reset is used to unstage files or remove commits. It does so by changing what the tip of the branch (HEAD) points to.

HEAD: Represents the tip of your branch.

Index: Serves as the staging area, proposing changes for the next commit.

Working Directory: The location of files on your disk. There are three reset modes: --soft, --mixed (default), and --hard:

--soft: Unstages changes but keeps commit history intact.

--mixed: Adjusts the staging area without modifying the working directory.

--hard: Alters both the staging area and working directory.

Undo git add:

git add .

git reset -- file.html (unstages a single file)

git reset (unstages all changes)

Undo the Most Recent Commit:

git commit -a -m "commit message"

git reset --soft HEAD^ (undoes the commit at the branch's tip)

git commit -a -m "new commit message"

Undo a Commit Permanently:

git reset --hard HEAD~2 (removes the last two commits, i.e., HEAD and HEAD^)

Let's Connect!

Feel the vibe and join the conversation. Your questions, ideas, and thoughts are not just welcomed but cherished. 🚀
🌐 LinkedIn
🚀 GitHub