Expect: Bash Scripting with Expect and Postgresql

Question

Necessary Info: I'm trying to use expect to automate a password involving a command with PostgreSQL in a BASH script. I've tried mimicking several examples I found here on StackOverflow.com and also other resources on the internet with no success.

Issue: I'm still getting prompted for input.

Code:

Extra Info: password is a global variable not shown in this snippet

database_work(){
sudo -u postgres createdb tinyows
sudo python /$1/database.py

expect <<- DONE
spawn sudo -u postgres psql -U postgres -d tinyows < `pg_config   --sharedir`/contrib/postgis-2.1/postgis.sql 
        expect "*?assword:*"
        send -- "$password\r"
        send -- "\r"
        expect eof
DONE
expect <<- DONE
spawn sudo -u postgres psql -U postgres -d tinyows < `pg_config   --sharedir`/contrib/postgis-2.1/spatial_ref_sys.sql 
        expect "*?assword:*"
        send -- "$password\r"
        send -- "\r"
        expect eof
DONE
}

Ending Note: I appreciate any help and advice anyone gives, thank you.

Answer 1

You're trying to solve your carpentry problem with a welder.

This won't work for a number of reasons.

sudo intentionally prevents you from redirecting its stdin, instead reading it from the terminal. This is partly to make password stealing wrappers harder, but is mostly useful so that things like some-command | sudo othercommand work even if sudo has to prompt for a password. To disable this you must pass the --stdin flag to sudo, explicitly telling it you want it to read a password from stdin.

sudo may or may not actually prompt for a password at each invocation, depending on its settings, whether the user ran it recently, etc. expect won't like that.

The better way to do this is to set up sudo so that you don't have to supply a password to perform the desired action, or get the user to run your whole script under sudo first so they can interactively respond to the prompt. Then use a regular shell script, or preferably an automation tooling framework like Ansible or Puppet.

BTW, on anything except ancient PostgreSQL releases you should be using CREATE EXTENSION postgis; not running a loader script through psql.