Python database connector with SSL

Anvil Q&A
#1

With the Individual plan I would like to confirm that I will be able to use a Python database connector such as mysql or psycopg2 along with SSL for connectivity to Amazon RDS. I believe in either case I will need to store the Amazon server certificate, so is there a resource object in Anvil that can be accessed by the Python connector?

#2

Hi there,

Short answer: yes!

Longer answer: With the Full Python runtimes included in the Individual Plan, you have access to a large number of Python modules, including psycopg2, pymysql, boto3 (the AWS API) and many more. We have people using external databases such as RDS from Anvil no problem.

You can find a full list of packages installed at https://anvil.works/doc/#python_packages - if there is something you need pip installing and you’re on the Individual plan or higher, drop us an email at support@anvil.works and we’ll usually get it installed within a day or so.

#3

When using psycopg2 over SSL, in order to verify the CA I need to set the sslrootcert to a file path. Is there a place where I can provide the PEM file such that I can refer to it with a file system path from a server module?

#4

The filesystem in your server code is ephemeral. What I’d suggest is putting the file into a Data Table as a Media object, then writing it to a file. (In fact, the filesystem isn’t swept every time, so you only have to write it if it’s not there). Something like:

  if not os.path.isfile('/tmp/rootcert.pem'):
    pemdata = app_tables.cert.get()['data'].get_bytes()
    with open('/tmp/rootcert.pem', 'w') as f:
      f.write(pemdata)
3 Likes
#5

Would adding the file as an asset work?
And be simpler?

#6

Thanks, I can confirm this works with psycopg2 and sslmode='verify-full'.

#7

I got it working but I had to make a few changes. I needed to pass parameters to a dependency app, so I stored the certs in the main app and passed them as parameters to a function in the dependency app (as media objects).

I then modified @meredydd’s code slightly to this :

    if "sslcert" in kwargs:
      if not os.path.isfile('/tmp/ca.crt'):
        with open('/tmp/ca.crt','w') as f:
          f.write(kwargs['sslcert'].get_bytes().decode())
        
    if "sslclientcert" in kwargs:
      if not os.path.isfile('/tmp/client.crt'):
        with open('/tmp/client.crt','w') as f:
          f.write(kwargs['sslclientcert'].get_bytes().decode())

    if "sslclientkey" in kwargs:
      if not os.path.isfile('/tmp/client.key'):
        with open('/tmp/client.key', 'w') as f:
          f.write(kwargs['sslclientkey'].get_bytes().decode())

        os.chmod("/tmp/client.key",0o600)

Note the client key - I needed to change the permissions to “0o600” to remove the errror :
Permissions should be u=rw (0600) or less.

I’m still feeling my way with this …

1 Like
#8

I just wanted to update this slightly to demonstrate slightly better practice.

In the example above I’m clearly storing the certs as a media object in the data tables (as shown by the fact I’m doing a get_bytes()).

Don’t do that. Instead store them as a secret, then they are encrypted at rest.

1 Like