How to connect to Google Cloud SQL with enforced SSL from within App Engine app

Question

I would like to have SSL enforced when connecting to my Google Cloud SQL (postgres) instance, but I can't find a way to provide a certificate to my Python app deployed to App Engine - psycopg2 requires the certificate to have a proper rights: <cert> has group or world access; permissions should be u=rw(0600) or less, but since there's no access to chmod on App Engine, I don't know how to change it.

Any ideas?

To provide a context, the goal is primarily to enforce secure access to the DB outside of GCP (from my workstation). The problem with App Engine apps is a side-effect of this change. But maybe there's a different and better way to achieve the same?

Thanks.

Answer 1

Google Cloud App Engine uses the Cloud SQL proxy for any connections using the build in /cloudsql unix socket directory. These connections are automatically wrapped in an SSL layer until they reach you Cloud SQL instance.

You don't need to worry about using certificates, as the proxy automates this process for you.

Answer 2

This is a bit tricky because there is a chicken-and-egg problem here:

• The Postgres driver wants a file (because it's a wrapper around a C library that reads the file)
• And you cannot set the correct file permission inside app engine on boot.

There are several cases (and solutions) for this problem:

• AppEngine Classic environment
  • Workaround - write the cert file yourself in the /tmp ramdisk
  • Use a UNIX socket to connect to a Cloud SQL instance with public IP address (no SSL).
  • Or use a VPC and private VPC access for a cloud SQL instance with private IP address and use the workaround.
• AppEngine Flex Environment
  • Include the cert file in your docker image

Workaround: Write the cert file yourself in /tmp

The easiest way to work around this problem is to write the cert file yourself into /tmp - this is a ramdisk in the app engine environment.

Something like this would do the trick:

import os
import psycopg2

certfile_path = '/tmp/certfile' # /tmp/ is a ramdisk
certfile_content = os.environ.get('CERTFILE')

if certfile_content != None:
   with open(certfile_path,'w+') as fp:
      fp.write(certfile_content)
   os.chmod(certfile_path, 0o600)
   # Now the certfile is "safe" to use, we can pass it in our connection string:
   connection_string= f"postgres+prycopg2://host/database?sslmode=require&sslcert={certfile_path}"

else:
   connection_string = 'postgres+prycopg2://host/database'
   # or just trow an exception, log the problem, and exit.

(See Postgres connection string reference for SSL options)

Long term solution

The point of using SSL is to have both encrypted traffic between the database and you, plus avoiding man-in-the-middle attacks.

We have some options here:

• Option 1: When you're inside App Engine AND your cloud SQL instance has a public IP address.

  In this case, you can connect to a Postgres instance with a Unix domain socket. Google uses a proxy listening on that socket and will take care of encryption and securing the endpoints (btw - SSL is disabled).

  In this case, you'll need to add SQL Client permissions to the service account you use AND add the UNIX socket directory in your Yaml file (see Google documentation).

• Option 2: When you're inside App Engine Classic AND your cloud SQL instance has a PRIVATE IP address.

  If your cloud SQL instance has a private IP, this can be a bit "simpler". We need to enable Serverless VPC Access - this connects AppEngine to the VPC where we have resources on private IP addresses (for public IPs it not needed because public IP addresses don't overlap) AND the Cloud SQL server must be connected to the VPC.

  Now you can connect to Postgres as usual. The traffic inside the VPC network is encrypted. If you have just this CloudSQL instance in the VPC there is no need to use SSL (nobody can put a sniffer on the network or do a Mitm attack).

  But if you still want/need to use SSL, you have to use the workaround described before.

• Option 3: Use AppEngine FLEX environment.

  The flex environment is 'just' a docker image. You'll need to build a custom docker image for the Python runtime that includes the certfile. Remember the Flex environment is not included in the free tier.