Unable to access BigQuery from local App Engine development server

Question

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?

Answer 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.

Answer 2

Is it possible to get the App Engine local development server to authenticate with the remote BigQuery service?

I think it's impossible to use AppAssertionCredentials as authentication method between BigQuery service and your local App Engine server currently.

Alternatively, I'm using OAuth2 authentication which is associated with specific user(this user must be registered in your project at google api console) to access BigQuery from local App Engine server.

For getting user OAuth2 authentication, I use oauth2client.client module in the app code.

I hope this will be helpful to your problem.

Updated:

This is what I'm doing for getting the user OAuth2 authorization.

Edited:

Added missing import statement. Thanks mattes!

import os
import webapp2
import httplib2
from oauth2client.client import OAuth2Credentials
from oauth2client.appengine import StorageByKeyName, CredentialsModel, OAuth2DecoratorFromClientSecrets
from google.appengine.api import users

oauth2_decorator = OAuth2DecoratorFromClientSecrets(
    os.path.join(os.path.dirname(__file__), 'client_secrets.json'),
    scope='https://www.googleapis.com/auth/bigquery')
oauth2_decorator._kwargs = {'approval_prompt': 'force'}


class TestPage(webapp2.RequestHandler):
  @oauth2_decorator.oauth_required
  def get(self):
    user_id = users.get_current_user().user_id()
    credentials = StorageByKeyName(CredentialsModel, user_id, 'credentials').locked_get()
    http = credentials.authorize(httplib2.Http()) # now you can use this http object to access BigQuery service


application = webapp2.WSGIApplication([
  ('/', TestPage),
  (oauth2_decorator.callback_path, oauth2_decorator.callback_handler()),
], debug=True)

Answer 3

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.

Answer 4

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

Answer 5

I agree with the first post - the localhost/production impedance is a real pain in the a**. AppAssertionCredentials is the right way to go on production and I don't want to have two different code paths between production and localhost. So the development environments need to be adjusted to be able to perform the required authentication without affecting the main code path.

E.g., perhaps a developer could log in with their own Google account using appcfg.py and then that auth would be cached for a period such that AppAssertionCredentials would work out. The developer's Google account could be granted permissions on the appropriate environments (dev and test for us, e.g.)

re: "local BigQuery" - we have some initial stuff in place that uses SQLLite to simulate BigQuery interactions for unit tests and other offline/local testing, but of course, it's not a great simulation. I agree that all the Cloud Platform products need to spend as much time thinking about the development-time experience as App Engine has.