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

Issue

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??

Solution

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.

Answered By – Martin Zeitler

Answer Checked By – Timothy Miller (BugsFixing Admin)