Execute postgres SQL script with password through NIFI-CodePudding

In a NIFI flow, I want to run a .sql file in a PostgreSQL database.

so I created a shell script file as below and execute it using ExecuteStreamCommand processor,

#!/bin/bash
psql -d dbName -U user -W  -f file.sql

My concern is that when we execute psql -d dbName -U user -W -f file.sql via terminal it asks for the psql password as below

also i tried PGPASSWORD=mypw psql -d dbName -U user -W -f file.sql but it also requested to enter the password

I tried to pass the password as the parameter via shell script but it didn't works

Since I have a little bit of experience with shell scripts, I have no idea how to provide the password via shell script or any other way to achive this.

Could you please advise me on this?

CodePudding user response：

The answer to this question has been give on dba.stackexchange.com:

How to use psql with no password prompt?

Also specify only parameters to psql, which are really needed. For an overview of parameters use psql -? (see: https://www.postgresql.org/docs/current/app-psql.html )

In this case the option -W was making sure the password was always asked.