Created attachment 166085 [details] [review]
Bug 36766: WIP Command-line utility to SFTP file to remote server

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

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.

(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...

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

(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

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

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.)

(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.

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.

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

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

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>

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>

(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

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

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>

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>

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>

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>

(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

YES OMG THANK YOU.

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>

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>

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>

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>

Created attachment 172180 [details] [review]
Bug 36766: (QA follow-up) Add single dash paramter options

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

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>

*** Bug 25246 has been marked as a duplicate of this bug. ***

Please provide a follow-up:

1) In some email setups this won't work as the KohaAdminEmailAddress will be a noreply address (for anti-spam reasons)

+if ($email) {
+    if ( C4::Context->preference('KohaAdminEmailAddress') ) {
+        $admin_address = C4::Context->preference('KohaAdminEmailAddress');
+    }

Can you please make the address configurable or better prefer ReplytoDefault and fall-back to KohaAdminEmailAddress if it's not set.

Pushed for 24.11!

Well done everyone, thank you!

(In reply to Katrin Fischer from comment #30)
> Please provide a follow-up:
> 
> 1) In some email setups this won't work as the KohaAdminEmailAddress will be
> a noreply address (for anti-spam reasons)
> 
> +if ($email) {
> +    if ( C4::Context->preference('KohaAdminEmailAddress') ) {
> +        $admin_address = C4::Context->preference('KohaAdminEmailAddress');
> +    }
> 
> Can you please make the address configurable or better prefer ReplytoDefault
> and fall-back to KohaAdminEmailAddress if it's not set.

I need this as a follow-up before release please.

Created attachment 173596 [details] [review]
Bug 36766: (follow-up) Set ReplytoDefault as the preferred from_address of sftp_file.pl

Default to KohaAdminEmailAddress if ReplytoDefault is not set

Test plan:
1. Enter a valid email address into ReplytoDefault and
   KohaAdminEmailAddress sysprefs

2. Apply patch and restart services

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

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

5. Enter Koha shell

6. 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 --email <your email>

7. Confirm the SFTP was successful

8. Confirm that the email that you received has a from address matching the
   ReplytoDefault syspref

9. Empty out the ReplytoDefault syspref and repeat steps 5 and 6

10. Confirm that the new email you received has a from address matching
    the KohaAdminEmailAddress syspref

Sponsored-by: Catalyst IT

(In reply to Katrin Fischer from comment #32)
> (In reply to Katrin Fischer from comment #30)
> > Please provide a follow-up:
> > 
> > 1) In some email setups this won't work as the KohaAdminEmailAddress will be
> > a noreply address (for anti-spam reasons)
> > 
> > +if ($email) {
> > +    if ( C4::Context->preference('KohaAdminEmailAddress') ) {
> > +        $admin_address = C4::Context->preference('KohaAdminEmailAddress');
> > +    }
> > 
> > Can you please make the address configurable or better prefer ReplytoDefault
> > and fall-back to KohaAdminEmailAddress if it's not set.
> 
> I need this as a follow-up before release please.

Hey Katrin, sorry I put the follow-up on a separate bug report ( bug 38282 ).

I've just attached that patch to this bug report. Does that give you what you need, or is there anything else I can do?

No that's good, thanks so much! :)

(In reply to Katrin Fischer from comment #35)
> No that's good, thanks so much! :)

Thanks Katrin!

I've left the additional_work_needed keyword. Is this still required?

(In reply to David Nind from comment #37)
> I've left the additional_work_needed keyword. Is this still required?

Yes, the last patch hasn't been pushed yet. I had asked for an additional review since the Email headers can be quite confusing.

Enhancement, no 24.05.x backport