CODEWITHSUNDEEP
postgresqlauthenticationdblink
minus34
minus34

Reputation: 275

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

Upvotes: 0

Views: 3381

Answers (2)

Dave Gray
Dave Gray

Reputation: 723

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

Upvotes: 1

Daniel Vérité
Daniel Vérité

Reputation: 61546

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.

Upvotes: 1

Related Questions