Unable to access BigQuery from local App Engine development server

This is specifically a question relating to server to server authorisation between a python Google AppEngine app and Google's BigQuery, but could be relevant for other cloud services.

tldr; Is it possible to get the App Engine local development server to authenticate with the remote BigQuery service? Better yet is there a local BigQuery?

I understand that AppAssertionCredentials does not currently work on the local development server, though that in itself is very frustrating.

The alternative method which works for standard python code, outside of the local development server sandbox, detailed here does not work for the local development server because even with PyCrypto enabled the sandbox does not allow some posix modules e.g. 'pwd'.

I have got AppAssertionCredentials working on the remote server and the SignedJwtAssertionCredentials method working in native python locally, so the service accounts are set up properly.

The imports fail within oauth2client/crypt.py within the try/except blocks - after commenting them out the sandbox whitelist exceptions are easily seen.

I've fiddled around with adding 'pwd' to the whitelist, then another problem crops up, so I scurried back out of that rabbit hole.

I've tried including PyCrypto directly into the project with similar results.

I've also tried with OpenSSL with similar results.

I have looked for a local appengine specific PyCrypto to no avail, have I missed one? I should say this is on Mac OSX - perhaps I should fire up a linux box and give that a go?

Solution 1:

A recent release of Google App Engine SDK added support for the AppAssertionCredentials method on the development server. To use this method locally, add the following arguments to dev_appserver.py:

$ dev_appserver.py --help
...
Application Identity:
  --appidentity_email_address APPIDENTITY_EMAIL_ADDRESS
                        email address associated with a service account that
                        has a downloadable key. May be None for no local
                        application identity. (default: None)
  --appidentity_private_key_path APPIDENTITY_PRIVATE_KEY_PATH
                        path to private key file associated with service
                        account (.pem format). Must be set if
                        appidentity_email_address is set. (default: None)

To use these:

1. In Google Developer Console, select a project then navigate to "API & auth" -> "Credentials" -> "Create new client ID".

2. Select "Service account" and follow the prompts to download the private key in PKCS12 (.p12) format. Take note of the email address for the service account.

3. Make sure you add that service account email address to the "Permissions" tab for any project that contains data it needs to access, by default it is added to the project team in which it was created.

4. Convert the PKCS12 format to PKCS1 format using the following command:

   $ cat /path/to/xxxx-privatekey.p12 | openssl pkcs12 -nodes -nocerts -passin pass:notasecret | openssl rsa > /path/to/secret.pem

5. Start dev_appserver.py as:

   $ dev_appserver.py --appidentity_email_address [email protected] --appidentity_private_key_path /path/to/secret.pem ...

6. Use appidentity module and AppAssertionCredentials in the same manner locally as you normally would in production.

Please ensure that /path/to/secret.pem is outside of your application source directory so that it is not accidentally deployed as part of your application.

Solution 2:

So searching deeper for PyCrypto and local appengine sandbox lead me onto this thread and response specifically...

https://code.google.com/p/googleappengine/issues/detail?id=1627#c22

This is fixed in 1.7.4. However, you must use easy_install -Z (--always-unzip) to install PyCrypto. The default zipfile option in OSX 10.8 is incompatible with the sandbox emulation in the dev_appserver.

The solution turns out to be very straight forward...

I used:

sudo easy_install pycrypto

and it should have been:

sudo easy_install -Z pycrypto

as per the thread above. Using PIP will work as well:

pip install pycrypto 

or a manual download and install of pycrypto will also work. I tested all three.

If you have installed pycrypto with easy_install and without -Z flag then you may want to install pip just so you can easily uninstall pycrypto...

easy_install pip

for the record I built and installed libgmp, as pil and the manual install showed this warning...

warning: GMP or MPIR library not found; Not building Crypto.PublicKey._fastmath.

Although this gave me fastmath, it was not essential to solve the problem as the Crypto libs gracefully fail to slowmath.

Another point that tripped me up for a bit was I removed pycrypto from app.yaml whilst testing to see if OpenSSL might give me all I need.

So dont forget to add...

- name: pycrypto
  version: latest

into app.yaml under the libraries: section.

With this missing the native _counter library was not imported hence Counter failed etc.

Also for the record any talk of having to move Crypto into the app folders themselves or out of the default Mac OS X location of /Library/Python/2.7/site-packages/Crypto was only valid in earlier versions of the dev server.

Similarly there is now no need to edit any _WHITE_LIST_C_MODULES lists (which is in sandbox.py in appengine 1.8 onwards, which also includes the regex which allows Crypto.Util._counter etc)

The other bit of the puzzle in case you get here before discovering the key issue is that the key file you download from the console is PKCS12 and is downloaded as hex text, so I converted that to binary and then converted that to a PEM so I could include it in the source code.

Solution 3:

I struggled with this one for a day or two. And I was finally able to get localhost working with server to server authentication, a service account and a .p12 cert.

If it's at all helpful to anyone, here's a simple gist: https://gist.github.com/dandelauro/7836962