Bash script, remote psql command over ssh, pipe sql file, password fails

I have a bash script. I want to run a postgres command with ssh that pipes a local file. The problem is the psql command prompts for a password, and my sql file gets piped into that. How do I write a command that pipes after I type in the password?

ssh server "psql -W -h db_host -p 5432 -U db_user -d postgres" < staging.sql

Solution

I suggest to break it down into multiple steps:

# Transfer the sql file to the server
scp staging.sql server

# Excute the queries in that file with psql over ssh
# Notes:
#   - ssh -t enforces terminal allocation. You may try it without this option and see if it still works.
#   - psql -f FILENAME reads commands from file
# 
ssh -t server \
    'psql -W -h db_host -U db_user -d postgres -f staging.sql; rm staging.sql'