0

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

minus34
  • 255
  • 1
  • 5
  • 16

2 Answers2

1

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.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • A good point to remember. I should have mentioned I'm running the test script from the server itself! – minus34 Apr 16 '14 at 11:10
1

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

Dave Gray
  • 715
  • 5
  • 11
  • Adding the IPv6 trust line to pg_hba.conf did the trick, although I'm not sure why. I'm running the test script on the server itself (i.e. client and server are the same machine), which may explain it. – minus34 Apr 16 '14 at 11:09