Pages

Sunday, May 31, 2009

Change Peoplesoft FTP to SFTP - Part 1

Many workplaces are implementing a higher level of secured data transfer requirements due to increased security concerns. The PS delivered file transfer process in v8.49 currently does not support SFTP however. These are the steps I employed to make the switch. This article applies to the file transfer from a client desktop to a FTP server using the delivered ftpunx shell script.

Firstly a note on the flow of Peoplesoft's File Attachment process. In a 4-tier environment, a file transfer request initiates from the client browser. This file data goes to the Web server using HTTP or HTTPS protocal. Web Server then sends it to App Sever through Jolt. It is in the final leg of the relay, from App Server to destination FTP Sever, that FTP/SFTP is employeed. When viewing a file, the above steps are reversed.

The Peoplecode function that transfers a file is AddAttachment. There are multiple pages that uses multiple ways to call this function, some directly and some can go through layers of Application Package code, but they ultimately call the same function/

A look into the behind the scene actions when adding an file attachment -
1. generating a unique temp file name, such as:

&UniqueName = .OPRID | "_" | String(Month(%Date)) | "_" | String(Day(%Date)) | "_" | String(Year(%Date)) | "_" | String(Hour(%Time)) | "_" | String(Minute(%Time)) | "_" | String(Second(%Time));

The name can vary slightly among different load pages/processes. They are in the general format of Date Time stamp and OPRID.

2. When user hit Attach button, an entry is inserted into PSFILE_ATTDET:




  • This row will stay in the table as a tag. FILE_SEQ is 0, FILE_SIZE is 5 bytes, indicating the FILE_DATA size. (value is HEX 00 64 00 75 00 on my system)
  • ATTACHSYSFILENAME is a unique file name that will be used to store file data as it loads in chunks of 28K indexed by FILE_SEQ. If no file is selected to load, this row is not removed.
  • All the rows (one per chunk) used during file load are deleted after successfully reassembled on App Server, and before actual FTP runs.

3. The Upload box displays after the above steps:




4. When user selects a file and hit "Upload", internally PS run a FileAttachService API that performs these tasks:
  • the tag row in PSFILE_ATTDET is cleared
  • get MAXCHUNKSIZE from PSOPTIONS. Default is 28K.
  • breaking down file data into chunks of MAXCHUNKSIZE and insert each segment, identified by FILE_SEQ, into PSFILE_ATTDET


(the last segment size is smaller because it contains only the remainder of file. so the file size = MAX(FILE_SEQ) - 1 * MAXCHUNKSIZE + FILE_SIZE of final segment)

note: the API is not directly accessible from Peoplecode. PS runs them at appropriate junction to perform special purpose internal tasks.

Now that the data has been saved onto a table on Database server. Next PS will put the data back into a file on App Server.
5. The function AddAttachment is called after the above has completed. PS runs internal APIs ExecuteGetAttachmentDB & ExecuteDeleteAttachmentDB:
  • reassembles the data from table back into a file, in the order of FILE_SEQ, the location for the temp file is /tmp/PSFTP/_?????/????
  • delete data from PSFILE_ATTDET
Now the file is on App Server, ready for FTP to its destination. When viewing a file, the above steps are reversed. The file is FTP'ed to App Server, then Jolt'ed to Web Server, then HTTP'ed to client browser.

6. Still in AddAttachment, the API ExecutePutAttachment performs the most important tasks of the process:
  • parse FTP URL (ftp://UID:PWD@FTP_Dest_Server/xxx/xxx/xxx/_
    _?????.ext) to build a series of FTP commands (open, user, cd, lcd, get/put/delete...) into a temp script in /tmp/ftp???_????.txt
  • verify the FTP script exists
  • run FTP using the FTP script
  • check FTP results in /tmp/ftp?????_?????_?????.log
  • delete FTP script, preserve log if needed
This is a very critical step because of the sensitive data involved. The ftp???.txt contains all FTP commands needed to perform the transfer. PS runs(spawn()?) the FTP wrapper, $PS_HOME/bin/ftpunx, to send the file. The output from the ftp is stored in ftp???.log & used to verify results. This is the process as being run:

/bin/sh $PS_HOME/bin/ftpunx /tmp/ftp????_????_?????.txt

A look at ftpunx:

2 things to note: (1) $FTP_CMD is the ftp executable on App server and $1 is the ftp????.txt script (2) because of the location of "result=$?", it's going to always = 0. As a result we will always see "File Transfer Successful" in the log, even in failed situations. This is misleading but not damaging, because PS does not rely on this check.

If all goes well, the file is now supposed to be FTP'ed to it's destination. How does PS verify this? It checks the resulting session message file. Before getting into this, take a look at the changes in the script to run SFTP.

Change Peoplesoft FTP to SFTP - Part 2