Windows PSQL command line: is there a way to allow for passwordless login?

Question

My goal is to be able to fire off a command without having to be prompted for my password. Is there any way to achieve this from the windows command line? In Linux I feel I could send the password to standard in or something, but I am not sure if I could do this for windows.

Thanks!

Answer 1

There are two ways:

• Set environment variable PGPASSWORD e.g. set PGPASSWORD=yoursecretpassword
• Use password file %APPDATA%\postgresql\pgpass.conf as described in documentation

Within password file (my location is C:\Users\Grzesiek\AppData\Roaming\postgresql\pgpass.conf) use specified in doc format. For example to connect database postgres as role postgres on local 5432 server add:

localhost:5432:postgres:postgres:12345

I checked this and it works well (for me), but don't use 127.0.0.1).

Answer 2

Another handy option (specially if your PG server runs in your own client machine, and if this does not poses any security problem for you) is to allow login without password in the server ("trust" authentication mode).

For example, this line in pg_hba.conf (in your DATA dir, a typical location: C:\Program Files\PostgreSQL\9.0\data\ ) grants access without password from your local machine.

host     all     all     127.0.0.1/32    trust

Then, connect with

  psql.exe -h 127.0.0.1 -U postgres -w [YOUR_DB_NAME]

Answer 3

I know it is an old question but for anyone looking like I was, it is hard to find a solution for windows. stick this in a .bat file and it will work (at least for me it did). change director to postres directory, set environment variable PGPASSWORD execute copy command to a csv file and then clear environment variable, then go back to root directory.

cd C:\Program Files\PostgreSQL\9.5\bin\

set PGPASSWORD=yourpassword


psql -d databasename -U yourusername -w -c "\COPY (select * from yourtable) TO 'c:/Users/yourdirectory/yourcsvfilename.csv' DELIMITER '|' CSV HEADER;"

set PGPASSWORD=

cd c:\

Answer 4

I realize this question is a bit old now, but I believe there is a better means for secure, password-free PostgreSQL logon on Windows - SSPI.

1. Create a local or domain user account and PostgreSQL login with the same name.

2. In pg_ident.conf, create a mapping:

   # MAPNAME   SYSTEM-USERNAME      PG-USERNAME
   SSPI        username@AUTHORITY   loginname
   

   Replace username with the Windows user name (this is the sAMAccountName attribute for domain accounts), and replace AUTHORITY with the computer name or short domain name. If you're not sure what to use for AUTHORITY, check the PostgreSQL log file. For a local account, this will be the computer name; for a domain account, it's probably the short domain name. Lastly, replace loginname with the PostgreSQL login name (to reduce confusion, I would recommend using the same name for both username and loginname).

3. In pg_hba.conf, allow the logon; e.g.:

   # TYPE   DATABASE   USER        ADDRESS        METHOD
   host     all        loginname   127.0.0.1/32   sspi map=SSPI
   host     all        loginname   ::1/128        sspi map=SSPI
   

4. If a domain account, set a SPN for it; e.g.:

   setspn -S POSTGRES/serverfqdn username
   

Now you can log onto Windows using the specified username and run psql.exe, etc. without needing a password.