How to connect to postgresql database using shell script

Question

I want to write a shell script to run these commands. I usually connect from terminal using commands as below

 //first go to the directory 
 cd /opt/novell/sentinel/3rdparty/postgresql/bin/
 // then type following
 export LD_LIBRARY_PATH=/opt/novell/sentinel/3rdparty/postgresql/lib/
 // then fire following command 
 ./psql --host 127.0.0.1 --port 5432 --dbname=SIEM --username=dbauser
 Password for user dbauser: ****

• Eugeniu Rosca: So, you are OK with your password being stored in the shell script?
• Gaurav Rajput: no i don't want to store password in my script

Answer 1

Why don't you update your PATH and export LD_LIBRARY_PATH permanently, by adding to your .profile these lines:

PATH=/opt/novell/sentinel/3rdparty/postgresql/bin/:$PATH
export LD_LIBRARY_PATH=/opt/novell/sentinel/3rdparty/postgresql/lib/

Then use the script to connect DB as simple as following

#!/bin/sh
psql --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser

After you run the script, you will be asked about the password.

If you would like not to enter password every time, you can use the password file .pgpass (see documentation for details), just add to your ~/.pgpass the following line:

127.0.0.1:5432:SIEM:dbauser:your_password

Be safe, dissallow any access to world or group:

chmod 0600 ~/.pgpass.

After this, you can connect to your db by using script above without password prompt.

• Gaurav Rajput: where do i create .pgpass file
• hooke: @GauravRajput, .pgpass file usually is located in your home directory, or in a directory defined by PGPASSFILE environmental variable.
• Gaurav Rajput: I had created this .pgpass file under root and then it worked fine.thank you very much
• Youssef Boudaya: i get this error "/usr/bin/postgres : option invalide -- 'U'"