Обсуждение: DBLINK Error

Поиск
Список
Период
Сортировка

DBLINK Error

От
Daulat
Дата:
Hello Team,

I am facing an issue while fetching data using dblink.
I am using a .pgpass file and MD5 for authentication.

.pgpass file is located in the home directory (postgres user home) of the user running the PostgreSQL query. This file has proper permission  0600 and owner postgres to keep it secure. The OS is Amazon Linux.
 
Connection made successfully without giving password.

[root@DEM-MT bin]# ./psql -d postgres -U postgres
psql (14.6)
Type "help" for help.


postgres=#
 cd /opt/PostgreSQL-14/bin/
[root@DEM-MT bin]# ./psql -d postgres -U postgres -W
Password:
psql (14.6)
Type "help" for help.

postgres=#


ERROR:
-----------

cd /opt/PostgreSQL-14/bin/
[postgres@DMM-MT bin]$ ./psql -U postgres -d tn2_demo_10

tn2_demo_10=# select * from ucf.dblink_connect_u('conn','dbname="ADT_DEM_DB"');
ERROR:  could not establish connection
DETAIL:  connection to server on socket "/tmp/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Please help on this issue.  

Thanks.

Re: DBLINK Error

От
Erik Wienhold
Дата:
On 02/09/2023 09:50 CEST Daulat <daulat.dba@gmail.com> wrote:

> I am facing an issue while fetching data using dblink.
> I am using a .pgpass file and MD5 for authentication.

What does ~postgres/.pgpass and pg_hba.conf look like?

> .pgpass file is located in the home directory (postgres user home) of the
> user running the PostgreSQL query. This file has proper permission 0600 and
> owner postgres to keep it secure. The OS is Amazon Linux.
>
> Connection made successfully without giving password.
>
> [root@DEM-MT bin]# ./psql -d postgres -U postgres
> psql (14.6)
> Type "help" for help.
>
> postgres=#

Does root also have a .pgpass file?  Otherwise this looks like user postgres is
configured with trust authentication (at least on database postgres) if there's
no password prompt for user postgres.

> cd /opt/PostgreSQL-14/bin/
> [root@DEM-MT bin]# ./psql -d postgres -U postgres -W
> Password:
> psql (14.6)
> Type "help" for help.
>
> postgres=#

Does it accept any password?  That would be the case for trust authentication.

> ERROR:
> -----------
>
> cd /opt/PostgreSQL-14/bin/
> [postgres@DMM-MT bin]$ ./psql -U postgres -d tn2_demo_10
>
> tn2_demo_10=# select * from ucf.dblink_connect_u('conn','dbname="ADT_DEM_DB"');
> ERROR: could not establish connection
> DETAIL: connection to server on socket "/tmp/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Okay, no trust authentication for user postgres on database ADT_DEM_DB.  My
guess is an error in ~postgres/.pgpass so that libpq does not find a matching
entry for the connection.

--
Erik



Re: DBLINK Error

От
Daulat
Дата:
It is working fine after restarting the postgres server. 

On Sat, Sep 2, 2023 at 6:58 PM Erik Wienhold <ewie@ewie.name> wrote:
On 02/09/2023 09:50 CEST Daulat <daulat.dba@gmail.com> wrote:

> I am facing an issue while fetching data using dblink.
> I am using a .pgpass file and MD5 for authentication.

What does ~postgres/.pgpass and pg_hba.conf look like?

> .pgpass file is located in the home directory (postgres user home) of the
> user running the PostgreSQL query. This file has proper permission 0600 and
> owner postgres to keep it secure. The OS is Amazon Linux.
>
> Connection made successfully without giving password.
>
> [root@DEM-MT bin]# ./psql -d postgres -U postgres
> psql (14.6)
> Type "help" for help.
>
> postgres=#

Does root also have a .pgpass file?  Otherwise this looks like user postgres is
configured with trust authentication (at least on database postgres) if there's
no password prompt for user postgres.

> cd /opt/PostgreSQL-14/bin/
> [root@DEM-MT bin]# ./psql -d postgres -U postgres -W
> Password:
> psql (14.6)
> Type "help" for help.
>
> postgres=#

Does it accept any password?  That would be the case for trust authentication.

> ERROR:
> -----------
>
> cd /opt/PostgreSQL-14/bin/
> [postgres@DMM-MT bin]$ ./psql -U postgres -d tn2_demo_10
>
> tn2_demo_10=# select * from ucf.dblink_connect_u('conn','dbname="ADT_DEM_DB"');
> ERROR: could not establish connection
> DETAIL: connection to server on socket "/tmp/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Okay, no trust authentication for user postgres on database ADT_DEM_DB.  My
guess is an error in ~postgres/.pgpass so that libpq does not find a matching
entry for the connection.

--
Erik

Re: DBLINK Error

От
Erik Wienhold
Дата:
On 06/09/2023 10:01 CEST Daulat <daulat.dba@gmail.com> wrote:

> It is working fine after restarting the postgres server.

Did you modify pg_hba.conf or other configs?  Those must be reloaded with
pg_ctl reload  to have an effect.  Of course restarting works also but is only
necessary for some config parameters.

--
Erik



Re: DBLINK Error

От
Daulat
Дата:
No Erik I did not make any change in the pg_hba.conf file. Just to crosscheck I checked the view pg_hba_file_rules before server restart.


On Wed, Sep 6, 2023 at 3:20 PM Erik Wienhold <ewie@ewie.name> wrote:
On 06/09/2023 10:01 CEST Daulat <daulat.dba@gmail.com> wrote:

> It is working fine after restarting the postgres server.

Did you modify pg_hba.conf or other configs?  Those must be reloaded with
pg_ctl reload  to have an effect.  Of course restarting works also but is only
necessary for some config parameters.

--
Erik