How do you create a dblink connection in Postgres without supplying username & password (for security reasons)?
Running the following query returns this error:
SELECT dblink_connect('conn_1','dbname=adatabase');
"could not establish connection 08001"
My pgpass.conf file has the following entry and I'm running the script as the postgres user in pgAdmin:
localhost:5432:*:postgres:apassword
Using dblink_connect_u also returns the same error.
I'm running Postgres 9.2 on Windows 2012 Server
If you're connecting to a PostgreSQL database, you can modify your pg_hba.conf on the server you're connecting to so that (for example) passwordless connections are allowed for your user from the local machine:
local all youruser trust
host all youruser 127.0.0.1/32 trust
host all youruser ::1/128 trust
Ideally, you would create a PostgreSQL user on the server specifically for dblink, and grant only the necessary rights (only SELECT on specific tables, for example).
The server uses its own pgpass file, not "yours" which is inaccessible to it.
Consider this excerpt from dblink_connect_u documentation:
Also, even if the remote server does demand a password, it is possible for the password to be supplied from the server environment, such as a ~/.pgpass file belonging to the server's user.
In unix it would be ~postgres/.pgpass, in Windows you should find the equivalent location depending on the OS user running the server's processes (either postgres or a system account, depending on your installer).
In case the OS user doesn't have a home directory or it's not sensible to put pgpass.conf in there, you may set a different location through the PGPASSFILE environment variable. I can't tell how a Windows administrator sets an environment variable for a specific service, though.
