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
