Not able to login to postgres through bash script

Question

I have one script in which I have given following command -

su - postgres

psql

\c db1;

select * from employees;

But when I am running the script I am not able to go inside psql, its stooping after first command that is "su - postgres"

[root@quadoralnx ~]# ./ora.sh

Last login: Tue Aug 25 01:23:59 EDT 2020 on pts/11

-bash-4.2$

And when I am doing exit, it is giving following error -

-bash-4.2$ exit

logout

psql: error: could not connect to server: FATAL: role "root" does not exist

./ora.sh: line 3: c: command not found

./ora.sh: line 4: syntax error near unexpected token `from'

./ora.sh: line 4: `select * from employees;'

[root@quadoralnx ~]#

So here my need is that it should not stop in first step, and should run all the command in that script in single shot. Can someone please help me with this?

Thanks in advance!!

Answer 1

It's not going to work like this. Use this syntax to run sql queries from bash cli:

dbhost=localhost
dbport=5432
dbuser=postgres
dbname=postgres
psql -h$dbhost -p$dbport -U$dbuser -d$dbname -c "select 1;"

And to avoid password promting you can create ~/.pgpass file like this

dbpass=secret
echo "$dbhost:$dbport:$dbname:$dbuser:$dbpass" > ~/.pgpass
chmod 600 ~/.pgpass

• saurabh704: Thank you @Ivan for your response. That script is working for me. But that password prompt is not working for me. Actually I created one ~/.pgpass file in which I added that 2 lines and after saving I came out and ran chmod command. But its still asking me for password. So am I lagging something or what?
• Ivan: You have to set password for user postgres, or create your own psql user/pass.