Send CSV from Google bucket to SFTP server (With RSA) using GCP Cloud function

Question

I am trying to send a csv file to a SFTP server using a Google Cloud Function.

This is the Python script I am using -

import base64
import os
import pysftp
import re
import csv
from google.cloud import storage
from google.cloud import bigquery

def hello_sftp(event, context):
    
    #defining credentials for the transfer
    myHostName = 'HostName'
    myUsername = 'TestUser'
    myPassword = 'RSA' // I don't have any password, instead I need to user a RSA key
    filename = 'file.csv' // This is my file Name
    path = "gs://mytestbucket/" // Here is my csv file stored

    copy_file_on_ftp(myHostName, myUsername, myPassword, filename, path)
   
def copy_file_on_ftp(myHostName, myUsername, myPassword, filename, localpath):
    
    remotepath = '/Export/' + str(filename)
    print(' ')
    print(localpath)
    print(' ')
    cnopts = pysftp.CnOpts()
    cnopts.hostkeys = None
    
    with pysftp.Connection(
    host=myHostName, username=myUsername, password=myPassword, cnopts=cnopts
    ) as sftp:
        print("Connection successfully established . . . ")
        print("Exporting . . . ")
        print("local path and file name is : ", localpath+filename)
        sftp.put(localpath =localpath+filename, remotepath=remotepath)
    sftp.close()
    print("export to sFTP successful!")

From the Script you can see that I don't have any Password to be able to connect to the SFTP SERVER, instead I have an RSA KEY.

Now my question is how can I put the RSA key into this Script? Does anyone know??

Martin Zeitler · Accepted Answer · 2022-01-24T01:23:37.907

The problem may be, that you're using a Python; it's easier with gcloud compute scp, because one can provision keys on demand and buckets are accessible, too. The sequence of commands is:

gcloud config set ...
gcloud compute config-ssh
gcloud compute scp ...
gcloud compute ssh ...

For example; and this explains how to manage the keys (not store them at all).

While I wonder why you don't just fetch the CSV from bucket, on that server? Such situations are always push vs. pull. This may make sense, but not always. Choosing the wrong environment or building a solution more complex than required, this only adds handicap - and "Send CSV from Google bucket to SFTP server" doesn't permit to determine the actual purpose of doing that.

score 1 · Answer 2 · answered Jan 24 '22 at 00:04

Leaving aside everything except where to store a private key, I would suggest to store all secrets (for example, private key, pass phrase, etc) in a Secret Manager. The cloud function should get all those values using the Secret Manager API, and use them to establish a SFTP connection.

Send CSV from Google bucket to SFTP server (With RSA) using GCP Cloud function

2 Answers2