Válasz: [GENERAL] dblink authentication failed

Yes, I missed that. Thank you, it solved the problem. And I had to edit the /etc/hosts file too, to make it sure to find the right host. 

-------- Eredeti üzenet --------
Feladó: Adrian Klaver
Dátum:2016.06.27. 15:28 (GMT+01:00)
Címzett: Attila Kiss
Másolatot kap: pgsql-general@postgresql.org
Tárgy: Re: [GENERAL] dblink authentication failed

On 06/27/2016 03:58 AM, Attila Kiss wrote:

I've got some additional info about the problem.

After several times trying to connect to the remote database, I tried to
connect to the local database with dblink. I edit the .pgpass file like
this:

127.0.0.1:5432:*:postgres:password

192.168.1.40:5432:*:myusername:mypassword

Then I tried to connect from local sql server:

postgres=# select dblink_connect_u('con1','hostaddr=127.0.0.1 port=5432
dbname=postgres user=postgres');

I get the same error like before. But when I edit the .pgpass and change
127.0.0.1 to 'localhost', it works even if I try it with the exactly
same connection string in dblink(hostaddr=127.0.0.1). I don't know what
is going on in the background, maybe some IP address<->hostname problem?

https://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-CONNSTRING

hostaddr

...

"Note that authentication is likely to fail if host is not the name of
the server at network address hostaddr. Also, note that host rather than
hostaddr is used to identify the connection in ~/.pgpass (see Section
31.15)."

....

     -----eredeti Üzenet-----
     *Feladó:*    Kiss Attila <kiss.attila@dardanis.hu>
     *Elküldött:*    Hétfő 2016.06.27 09:42
     *Tárgy:*    [GENERAL] dblink authentication failed
     *Címzett:*    pgsql-general@postgresql.org;

     Hello,

     I have some problem with using dblink_connect_u() in my function.
     When I provide the password in the connection string, there is no
     problem, but when I don’t, it says:
     ERROR:  could not establish connection

     DETAIL:  fe_sendauth: no password supplied

     However the .pgpass file in the path /var/lib/postgres/.pgpass does
     contain the proper password.
     I have tried to connect to the remote server with psql from bash
     with the following command:
     # psql –h 192.168.1.40 –U myuser –w remote_db_name

     it works fine.

     My .pgpass file does have the line:
     192.168.1.40:5432:*:myuser:mypassword

     It seems the psql finds the proper password in the .pgpass file but
     dblink doesn’t.

     The local and the remote server are running on Ubuntu server 14.04,
     both of them are PostgreSQL 9.4.

     The .pgpass file properties look this:

     # ls –la ./.pgpass
     -rw------- 1 postgres postgres 193 jún   24 13:54 ./.pgpass

     Any ideas are welcome.

     Regards,

     Attila

     <https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&gt;
         Mentes a vírusoktól. www.avast.com
     <https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general