Bug 36766 - Add command-line utility to SFTP a file to a remote server
Summary: Add command-line utility to SFTP a file to a remote server
Status: Passed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Alex Buckley
QA Contact: Kyle M Hall (khall)
URL:
Keywords:
: 25246 (view as bug list)
Depends on:
Blocks: 35761
  Show dependency treegraph
 
Reported: 2024-05-02 18:49 UTC by Alex Buckley
Modified: 2024-10-08 09:54 UTC (History)
8 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 36766: WIP Command-line utility to SFTP file to remote server (6.43 KB, patch)
2024-05-02 20:34 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 36766: Add a command-line utility to SFTP a file to a remote server (7.40 KB, patch)
2024-05-02 20:56 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 36766: Make SFTP notice more customisable (5.56 KB, patch)
2024-05-09 18:27 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 36766: Only list the file basename as the SFTP put destination (1.22 KB, patch)
2024-05-17 02:19 UTC, Alex Buckley
Details | Diff | Splinter Review
Bug 36766: Add a command-line utility to SFTP a file to a remote server (7.40 KB, patch)
2024-09-27 18:34 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 36766: Make SFTP notice more customisable (5.56 KB, patch)
2024-09-27 18:34 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 36766: Only list the file basename as the SFTP put destination (1.27 KB, patch)
2024-09-27 18:34 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 36766: (QA follow-up) Tidy and use die_on_error over return (6.53 KB, patch)
2024-09-27 18:34 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 36766: Add a command-line utility to SFTP a file to a remote server (7.44 KB, patch)
2024-09-27 19:40 UTC, David Nind
Details | Diff | Splinter Review
Bug 36766: Make SFTP notice more customisable (5.60 KB, patch)
2024-09-27 19:40 UTC, David Nind
Details | Diff | Splinter Review
Bug 36766: Only list the file basename as the SFTP put destination (1.32 KB, patch)
2024-09-27 19:40 UTC, David Nind
Details | Diff | Splinter Review
Bug 36766: (QA follow-up) Tidy and use die_on_error over return (6.57 KB, patch)
2024-09-27 19:40 UTC, David Nind
Details | Diff | Splinter Review
Bug 36766: Add a command-line utility to SFTP a file to a remote server (7.51 KB, patch)
2024-09-27 20:26 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 36766: Make SFTP notice more customisable (5.68 KB, patch)
2024-09-27 20:26 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 36766: Only list the file basename as the SFTP put destination (1.39 KB, patch)
2024-09-27 20:26 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 36766: (QA follow-up) Tidy and use die_on_error over return (6.65 KB, patch)
2024-09-27 20:26 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 36766: (QA follow-up) Add single dash paramter options (2.67 KB, patch)
2024-09-27 20:26 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 36766: (QA follow-up) Pass more variable to the template (1.48 KB, patch)
2024-09-27 20:27 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Buckley 2024-05-02 18:49:27 UTC
In New Zealand libraries need to be able to SFTP MARC files to a remote server to keep the Te Puna union catalogue up-to-date.

It would be nice if NZ libraries could automate sending their MARC files in the following way:

1) Use the runreport.pl cronjob to generate a list of bibliographic numbers.
2) Use export_records.pl cronjob to generate the MARC file for those numbers.
3) Use a new cronjob to SFTP the MARC file to the remote server.
Comment 1 Alex Buckley 2024-05-02 20:34:00 UTC
Created attachment 166085 [details] [review]
Bug 36766: WIP Command-line utility to SFTP file to remote server
Comment 2 Alex Buckley 2024-05-02 20:56:48 UTC
Created attachment 166089 [details] [review]
Bug 36766: Add a command-line utility to SFTP a file to a remote server

To test you need to have a remote server you are authorised to SFTP files to.

Test plan:
1. Apply patch and restart services

2. Generate a file on your Koha server. Place it in /tmp/test.mrc

3. Manually connect to the SFTP remote host and accept the hostkey

