Using a 'loopback' FDW

From: Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Using a 'loopback' FDW
Date: 2021-03-10 03:17:54
Message-ID: CAKE1AibRA0nhdyk-vF51HxXHp+7gC7AcY0On_-aN=XT2t++RNg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)slaw=> \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version |
FDW options | Description
------+-------+----------------------+-------------------+------+---------+-------------+-------------
(0 rows)

slaw_owner(at)slaw=> create server caas foreign data wrapper postgres_fdw
options(dbname 'slaw', host 'localhost');
CREATE SERVER
slaw_owner(at)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(at)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(at)slaw=# alter user MAPPING FOR slaw_owner server caas options (add
password_required 'false');
ALTER USER MAPPING
dba(at)slaw=#
\q
[~/git/slaw]$ psql
psql (13.2)
Type "help" for help.

slaw_owner(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-03-10 03:42:23 Re: Using a 'loopback' FDW
Previous Message Martín Fernández 2021-03-10 01:56:15 Logical Replication, CPU load and Locking contention