This article explains how to connect an SFTP server to Pigment to load CSV files stored on the server into a Pigment Block.

Overview

This process begins with configuring access to 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. On the Pigment side, you'll create a connection by adding the server configuration parameters and the user's private SSH key. Once set up, the SFTP connector allows you to choose between a fixed file or the 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 a dedicated account for Pigment imports, ensuring it only contains data that Pigment should access.
• This account should have read-only access, meaning the data is uploaded to the server by a separate user or system.

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.

You will need to provide the SSH private key to Pigment in the following steps during the integration setup to allow the user account to authenticate. The SSH public key must be transferred to the server for authentication on that end.

We support the following encryptions algorithm and key format:

• RSA (OpenSSL PEM and ssh.com format)
• DSA  (OpenSSL PEM and ssh.com format)
• ECDSA 256/384/521 (OpenSSL PEM format)
• ED25519 (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 4096 -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

To secure the connection and prevent man-in-the-middle attacks, you need to provide Pigment with the SSH host key fingerprint of the target server during the integration configuration.

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 

To establish a connection in Pigment:

1. In your Workspace, go to Settings then select Integrations.
2. Select + Add next to the SFTP integration and fill out the form with the information below.

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 3) Use PGP encryption: check this box to if you want to Pigment to read PGP encrypted file.

Use the connection in Pigment 

After configuring the SFTP connection, open an Application where the connection is available and select Import Data in the Block where you want to import data.

For example, to import data into a Transactions List:

1. Open the List, select Import data, and then choose Import.
2. Instead of Upload File, select the Integration option
3. Choose your SFTP connection. 

Select the Import mode you want. You can choose between Fixed file and Most recent file.

Fixed file

This import mode will choose one individual file name.

Input the file name and location (ex: folder1/folder2/file.csv) and select Import.

The file should load into Pigment within a few seconds. If this configuration is saved, it will only upload this 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. 

File 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 of the name.

ISO-8601 compatible date formats	example of file names {{date}}-rev.csv naming convention
yyyy-MM-dd	2022-09-30-rev.csv
yyyy-MM-ddThhss	2022-09-30T00:00:00-rev.csv
yyyy-MM-ddThhssZ	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 to the file location, for example: folder1/folder2/ ). Then, set a file naming convention that includes a date in the file name. When entering the case-sensitive path in Pigment, use {{date}} where the date appears in the file name.

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

Here are some examples of file names and the results depending on which 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 you it would return depending on the 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 does not assign a domain name to the IP addresses of outbound NAT gateways in its private infrastructure.

The IP addresses of the outbound gateways are:

• 35.242.251.111
• 34.145.54.113
• 34.163.209.119
• 35.202.142.12

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