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