FDW use in postgres

Question

I need to create a sequence and need to use the sequence for auto increment id for more than 2 databases on 2 separate servers. I'm referring below doc for that(for test purpose): https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/

I have followed below steps:

    Server 1: 
CREATE DATABASE
postgres=# \c a1
You are now connected to database "a1" as user "postgres".
a1=# CREATE SEQUENCE seq;
CREATE SEQUENCE
a1=# CREATE VIEW seq_view AS SELECT nextval('seq') as a;
CREATE VIEW
a1=# \c postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create database a2;
 CREATE DATABASE
 postgres=# \c a2;
You are now connected to database "a2" as user "postgres".
a2=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
a2=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS
a2-# (host '192.168.xx.xxx', port '5432', dbname 'a1');
CREATE SERVER
a2=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (password 
'');
CREATE USER MAPPING
a2=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER
a2-# postgres_server
a2-# OPTIONS (table_name 'seq_view');
CREATE FOREIGN TABLE
a2=# select * from foreign_seq_table;
 a
---
 1
(1 row)

a2=# select * from foreign_seq_table;
a
---
2
(1 row)

As it can be seen from above example, it's working properly for 2 databases on same server. However, when I proceed with another server, I did below steps there:

    postgres=# create database kbc;
CREATE DATABASE
 postgres=# \c kbc
You are now connected to database "kbc" as user "postgres".
kbc=#  CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
kbc=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS
kbc-# (host '192.168.xx.xxx', port '5432', dbname 'a1');
CREATE SERVER
kbc=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS 
(password '');
CREATE USER MAPPING
kbc=#
kbc=# CREATE FOREIGN TABLE foreign_seq_table (a bigint) SERVER 
postgres_server OPTIONS (table_name 'seq_view');
  CREATE FOREIGN TABLE
 kbc=# select * from foreign_seq_table;
  ERROR:  could not connect to server "postgres_server"
    DETAIL:  fe_sendauth: no password supplied

The error at last I'm receiving. Is there anything/any step I'm missing here. I can easily ping to old server(where I created sequence) from new server(where I want to use that sequence). pg_hba.conf file settings on both server -> the entries there are md5 or trust only. Any other entry I need to add into configuration file? Any suggested doc for fdw across different servers will also be helpful.

Thanks in advance!

Note: I can't use UUID due to some application requirement. That's why we need an auto increment numeric column. It picks the highest id value.

Answer 1

"fe_sendauth: no password supplied"

The foreign server is demanding a password (due to pg_hba.conf being set to md5, presumably) and you are not supplying one. - jjanes

---

I altered my user mapping (from another server) and provided password there and got it working. Like below,

CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS 
(user 'postgres' , password 'xxxxxxx');