Обсуждение: DBLINK Error
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=#
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=#
[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
[postgres@DMM-MT bin]$ ./psql -U postgres -d tn2_demo_10
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.
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
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
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
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