scp a file w/o password FROM remote to local?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have an Apache server and a seperate mySQL server.
A PHP script on the Web server runs a SQL "INTO OUTFILE" which creates
a file... on the mySQL server.
So in order to continue working with the file and make it accessible
through the Web, I need to copy it over to the Apache machine.

I tried executing an "rsync" command, but can't find a way to bypass a
password or force it to use a particular user.
Having done passwordless scp before, I went that route. Created mutual
users, keys, etc, and can ssh/scp using that username and no password.

If I run this (as root) it works:
scp -i /home/scotty/.ssh/id_rsa -B -C -P 22

Problem is, come to find out, the scp can't be run in a PHP script as
another user. Even if I copy the id_rsa into a folder apache can access
(NOT a good idea!! I just did that to test if it'd work.) And I'm not
about to make apache a password-less account.

What can I do to make this happen? If someone could just give me the
name of the command I should look into I'll look up the docs. If rsync
or scp really is still the answer, give me a RTFM and I'll keep
looking--because at the moment I'm not finding anything in the MANs
that will get me further than I am.

Re: scp a file w/o password FROM remote to local? wrote:
Quoted text here. Click to load it

Are you sure that copying the file is the right way to approach the problem?

To me, it would make more sense to omit the "INTO OUTFILE" clause from
the SQL statement.  Let the SQL server return the data to the PHP
script.  Then have the PHP script create the textfile (if a text file is
what you really need in the end).

For example, in the PHP code:

$result = mysql_query( $query_string )
for( row = 0; row < mysql_num_rows( $result ); row++ )
    for( col = 0; col < mysql_num_fields( $result ); col++ )
        fwrite( $outfile, mysql_result( $result, $row, $col ) );
        fwrite( $outfile, "," ); // or any other field delimeter
    fwrite( $outfile, "\n" ); // finish the row with a newline

That's a quick, rough outline of code that would produce the output in a
CSV format.  (I don't know what format you're currently using, but the
above can probably be modified to suit your needs.)  Since the PHP code
is running on the webserver as the Apache user, there's no need to copy
the output file anywhere.

Note that this solution has different security considerations to take
into account than your existing method.  It may not be any better or
worse, just different.

Performance shouldn't be terribly different:  the total number of bytes
transferred is roughly the same.  My method transfers it as part of the
database data stream whereas yours transfers (approximately) the same
number of bytes over rsync or ssh or ....

It's just a thought.

Re: scp a file w/o password FROM remote to local?

John-Paul Stewart wrote:
Quoted text here. Click to load it

Yeah, that's probably what I'm going to do.
See, we used to have only one server so this used to not be a problem
'til we migrated mySQL to its own server. I was hoping I could just add
a couple of lines to transfer the file, but by now it would have just
been a lot easier to write the file locally than to figure this
transfer thing out. =)

Thanks for the clue-by-four.

Re: scp a file w/o password FROM remote to local? wrote:
Quoted text here. Click to load it
Why not have the OUTFILE written into a directory that is exported via
NFS and mounted (or automounted) on the apache machine? You can make the
share as restrictive as you like and would only need to be readonly
because the apache machine will presumably read the file, process it and
produce some output in the web tree.

Frank Ranner

Re: scp a file w/o password FROM remote to local?

Quoted text here. Click to load it

   man sudoers

   This is *USUALLY* used to allow "janedoe" to run specific commands as
"root".  However, it can also be used to allow "janedoe" to run specific
commands as "johnsmith".  Be sure to make the mySQL user and the other
user members of a new group, and set umask such that you can read/write
the files that have been pulled down and belong to the other user.

Walter Dnes; my email address is *ALMOST* like
Delete the "z" to get my real address.  If that gets blocked, follow
the instructions at the end of the 550 message.

Site Timeline