Обсуждение: dblink authentication failed
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
![]() | Mentes a vírusoktól. www.avast.com |
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 connectionDETAIL: 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_nameit works fine.
My .pgpass file does have the line:
192.168.1.40:5432:*:myuser:mypasswordIt 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
Mentes a vírusoktól. www.avast.com
Hello: On Mon, Jun 27, 2016 at 9:38 AM, Kiss Attila <kiss.attila@dardanis.hu> wrote: I'm not expert on dblink but, even supposing it uses libpq and its files: > # psql –h 192.168.1.40 –U myuser –w remote_db_name ... > # ls –la ./.pgpass > -rw------- 1 postgres postgres 193 jún 24 13:54 ./.pgpass This '#' seems to indicate you run those commands as root, while the server typically runs as postgres. Have you checked the commands work when issued as the server user? Francisco Olarte.
I just wrote '#' to show it is command line, but actually I ran the psql as user postgres, just like the SQL server process is running. So if it was deceptive, I am sorry.
-----eredeti Üzenet-----
Feladó: Francisco Olarte <folarte@peoplecall.com>
Elküldött: Hétfő 2016.06.27 11:23
Tárgy: Re: [GENERAL] dblink authentication failed
Címzett: Attila Kiss <kiss.attila@dardanis.hu>;
Másolatot kap: pgsql-general@postgresql.org;
Hello:
On Mon, Jun 27, 2016 at 9:38 AM, Kiss Attila <kiss.attila@dardanis.hu> wrote:
I'm not expert on dblink but, even supposing it uses libpq and its files:
> # psql –h 192.168.1.40 –U myuser –w remote_db_name
...
> # ls –la ./.pgpass
> -rw------- 1 postgres postgres 193 jún 24 13:54 ./.pgpass
This '#' seems to indicate you run those commands as root, while the
server typically runs as postgres. Have you checked the commands work
when issued as the server user?
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
-----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;body { font-family: monospace; } 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 connectionDETAIL: 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_nameit works fine.
My .pgpass file does have the line:
192.168.1.40:5432:*:myuser:mypasswordIt 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
Mentes a vírusoktól. www.avast.com
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&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> > Mentes a vírusoktól. www.avast.com > <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> > > -- Adrian Klaver adrian.klaver@aklaver.com