Search code examples
postgresqlauthenticationdblink

Authentication Issue Using PostgreSQL dblink

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

Solution

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