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?
Keep reading, the best options come last. But let's clarify a couple of things first.
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. (...)
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 the127.0.0.0/8
(loopback) net block, usually127.0.0.1
, or::1
in IPv6.
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.
... 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
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
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 namelocalhost
) 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\
.
I had kinda same problem:
psql -hlocalhost -d<myDB> -U<myUser>
always prompted me for password. This is as @Erwin explained because of -hlocalhost
is connecting through TCP and not through the Unix-domain socket (for Unix based OS). So even if you've configured your local
as trusted:
local all all trust
it will still prompt for password. So in order to configure the -hlocalhost
to work through TCP I had to configure the host
for localhost addresses, like so:
host all all 127.0.0.1/32 trust host all all ::1/128 trust
But this didn't work for me. What I had to do is combine both of those as:
host all all localhost trust
Some additional readings:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With