Postman with Authenticated Google API HTTP Requests

Let's say I want to send an HTTP GET request to a Google API using the super helpful Postman application. That's simple enough, I'll just choose GET and enter the URL. In this example, I'll search YouTube for videos with "test" in their details.

https://www.googleapis.com/youtube/v3/search?q=test&key=AIzaSyDbnfRUVxqMEJqYwxxxxx-uV75_GaPTVr0&part=snippet&type=video

Breaking down this URL, you'll see that the query ("q") for "test" is in there and I'm asking for only the 'snippet' details of videos to be returned ("type=video"). Without the type optional parameter, the search would also return channels.

The only cumbersome thing about this GET request is that I need a Google API Key. These keys allow Google to limit how many API requests any account is making--and potentially charge the user when the count gets too high. To get a key, you need to a Google Cloud Platform (GCP) account and then follow the instructions to create an API key from the API Manager section.

Since API keys are--by definition--limited, most people try to keep them private and restrict who can use them. There are a few different ways to add API Key Restrictions in GCP. "HTTP referrers (websites)" is a popular and straightforward option. For example, if you were using the key from a local website, you could add http://localhost:/* to the list.

Note: If you don't care about your key being used by others, then you can leave the key with no restrictions and skip the next part.

In Postman, you can pretend you're sending the request from a local website by adding a "Referer" header entry. However, since it's a restricted header, there is an extra step. You must turn on Postman Interceptor by clicking the Interceptor button at the top of the window (next to the sync button). If you have interceptor off, the Referer header entry will be ignored and you'll get an error: "Error 403:The request did not specify any referer. Please ensure that the client is sending referer or use the API Console to remove the referer restrictions."

Now you're sending the GET request with an API key and you're getting back JSON results that look like this:

