How to use PGPASS file in Powershell to avoid password prompt?

Question

I had to automate my postgre database backup. As instructed by my software vendor I am trying to use pg_dump.exe (see below) file to take a backup but that prompts me for password.

.\pg_dump.exe -h localhost -p 4432 -U postgres -v -b -F t -f "C:\Backup\Backup.tar" Repo

So googled and found that as per "https://www.postgresql.org/docs/9.6/libpq-pgpass.html" I can create a pgpass.conf file within 'C:\Users\User1\AppData\Roaming\postgresql\pgpass.conf" which I did.

Then I tried to pass data of pgpass.conf file to env variable before executing my pg_dump command. But it is not working. Still I am getting prompt to enter password. This is the content of pgpass.conf file: *:*:*:postgres:password

Below is the code I am trying in PowerShell,

   $Env:PGPASSFILE="C:\Users\User1\AppData\Roaming\postgresql\pgpass.conf"
   cd "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin"
   .\pg_dump.exe -h localhost -p 4432 -U postgres -v -b -F t -f "C:\Backup\Backup.tar" Repo

Why am I still being asked for password?

When I type following code $Env:AppData I get following response "C:\Users\User1\AppData\Roaming"

Everywhere there are guidance on how to use it in UNIX or command prompt but not in powershell. Any help is appreciated. Also if you could direct me how to secure this password file then it will be great.

With password prompt I cannot automate it with windows task scheduler.

pgpass.conf works fine for me with powershell. Do some basic debugging. What if you use cmd instead of powershell? psql instead of pg_dump? — jjanes, Sep 23 '21 at 00:18
Thank you @jjanes. It was not working in command prompt either. Since there was no error message, I am not sure what i was missing. I am going to use pg_hba file to allow connection "trust" this is riskier method but I had to get things done ASAP. Thank you for your time and effort. — Sky scream, Sep 23 '21 at 04:02
On linux I would next use `strace` to see if it was even opening the pgpass file. I don't the windows equivalent of that, but presumably there is one. — jjanes, Sep 23 '21 at 21:14
@Skyscream, did you ever get this question answered? If so, please answer your own post and accept it for the greater community! — Parfait, Feb 14 '22 at 17:47

score 0 · Answer 1 · answered Oct 03 '21 at 02:11

I suspect you have a suitable solution, however, as a quick (and not secure) workaround via the command prompt, you can use the variable PGPASSWORD to hold the password then run the backup script.

A sample might be something like:

SET PGPASSWORD=password

cd "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin" pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "d:\qs_backup\QSR_backup.tar" QSR

Rod

score 0 · Answer 2 · answered Jan 11 '22 at 18:03

I have yet to get the damned thing to work yet, but I did find this:

-w --no-password Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

I don't see a -w parameter in your call to pg_dump

score 0 · Accepted Answer · answered Mar 15 '22 at 04:58

0

I used pg_hba file to allow connection "trust" this is riskier method but I had to get things done ASAP. Thank you for your time and effort

answered Mar 15 '22 at 04:58

Sky scream

35
5

How to use PGPASS file in Powershell to avoid password prompt?

3 Answers3