Scripting postgres synchronization--is this secure?

Question

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.

#!/bin/bash
#this is pretty seriously sketchy script...
current_time=`date +%s` 
dump_file_name=/tmp/temporary_dump_$current_time.dump
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
exit
EOT
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
exit
EOT

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

echo Cleaning up the local file
rm $dump_file_name

exit 0

Answer 1

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.