CODEWITHSUNDEEP
postgresqlpowershell
530529
530529

Reputation: 41

No Password Supplied / Positional Parameter not found PostgreSQL - Powershell

I am trying to build a simple script that outputs a query to a csv in powershell. However, it keeps returning the following error:

psql: fe_sendauth: no password supplied

I tried exposing the password as this will be a safe environment, but have seen suggestions to use pgpass but no real explanation on how to implement.

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
SET 'PGPASSWORD = myPwd';
.\psql -U postgres -w MyDatabase 
copy {'SELECT * FROM table';} TO 'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';

Upvotes: 2

Views: 3159

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

SET is an alias for Set-Variable, but Powershell variables are not environment variables. To set an environment variable, you need to use the $env: scope. Try:

$env:PGPASSWORD = 'myPwd';

See also here for more on environment variables.

Also, I don't think you can get away with putting raw input on the command line like that in PowerShell. I think it will treat things as separate commands, but I could be wrong.

You may also want to use the command switch (-c) and the PowerShell stop parsing symbol (--%) when you call psql to prevent PowerShell from parsing your command string:

.\psql --% -U postgres -w MyDatabase -c "copy {'SELECT * FROM table';} TO 'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

Or set the commands to a variable with here-strings and pipe that to psql:

$query = @'
copy {'SELECT * FROM table';} TO 'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';
'@
$query | .\psql -U postgres -w MyDatabase

Or about a dozen other ways to call an executable.

Upvotes: 9

Related Questions