Run batch file with psql command without password
connection, postgresql, psql, shell
Solution
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\`.
Problem
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?