Take the 2-minute tour ×

I have written a script to synchronize (recreating from scratch) a production database with a development database, and I wanted insight into potential security flaws/issues, the script is posted below. The server has remote access disabled for security purposes, and ssh authentication is managed using publickey authentication.

#this is pretty seriously sketchy script...
current_time=`date +%s` 
echo SSHing and dumping to $dump_file_name
ssh -t remoteuser@remote.server.amazonaws.com << EOT
pg_dump -U postgres -Fc dbName > $dump_file_name
PASSWORD #password in plain text
echo SCPing the file locally to: `pwd`$dump_file_name
scp remoteuser@remote.server.amazonaws.com:$dump_file_name $dump_file_name

echo Cleaning up the remote file
ssh -t remoteuser@remote.server.amazonaws.com << EOT
rm $dump_file_name

ssh -t development@development-testing << EOT
pg_restore --clean --dbname=dbName -Fc --username=postgres -W $dump_file_name
PASSWORD #password in plain text

echo Cleaning up the local file
rm $dump_file_name

exit 0
share|improve this question
Define "secure"... –  voretaq7 Apr 27 '12 at 4:04
add comment

1 Answer

up vote 2 down vote accepted

At a first glance I see four problems:

  1. You are storing a password in plaintext.
    Horrible sin, unforgivable even. There are other ways to deal with this, though each has its own security issues.
    If you absolutely must store this here make sure the script is not readable by anyone who shouldn't have the password...

  2. You're storing your DB dump in the cloud, unencrypted.
    Do you really trust Amazon's security? You shouldn't.

  3. You can run this script more than once (and have 2 going at the same time).
    That's not a "security" problem, but it can be abusive to your DB. The script should lock itself out so it can't be running more than once.

  4. pg_dump gets inefficient on big databases
    This isn't a "security" problem either, but check out the Postgres documentation on backup and restore for more information.
    Also consider asking about backups over on our DBA site.

share|improve this answer
Note that if you're using Amazon AWS as your hosting platform you can disregard #2 - You've already submitted yourself and your data to their security, so there's no additional risk :-) –  voretaq7 Apr 27 '12 at 4:15
I'd also consider (assuming you have postgres 9 or better) using pg_basebackup to synchronize your data to your development database using streaming replication. This is quicker, has no performance issues, resolves the password storage problems (by keeping the passwords in the postgres allowed users' file), and has added connection security if you're using hostssl in your pg_hba.conf file for streaming replication. –  Zac B Sep 20 '12 at 22:02
add comment

Your Answer


