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%postgresqlpgpass.conf
%APPDATA% typically resolves to: C:Documents and SettingsMy_Windows_User_NameApplication Data.
