Using a 'loopback' FDW

[Steve Baldwin <steve.baldwin@xxxxxxxxx>]

I'm guessing I'm doing something wrong here. I've used postgres_fdw before to connect between servers, but in this instance I'm trying to use it to connect back to itself.

(This is postgres 13.2)

In my local DB have a user 'slaw_owner' which has a password of 'password'. This user has been granted usage on postgres_fdw.

slaw_owner@slaw=> \des+
                                       List of foreign servers
 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+-------+----------------------+-------------------+------+---------+-------------+-------------
(0 rows)

slaw_owner@slaw=> create server caas foreign data wrapper postgres_fdw options(dbname 'slaw', host 'localhost');
CREATE SERVER
slaw_owner@slaw=> create user mapping for slaw_owner server caas options (user 'slaw_owner', password 'password');
CREATE USER MAPPING

So far so good. When I try to use this mapping however

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user) from server caas into fdw;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server's authentication method must be changed or password_required=false set in the user mapping attributes.

When I look at the server (csv) log it _seems_ to be connecting successfully:

2021-03-10 02:49:22.100 UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,9,"idle",2021-03-10 02:46:54 UTC,3/106,0,LOG,00000,"statement: import foreign schema caas limit to (api_key, buyer_user) from server caas into fdw;",,,,,,,,,"psql","client backend"
2021-03-10 02:49:22.104 UTC,,,667,"127.0.0.1:54290",604833b2.29b,1,"",2021-03-10 02:49:22 UTC,,0,LOG,00000,"connection received: host=127.0.0.1 port=54290",,,,,,,,,"","not initialized"
2021-03-10 02:49:22.106 UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,2,"authentication",2021-03-10 02:49:22 UTC,4/17,0,LOG,00000,"connection authorized: user=slaw_owner database=slaw application_name=postgres_fdw",,,,,,,,,"","client backend"
2021-03-10 02:49:22.109 UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,10,"IMPORT FOREIGN SCHEMA",2021-03-10 02:46:54 UTC,3/106,0,ERROR,2F003,"password is required","Non-superuser cannot connect if the server does not request a password.","Target server's authentication method must be changed or password_required=false set in the user mapping attributes.",,,,"import foreign schema caas limit to (api_key, buyer_user) from server caas into fdw;",,,"psql","client backend"
2021-03-10 02:49:22.111 UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,3,"idle",2021-03-10 02:49:22 UTC,,0,LOG,00000,"disconnection: session time: 0:00:00.008 user=slaw_owner database=slaw host=127.0.0.1 port=54290",,,,,,,,,"postgres_fdw","client backend"

If, as a superuser I modify the user mapping, everything works:

[~/git/slaw]$ psql -U dba
psql (13.2)
Type "help" for help.

dba@slaw=# alter user MAPPING FOR slaw_owner server caas options (add password_required 'false');
ALTER USER MAPPING
dba@slaw=#
\q
[~/git/slaw]$ psql
psql (13.2)
Type "help" for help.

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user) from server caas into fdw;
IMPORT FOREIGN SCHEMA

I don't understand why it doesn't like it when I define a password in the user mapping.

Any ideas gratefully received.

Thanks,

Steve