Connect Pigment with an SFTP server

  • 1 September 2022
  • 2 replies
  • 106 views
Connect Pigment with an SFTP server
Userlevel 5
Badge +3

This article explains how to connect an SFTP server with your Pigment Organization to load any csv files stored in that server into a Pigment’s block.

 

 

Overview

 

This process starts with the configuration to access an SFTP server. You’ll grant access to a user account, and generate an SSH key pair to authenticate the SFTP user Pigment uses to connect to the server. Then, on Pigment side, you’ll create a Connection, by adding the server configuration parameters, as well as the user’s private SSH key. Once set up, the SFTP connector has the added benefit of being able to choose from a fixed or most recent file.

 

Prerequisites

You must have read-access to an SFTP server containing the data you want to load into Pigment.

 

Setting up access 

 

SFTP Step 1 - Creating an SFTP account

 

Pigment connects to the target server with a user account identified by an SSH key pair. For security reasons, we recommend that:

  • You create an account dedicated to Pigment imports, where you'll only upload data Pigment is meant to have access to.
  • This account should have read-only access; meaning the data is uploaded to the server by a distinct actor.

The details on how you create the account very much depend on your particular case; IT policy, SFTP server implementation in use, or several other factors.

Please refer to your provider or administrator who will provide the specific setup.

 

SFTP Step 2 - Generating an SSH key pair 

 

For security reasons, we suggest that you create an SSH key pair specifically dedicated to authenticating this user, and do not reuse these keys on other server locations.

The SSH private key will be needed in the following steps: you provide it to Pigment in the integration configuration, as the means by which this user account authenticates. The SSH public key must be transferred to the server end for authentication on that side.

We support the following private key formats:

  • RSA in OpenSSL PEM and ssh.com format
  • DSA in OpenSSL PEM and ssh.com format
  • ECDSA 256/384/521 in OpenSSL PEM format
  • ECDSA 256/384/521, ED25519 and RSA in OpenSSH key format

 

SFTP Step 3 - Get the server SSH host key fingerprint

 

The SSH host key fingerprint of the target server must be provided to Pigment in the integration configuration, as a means to secure the connection against man-in-the-middle attacks.

 

This fingerprint can be obtained on the server side, by using the ssh-keygen command.

Example:

ssh-keygen -l -f /etc/ssh/ssh_host_ed25519_key

 

It could also be obtained on the client side, by combining ssh-keyscan and ssh-keygen.

ssh-keyscan -p $port $host | ssh-keygen -lf -

The expected format is SHA256-base64.

Example:

SHA256:AVf87SpGS622J6Iqv6F79U/y7LMTkSE5N37bRzPw2ek

 

Establishing connection in Pigment 

 

Go to the integration page from the Settings tab.

Next, click on + Add next to the SFTP integration and fill out the form with the following information.

You will need to establish a connection for each individual SFTP location.

 

Name: give a name to your connection

Application access: Select the applications which will be allowed to use this connection

Host name or address: name, or IP address, of the SFTP server

Port: the TCP port on which the server listens to SFTP connections

User login: the username that Pigment will use to connect to the server

User private key: Copy and paste here the whole content of your private key file

Server fingerprint: the fingerprint of the server, in SHA256-base64 format Key creation (Step 2)

 

 

Use the connection in Pigment 

 

Once the SFTP connection is configured, open an Application that the connection is available on and open the “Import Data” interface for the object you want to import data into. 

For example, if you were importing data into a Transactions, you would open up list and then select Import and Download and then Import .

Instead of “Upload file”, select the Integration option.  From here, select your SFTP connection.

 

Select the Import mode you want.

 

Fixed file

This import mode will choose one individual file name.

Just input the file name and location (ex: folder1/folder2/file.csv) and click on Import.

The file should load in Pigment within a few seconds!  If this configuration is saved, it will only upload that file name.  

 

Most recent file

 

This import mode allows you to define a path and Pigment will upload the most recent file depending on a defined naming convention that must contain a ISO-8601 compatible date format within it. 

 

Naming convention 

When naming your files on the SFTP server, your file name must follow a consistent naming convention with the date included.  Here are some examples of file names for a rev.csv with the ISO-8601 compatible date formats located at the start.

 

ISO-8601 compatible date formats example of file names {{date}}-rev.csv naming convention
yyyy-MM-dd 2022-09-30-rev.csv
yyyy-MM-ddThh:mm:ss 2022-09-30T00:00:00-rev.csv
yyyy-MM-ddThh:mm:ssZ 2022-09-30T00:00:00Z-rev.csv

 

When writing the file path in Pigment

 

Similar to the Fixed file, you must first define the path with the location(ex: folder1/folder2/ ) ,then you must define file naming convention with a date included in the file name.  When entering the case sensitive path in Pigment, use {{date}} where the date is located. 

It should look something like this  folder1/folder2/file-{{date}}.csv

 

Here are some examples of file names and the results depending on what path you entered.

If the bucket contains the following files:

  • /myfolderA/2022-09-15-salaries.csv
  • /myfolderA/2022-10-15-salaries.csv
  • /myfolderA/2022-10-16-revenue.csv
  • /myfolderB/2022-11-15-salaries.csv

Here are the results I would get depending on my file path :

  • path "myfolderA/{{date}}-salaries.csv" will import /myfolderA/2022-10-15-salaries.csv because this is the most recent file in myfolderA.
  • path "myfolderB/{{date}}-salaries.csv" will import /myfolderB/2022-11-15-salaries.csv because this is the most recent file in myfolderB.
  • path "/{{date}}-salaries.csv" will import nothing because its missing the folder location.

 


This topic has been closed for comments

2 replies

Badge

This is great! Will it be long before we can send files from Pigment into SFTP via a native integration?

Userlevel 5
Badge +3

Hello @agnar ,

Automated export to SFTP is a feature candidate we have a mind but which is not yet prioritized.
I would suggest you to create an Idea in the Ideas section to collect clients votes and help us prioritizing it.