Connect Pigment with an SFTP server

  • 1 September 2022
  • 0 replies
Connect Pigment with an SFTP server
Userlevel 6
Badge +8

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.





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.



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

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



  • RSA keys in on OpenSSH format are not supported
  • Keys must have No Passphrase


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.


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.




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.


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:



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

This topic has been closed for comments