Connect Pigment with an SFTP server

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.

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.

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  encryptions agorythm and key format :

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

Here an example of how to create an RSA 4096bits key pair in OpenSSL PEM format which we support

ssh-keygen -t rsa -b 4069 -m pem

Alternatively if openSSH format is required, then you can use the ED25519 encryption and generate the keys with this command

ssh-keygen -t ed25519 -noencrypt

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.

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.

IP allowlisting

Pigment is not associating a domain name with the IP addresses of the outbound NAT gateways in front of its private infrastructure.

The IP addresses of the outbound gateways are:

• 35.242.251.111
• 34.145.54.113

These are the IPs you need to whitelist if you want to allow incoming SFTP traffic from Pigment’s infrastructure.