{
    "kind": "youtube#searchListResponse",
    "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/71Y1Pa_Vox_0ZzzjdbBNppwdf0s\"",
    "nextPageToken": "CAUQAA",
    "regionCode": "CA",
    "pageInfo": {
        "totalResults": 1000000,
        "resultsPerPage": 5
    },
    "items": [
        {
            "kind": "youtube#searchResult",
            "etag": "\"m2yskBQFythfE4irbTIeOgYYfBU/WePlVVP0Z4fWK6zl92pA9jVLbdQ\"",
            "id": {
                "kind": "youtube#video",

...

For more about the Google API options for YouTube Search, refer to the YouTube developer documentation.

Just that query alone is useful, but there's still one key thing missing from our request--authentication. I'm not going to go into detail about OAuth authentication here--you can read about that elsewhere--so this section will just follow the basic steps. To use the Google APIs as an authenticated user, you need an OAuth token.

There are a few ways to get a Google OAuth token. Probably the simplest option is to use Google's own OAuth playground. This is a super useful app that allows you to fiddle with all sorts of settings.

Another option is to use Postman's built in Get New Access Token feature. To use it, click on Authorization (next to the Headers tab) and then the "Get New Access Token" button. Here you would enter the Auth URL as https://accounts.google.com/o/oauth2/auth and the Access Token URL as https://accounts.google.com/o/oauth2/token. You also need to know the Scope for your request--which you probably just want to go to Google's OAuth Playground to get anyway.

Once you have an access token, a simple method to check its validity is to paste this URL into a browser address bar: https://www.googleapis.com/oauth2/v1/tokeninfo?access_token=

Note: Checking the token will show its expiration time and also its scopes.

Google Cloud Platform for a Full-stack Angular Web Application

At first blush, getting a complete Angular web application deployed and running on the Google Cloud Platform (GCP) can be a daunting task. Each quickstart tutorial is reasonable enough, but if you read ahead through all of the various documentation pages you'll need, they can soon begin to snowball.

UPDATE: After I started writing this post, I discovered a Google lab that covers the same topic: Build a Node.js & Angular Web App Using Google Cloud Platform.

I'll just quickly provide an overview of deploying Angular as the front-end app (on Google App Engine) and MySQL with a Node.js API on the backend. MySQL will run on Cloud SQL and Node.js runs on App Engine.

The basic steps involved are:

1. Set up a GCP account and project

2. Download the Angular sample and install the requirements

3. Deploy your Angular front-end app to GCP

4. Set up a MySQL DB on Cloud SQL. I wrote a whole post about setting on MySQL on Google Cloud SQL.

5. Complete the tutorial for using Google Cloud with Node.js

Since these samples aren't linked, you'll have to test the pieces separately until you develop some interaction in your Angular app. You can use Postman to test your Node.js API.

Set up MySQL on Google Cloud Platform

Here's a walkthrough of how to set up MySQL on Google Cloud Platform's (GCP) Cloud SQL service.

1. Create a Google account if you don't have one: https://accounts.google.com

2. Sign up for a free trial on: https://console.cloud.google.com

3. Create a GCP project to house your MySQL instance.

4. Create a Cloud SQL instance--obviously, choosing MySQL as the DB type.

5. Create a database in the instance by going to the Databases tab under the Instance. For this post, I'll create one called "test".

6. To add a schema to your DB, you can use the Cloud SQL command line, https://dbdesigner.net or MySQL Workbench. Of course, if you know SQL, you can just type out your schema, but if you want a visual representation of your DB, these tools are handy.

I like the simplicity and no-install aspect of dbdesginer.net, but I ran into a bug, so I don't use it as much as I would otherwise. Hopefully, they'll fix it soon. Until then, I guess I have to recommend MySQL Workbench since it's great in many ways.

7. If you just want to quickly test that your DB is working. You can run some simple queries in the Google Cloud Shell. To connect to your MySQL instance, open the shell (little command line icon in the top-right of the GCP dashboard.) and type in these commands:

googleusername@projectId:~$ gcloud beta sql connect dbinstance --user=dbusername (usually root for username)

You'll be prompted for your password and then you'll see the MySQL prompt. Note that it might take a few seconds before it appears.

Whitelisting your IP for incoming connection for 5 minutes...\
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1234
Server version: 5.7.14-google-log (Google)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>;

8. To run your test queries, you'll need to select your DB within your instance.

mysql>; use test
Database changed

Once you've selected the DB, you can run a simple operation to create a table and then use SHOW TABLES; to see the results.

The command I'll use to create a simple table is:

CREATE TABLE `testtable` (
`id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

This creates a new table with one column called "id".

And this is what it looks like when I paste this command into the Cloud Shell:

mysql>; CREATE TABLE `testtable` (
    >; `id` INT NOT NULL AUTO_INCREMENT,
    >; PRIMARY KEY (`id`)
    >; );
Query OK, 0 rows affected (0.03 sec)
mysql>; show tables;
+----------------+
| Tables_in_test |
+----------------+
| testtable      |
+----------------+
1 row in set (0.00 sec)
mysql>;

9. Now that you have a working DB, you'll want to connect to it from code and possibly from a MySQL client (e.g., the 'dolphin' a.k.a. MySQL Workbench).

To get the info you need, go to the Instance Details page in GCP and look at the Properties under the Overview tab. What you need is the "IPv4 address". Grab that, but you won't be able to connect to your instance yet. First, you have to allow connections.

To allow your client to connect to your DB in the Google cloud, you need to open Authorized Networks and add your network IP. To get your client IP, type "what's my IP" into Google.

10. Now you can access your DB from a MySQL client, but what about a local dev instance of a GCP App Engine application? For that you need a Google Cloud SQL Proxy running.

NOTE: The default setting is for all apps within the same project to be authorized, if you're using the DB from a different GCP project, you'll have to authorize it explicitly.

Without the Cloud SQL Proxy running, your local dev instance of your app will return the error: Error: connect ECONNREFUSED xxx.0.0.xxx:3306.

You'll need to install the Google Cloud SDK (configuration will have you select the project). If you have multiple environments [e.g., test, dev, prod, etc. You may have to change this as some point with this command: cawood$ gcloud config set project projectName]), and enable the Cloud SQL API, if you haven't already. You'll also need a credential file to supply to the proxy. To get this file, create a service account in the IAM & Admin section of GCP. Make sure you grant the account rights to Cloud SQL. When you create the account, you'll be able to create a new private key and download a JSON credential file. The Cloud Proxy will read this credential file.

https://cloud.google.com/sql/docs/mysql/connect-external-app

11. Once you have everything ready, here is the command to start the Cloud Proxy:
./cloud_sql_proxy -instances=instanceConnectionName=tcp:3306 \ -credential_file=credentials.json &

If all goes well, the Cloud Proxy command will return this output:

cawood$ 2017/04/23 22:34:39 Listening on 127.0.0.1:3306 for instanceConnectionName
2017/04/23 22:34:39 Ready for new connections


FYI: From the command line help:
Authorization:
  * On Google Compute Engine, the default service account is used.
    The Cloud SQL API must be enabled for the VM.

  * When gcloud is installed on the local machine, the "active account" is used
    for authentication. Run 'gcloud auth list' to see which accounts are
    installed on your local machine and 'gcloud config list account' to view
    the active account.

  * To configure the proxy using a service account, pass the -credential_file
    flag or set the GOOGLE_APPLICATION_CREDENTIALS environment variable. This
    will override gcloud or GCE credentials (if they exist).

Google Cloud SQL Proxy on Same Machine as MySQL

As a web developer, it makes sense that you'd want to connect to my Google Cloud SQL instances running in Google Cloud Platform (GCP) via Cloud SQL Proxy and also have MySQL running locally on your machine. Unfortunately, the default configurations for these two systems will cause an error when you try to run the proxy: "bind: address already in use."

One clear reason to run the proxy is that it allows you to securely connect to your Cloud SQL instances with MySQL Workbench. You can get the IP (hostname) and port from GCP no problem, but to connect, you need to be running Google's proxy. (You also need to create a JSON credential file from GCP and add your IP to the Authorized Networks BTW.)

The reason for the error is that both the proxy and the local default install of MySQL will try to communicate on port 3306. When you try to start the proxy, this is the result:

doom:project cawood$ ./cloud_sql_proxy -instances=instancename-9999:us-west1:instance=tcp:3306 -credential_file=CredFileName-dflkjal.json &

doom:project cawood$ using credential file for authentication; email=test@test.999999.iam.gserviceaccount.com
2017/06/05 23:51:24 listen tcp 127.0.0.1:3306: bind: address already in use

To resolve this error, one solution is to create a config file for the local MySQL server and change the port.

One thing to note (as per the MySQL documentation) is that the installer for macOS will not create a config file: "Note As of MySQL 5.7.18, my-default.cnf is no longer included in or installed by distribution packages." If you don't have a config file already... to create the config file, simply run these commands.

$ cd /etc

$ sudo nano my.cnf

This is all you need in the file:

[client]

port = 3366

[mysqld]

port = 3366

Once you're done use CTRL + o to save and exit Nano.

It's worth noting, that I used to see this error before I installed MySQL locally. The reason was that there was another Cloud SQL Proxy process ("cloud_sql_proxy") already running. The solution to that is simply to kill the process.

Node.js Authentication Error Connecting to Google Cloud SQL Proxy

I was receiving this error trying to run a local instance of a Node.js API against a Google Cloud Platform (GCP) database.

Error: ER_ACCESS_DENIED_ERROR: Access denied for user ''@'cloudsqlproxy~174.7.116.43' (using password: NO) at Handshake.Sequence._packetToError (/Users/cawood/GitHub/project/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Handshake.ErrorPacket (/Users/cawood/GitHub/project/node_modules/mysql/lib/protocol/sequences/Handshake.js:103:18) at emitOne (events.js:96:13) at Socket.emit (events.js:191:7) at readableAddChunk (_stream_readable.js:178:18) at Socket.Readable.push (_stream_readable.js:136:10) 

The solution was quite straightforward, but when I first googled the error, I couldn't find anything about the access denied error showing no user--the username should be in the error (i.e. 'username'@'cloudsqlproxy).

I thought the error was the GCP Cloud SQL Proxy, but I was initiating it correctly and with a valid credential file for a service account:

$ ./cloud_sql_proxy -instances=name-111111:us-central1:instancename=tcp:0000 \ -credential_file=serviceAccountCreds.json &

The problem was actually with the environment variables for my local instance of the Node.js API. I hadn't exported them. Of course, the error was correct, I wasn't trying to connect with any user at all.

$ export MYSQL_USER="username" 
$ export MYSQL_PASSWORD="password" 
$ export MYSQL_DATABASE="test" 
$ npm start

To check if you have set these correctly, you can output them to the console when you start the server: console.log(config);

cawood$ npm start 
> projectapi@0.0.1 start /Users/cawood/GitHub/project
> node server.js 
 { user: 'username', password: 'password', database: 'test' } 
App listening on port 8000 Press Ctrl+C to quit.

Switching to Google Cloud Platform Storage

After staring down an upcoming bill for website hosting, I quickly decided to switch my website content (including most of the images on this blog) to Google Cloud Platform (GCP) storage buckets. GCP storage buckets for static websites are much cheaper and I liked the idea of having all my content being managed by Google's cloud services.

To setup static content in GCP storage buckets, follow the steps outlined in the Google Cloud article, Hosting a Static Website.

To serve content from your custom domain, you'll need FTP access to upload a file to your current hosting storage. This is how Google verifies that you own the domain.

Once you've verified your domain, you create a storage bucket named the same as your domain (e.g., www.mycustomdomainname.com) and then upload your files to the storage bucket. There are two ways to upload content, you can use the dead easy Storage Browser web interface, or you can use gsutil. gsutil is a Python app that enables command line access to GCP.

Even if you use the web UI to upload your files, you'll probably still want to use gsutil for some additional actions. For example, to set files as public, you have to go into every folder using the UI (it's labourious to say the least), but gsutil provides a simple command to accomplish the same thing extremely quickly. This one command recursively sets every file in the bucket to public read: gsutil -m acl set -R -a public-read gs://bucketname

After you've got your files uploaded and set to public, you can test the links from the Storage Browser and then your bucket is ready to go. The last step is to go to your domain registrar's site and add a CNAME entry for GCP storage. For example, www.mydomainname.com points to c.storage.googleapis.com. After adding the alias, you can verify it's working by using the free CNAME lookup tool on http://mxtoolbox.com.

That's it! You're now saving money and your files are being managed by someone else. Very nice.

Note that if you're moving from Windows hosting to GCP, you might be switching from an IIS web server on Windows to a Linux backend. This means that your new Linux driven URLs will be case sensitive. This actually broke some of my image links because I hadn't matched the case of the file path exactly in the HTML and IIS doesn't care about such details.

Update: I saved over $120 by not renewing my hosting plan and my first Google Cloud Platform monthly bill was 0.08c.