Run batch file with psql command without password

Question

I am trying to execute this psql command using a batch script:

psql --host=localhost --dbname=<dbname> --port=<Port Number>
     --username=<dbuser> --file=C:\PSQL_Script.txt --output=C:\PSQL_Output.txt

The problem is that it's asking for the password every time I execute the batch script. How can I password argument through the batch file?

Answer 1

Keep reading, the best options come last. But let's clarify a couple of things first.

Only silence the password request

If your issue is only the password prompt, you can silence it. I quote the manual here:

-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. (...)

You probably don't need a password

Normally this is unnecessary. The default database superuser postgres usually corresponds to the system user of the same name. Running psql from this account doesn't require a password if the authentication method peer or ident are set in your pg_hba.conf file. You probably have a line like this:

local    all    postgres    peer

And usually also:

local    all    all         peer

This means, every local user can log into a all database as database user of the same name without password.
However, there is a common misconception here. Quoting again:

This method is only supported on local connections.

Bold emphasis mine.
You are connecting to localhost, which is not a "local connection", even though it has the word "local" in it. It's a TCP/IP connection to 127.0.0.1. Wikipedia on localhost:

On modern computer systems, localhost as a hostname translates to an IPv4 address in the 127.0.0.0/8 (loopback) net block, usually 127.0.0.1, or ::1 in IPv6.

Simple solution for local connections

Omit the parameter -h from the psql invocation. Quoting the manual on psql once more:

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

Windows

... doesn't have Unix-domain sockets, pg_hba.conf lines starting with local are not applicable on Windows. On Windows you connect via localhost by default, which brings us back to the start.

If your security requirements are lax, you could just trust all connections via localhost:

host    all    all    127.0.0.1/32     trust

I would only do that for debugging with remote connections off. For some more security you can use SSPI authentication on Windows. Add this line to pg_hba.conf for "local" connections:

host    all    all    127.0.0.1/32     sspi

If you actually need a password

You could set an environment variable, but this is discouraged, especially for Windows. The manual:

PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file (see Section 32.15).

The manual on psql:

A conninfo string is an alternative to specify connection parameters:

 $ psql "user=myuser password=secret_pw host=localhost port=5432 sslmode=require"

Or a URI, which is used instead of a database name:

 $ psql postgresql://myuser:secret_pw@localhost:5432/mydb?sslmode=require

Password File

But it's usually preferable to set up a .pgpass file rather than putting passwords into script files.
Read the short chapter in the manual carefully. In particular, note that here ...

A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine.

Exact path depends on the system. This file can store passwords for multiple combinations of role and port (DB cluster):

localhost:5432:*:myadmin:myadminPasswd
localhost:5434:*:myadmin:myadminPasswd
localhost:5437:*:myadmin:myadminPasswd
...

On Windows machines look for the file in:

%APPDATA%\postgresql\pgpass.conf

%APPDATA% typically resolves to: C:\Documents and Settings\My_Windows_User_Name\Application Data\.