Search code examples
postgresqlshellapache-nifi

Execute postgres SQL script with password through NIFI

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

as below image

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?

Solution

  • 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.