No Password Supplied / Positional Parameter not found PostgreSQL - Powershell

Question

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 ',';

Bacon Bits · Accepted Answer · 2015-08-11 15:46:50Z

9

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.

edited Aug 11, 2015 at 15:46

answered Aug 11, 2015 at 14:47

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

530529 Over a year ago

Thanks for the response. I no longer get the password prompt. I broke out your suggestion into two lines (psql declaration of databse and another for the sql copy command). This works but leaves my script hanging with an open database connection waiting for me to enter my sql command. It doesn't actually execute the copy as I would need. I marked the answer as correct but would really appreciate if you could provide some feedback on this final issue.

530529 Over a year ago

Running with the quotes results in a warning message ignoring the -c argument and another message ignoring the entire copy. Despite the warning messages, it logs onto my database, and hangs again waiting for instructions. MyDatabase=# _

Bacon Bits Over a year ago

@530529 Not sure then. It's been a couple years since I've used psql, and I was on Debian when I was doing it. You'll have to figure out if your command is wrong, or if Powershell is messing with it, or if Powershell simply isn't calling psql correctly and you need to use Start-Process instead.

530529 Over a year ago

Thank you very much for the great guidance. I might have to open a separate question on this new issue since I am struggling to get around it. Have a nice day

LightningWar Over a year ago

I had the same issue, distinction between PS variables and environment. Thanks.

Collectives™ on Stack Overflow

No Password Supplied / Positional Parameter not found PostgreSQL - Powershell

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related