4. Enter Koha shell

5. Run the sftp_file.pl command-line script
./sftp_file.pl --host <your remote hostname> --user <your user> --pass <your password> --upload_dir <your upload directory> --file /tmp/test.mrc

6. Confirm the SFTP was successful

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Comment 3 David Cook 2024-05-02 23:55:11 UTC
It looks like the email subject and body are hardcoded for OCLC. You'll want to make that more configurable I reckon.

In theory, I'm not sure why you wouldn't just use a utility like lftp, but if using "koha-foreach", it could be nice to pick out things like the database name and admin email address I suppose.
Comment 4 David Cook 2024-05-02 23:55:51 UTC
(In reply to David Cook from comment #3)
> In theory, I'm not sure why you wouldn't just use a utility like lftp, but
> if using "koha-foreach", it could be nice to pick out things like the
> database name and admin email address I suppose.

Although then I wonder sometimes if we should use koha-foreach to provide a bit more per-instance information to make it easier to write custom scripts that are easier to koha-foreach...
Comment 5 Alex Buckley 2024-05-09 18:27:41 UTC
Created attachment 166471 [details] [review]
Bug 36766: Make SFTP notice more customisable

SFTP_FAILURE and SFTP_SUCCESSFUL notices can be configured from Notices
and slip tool.

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Comment 6 Alex Buckley 2024-05-09 18:35:04 UTC
(In reply to David Cook from comment #3)
> It looks like the email subject and body are hardcoded for OCLC. You'll want
> to make that more configurable I reckon.
> 
> In theory, I'm not sure why you wouldn't just use a utility like lftp, but
> if using "koha-foreach", it could be nice to pick out things like the
> database name and admin email address I suppose.

Hi David, 

Thanks for your feedback.

I've replaced the email with two configurable notices SFTP_FAILURE and SFTP_SUCCESS , which can be configured from the Notices and slips tool. 

Koha/Transport/Edifact.pm does the SFTP the same way we are in our newly added file so that is why we took that same approach.

Thanks,
Alex
Comment 7 Alex Buckley 2024-05-17 02:19:10 UTC
Created attachment 166859 [details] [review]
Bug 36766: Only list the file basename as the SFTP put destination

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Comment 8 David Nind 2024-05-28 00:17:55 UTC
I'd like to test, any tips on setting up configuring an SFTP server to run in KTD? 

(I looked for some tutorials, but couldn't find anything that made sense to me.)
Comment 9 David Cook 2024-05-28 00:34:25 UTC
(In reply to David Nind from comment #8)
> I'd like to test, any tips on setting up configuring an SFTP server to run
> in KTD? 
> 
> (I looked for some tutorials, but couldn't find anything that made sense to
> me.)

Take a look at the Docker hub entry for "atmoz/sftp". You should be able to add something like the following to your docker-compose.yml:

    sftp:
        image: atmoz/sftp
        command: USERNAME:PASSWORD:::upload
        networks:
            - kohanet

I've used this image and config for other non-Koha projects before when I was testing SFTP clients, and it worked well.
Comment 10 David Nind 2024-06-03 21:39:11 UTC
Thanks for the suggestion and configuration David!

I can get it to run and start up fine.

However, I'm at a loss/none the wiser after reading the readme, on how to connect to the SFTP server from within the Koha container. Any tips would be appreciated!

For example:
1. How to connect to the SFTP server. (Do I need to install an SFTP client on the Koha container?)
2. What username and password to use, or how to set one up.
Comment 11 Nick Clemens (kidclamp) 2024-09-27 18:34:27 UTC
Created attachment 172151 [details] [review]
Bug 36766: Add a command-line utility to SFTP a file to a remote server

To test you need to have a remote server you are authorised to SFTP files to.

Test plan:
1. Apply patch and restart services

2. Generate a file on your Koha server. Place it in /tmp/test.mrc

3. Manually connect to the SFTP remote host and accept the hostkey

4. Enter Koha shell

5. Run the sftp_file.pl command-line script
./sftp_file.pl --host <your remote hostname> --user <your user> --pass <your password> --upload_dir <your upload directory> --file /tmp/test.mrc

6. Confirm the SFTP was successful

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Comment 12 Nick Clemens (kidclamp) 2024-09-27 18:34:30 UTC
Created attachment 172152 [details] [review]
Bug 36766: Make SFTP notice more customisable

SFTP_FAILURE and SFTP_SUCCESSFUL notices can be configured from Notices
and slip tool.

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Comment 13 Nick Clemens (kidclamp) 2024-09-27 18:34:32 UTC
Created attachment 172153 [details] [review]
Bug 36766: Only list the file basename as the SFTP put destination

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 14 Nick Clemens (kidclamp) 2024-09-27 18:34:34 UTC
Created attachment 172154 [details] [review]
Bug 36766: (QA follow-up) Tidy and use die_on_error over return

Tidy and add exec to atomic update
Fix POD

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 15 Nick Clemens (kidclamp) 2024-09-27 18:37:02 UTC
(In reply to David Nind from comment #10)
> Thanks for the suggestion and configuration David!
> 
> I can get it to run and start up fine.
> 
> However, I'm at a loss/none the wiser after reading the readme, on how to
> connect to the SFTP server from within the Koha container. Any tips would be
> appreciated!
> 
> For example:
> 1. How to connect to the SFTP server. (Do I need to install an SFTP client
> on the Koha container?)
> 2. What username and password to use, or how to set one up.

1 -
you can sftp from the command line:
sftp user@sftp

2 - 
The username and password are set in the command line in David's example, so if you did 
command: foo:bar:::upload
the user would be foo and the password bar

You should add the lines to docker-compose-light.yml
Comment 16 Nick Clemens (kidclamp) 2024-09-27 18:40:32 UTC
It would be nice to add failures for things like changing directory or bad email to the email sent by this - so that user's can be notified, but as this is new I think it is enough to move forward

>In theory, I'm not sure why you wouldn't just use a utility like lftp, but if >using "koha-foreach", it could be nice to pick out things like the database >name and admin email address I suppose.

PTFS is looking into adding SFTP configs to the interface (more generalized than the config in EDI) so this script would be nice to have and could then read configs from the DB given an ID or a name, and then we can encrypt passwords etc. I think this is a good base

If David (either one) signs off, then mine can be considered a QA stamp
Comment 17 David Nind 2024-09-27 19:40:07 UTC
Created attachment 172161 [details] [review]
Bug 36766: Add a command-line utility to SFTP a file to a remote server

To test you need to have a remote server you are authorised to SFTP files to.

Test plan:
1. Apply patch and restart services

2. Generate a file on your Koha server. Place it in /tmp/test.mrc

3. Manually connect to the SFTP remote host and accept the hostkey

4. Enter Koha shell

5. Run the sftp_file.pl command-line script
./sftp_file.pl --host <your remote hostname> --user <your user> --pass <your password> --upload_dir <your upload directory> --file /tmp/test.mrc

6. Confirm the SFTP was successful

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: David Nind <david@davidnind.com>
Comment 18 David Nind 2024-09-27 19:40:10 UTC
Created attachment 172162 [details] [review]
Bug 36766: Make SFTP notice more customisable

SFTP_FAILURE and SFTP_SUCCESSFUL notices can be configured from Notices
and slip tool.

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: David Nind <david@davidnind.com>
Comment 19 David Nind 2024-09-27 19:40:13 UTC
Created attachment 172163 [details] [review]
Bug 36766: Only list the file basename as the SFTP put destination

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: David Nind <david@davidnind.com>
Comment 20 David Nind 2024-09-27 19:40:15 UTC
Created attachment 172164 [details] [review]
Bug 36766: (QA follow-up) Tidy and use die_on_error over return

Tidy and add exec to atomic update
Fix POD

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: David Nind <david@davidnind.com>
Comment 21 David Nind 2024-09-27 19:43:30 UTC
(In reply to Nick Clemens (kidclamp) from comment #15)
....
> 
> 1 -
> you can sftp from the command line:
> sftp user@sftp
> 
> 2 - 
> The username and password are set in the command line in David's example, so
> if you did 
> command: foo:bar:::upload
> the user would be foo and the password bar
> 
> You should add the lines to docker-compose-light.yml

Thanks Nick, that really helped!

I've added my sign-off, so I'll let you change the status to passed QA.

My testing notes (using KTD):

1. Set up a Docker-based SFTP server:
   1.1 Add this information to koha-testing-docker/docker-compose-light.yml (near the end, before networks:):

    sftp:
        image: atmoz/sftp
        command: koha:koha:::upload
        networks:
            - kohanet

    1.2 ktd up (should pull the sftp Docker image).

2. Apply the patch and restart services.

3. Generate a file on your Koha server. Place it in /tmp/test.mrc
   3.1 misc/export_records.pl --starting_biblionumber=261 --ending_biblionumber=262 --filename=/tmp/test.mrc

4. Manually connect to the SFTP remote host and accept the hostkey, and also check that you can successfully connect to the SFTP server:
   4.1 sftp koha@sftp

5. Run the sftp_file.pl command-line script:
   5.1 ./misc/cronjobs/sftp_file.pl --host sftp --user koha --pass koha --upload_dir upload --file /tmp/test.mrc

6. Confirm the SFTP was successful:
   6.1 Access the SFTP server: sftp koha@sftp
   6.2 List the files in the upload directory: ls -la upload
   6.3 Exit the SFTP server: exit
Comment 22 Liz Rea 2024-09-27 19:52:52 UTC
YES OMG THANK YOU.
Comment 23 Kyle M Hall (khall) 2024-09-27 20:26:44 UTC
Created attachment 172176 [details] [review]
Bug 36766: Add a command-line utility to SFTP a file to a remote server

To test you need to have a remote server you are authorised to SFTP files to.

Test plan:
1. Apply patch and restart services

2. Generate a file on your Koha server. Place it in /tmp/test.mrc

3. Manually connect to the SFTP remote host and accept the hostkey

4. Enter Koha shell

5. Run the sftp_file.pl command-line script
./sftp_file.pl --host <your remote hostname> --user <your user> --pass <your password> --upload_dir <your upload directory> --file /tmp/test.mrc

6. Confirm the SFTP was successful

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 24 Kyle M Hall (khall) 2024-09-27 20:26:52 UTC
Created attachment 172177 [details] [review]
Bug 36766: Make SFTP notice more customisable

SFTP_FAILURE and SFTP_SUCCESSFUL notices can be configured from Notices
and slip tool.

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 25 Kyle M Hall (khall) 2024-09-27 20:26:54 UTC
Created attachment 172178 [details] [review]
Bug 36766: Only list the file basename as the SFTP put destination

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 26 Kyle M Hall (khall) 2024-09-27 20:26:56 UTC
Created attachment 172179 [details] [review]
Bug 36766: (QA follow-up) Tidy and use die_on_error over return

Tidy and add exec to atomic update
Fix POD

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 27 Kyle M Hall (khall) 2024-09-27 20:26:59 UTC
Created attachment 172180 [details] [review]
Bug 36766: (QA follow-up) Add single dash paramter options

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 28 Kyle M Hall (khall) 2024-09-27 20:27:01 UTC
Created attachment 172181 [details] [review]
Bug 36766: (QA follow-up) Pass more variable to the template

Libraries may want to use this for more varied purposes and
adjust the language of the email based on the sftp upload
that succeeded or failed.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 29 Martin Renvoize (ashimema) 2024-10-08 09:54:40 UTC
*** Bug 25246 has been marked as a duplicate of this bug. ***