Can I run 'su' in the middle of a bash script?

Question

You can, but bash won't run the subsequent commands as postgres. Instead, do:

su postgres -c 'dropdb $user'

The -c flag runs a command as the user (see man su).

You can use a here document to embed multiple su commands in your script:

if [ "$user" == "" ]; then
  echo "Enter the table name";
  read user
fi

gunzip *
chown postgres *
su postgres <<EOSU
dropdb $user
psql -c "create database $user with encoding 'unicode';" -U dbname template1
psql -d $user -f *.sql
EOSU

Not like this. su will invoke a process, which defaults to a shell. On the command line, this shell will be interactive, so you can enter commands. In the context of a script, the shell will end right away (because it has nothing to do).

With

su user -c command

command will be executed as user - if the su succeeds, which is generally only the case with password-less users or when running the script as root.

Use sudo for a better and more fine-grained approach.

Refer to answers in below question,

You can write between << EOF and EOF as mentioned in answers.

#!/bin/bash
whoami
sudo -u someuser bash << EOF
echo "In"
whoami
EOF
echo "Out"
whoami

How do I use su to execute the rest of the bash script as that user?