Using a 'loopback' FDW

Started by Steve Baldwinabout 5 years ago4 messagesgeneral
Jump to latest
#1Steve Baldwin
steve.baldwin@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Steve Baldwin (#1)
Re: Using a 'loopback' FDW

Steve Baldwin <steve.baldwin@gmail.com> writes:

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.

What are your pg_hba.conf settings for local connections?

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.

This suggests that you're using trust, peer, or something else in which
the password isn't actually relevant.

regards, tom lane

#3Steve Baldwin
steve.baldwin@gmail.com
In reply to: Tom Lane (#2)
Re: Using a 'loopback' FDW

Thanks Tom. I'm running postgres from the standard alpine docker container
and haven't changed that file. I'm not at my laptop ATM but will check it
out when I get home.
Cheers, Steve

On Wed, 10 Mar 2021, 14:42 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Steve Baldwin <steve.baldwin@gmail.com> writes:

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.

What are your pg_hba.conf settings for local connections?

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.

This suggests that you're using trust, peer, or something else in which
the password isn't actually relevant.

regards, tom lane

#4Steve Baldwin
steve.baldwin@gmail.com
In reply to: Steve Baldwin (#3)
Re: Using a 'loopback' FDW

Thanks so much Tom. That was indeed the problem. In hindsight the error
information makes perfect sense (at least after reading the docs on
pg_hba.conf).

Regards,

Steve

On Wed, Mar 10, 2021 at 3:04 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Show quoted text

Thanks Tom. I'm running postgres from the standard alpine docker container
and haven't changed that file. I'm not at my laptop ATM but will check it
out when I get home.
Cheers, Steve

On Wed, 10 Mar 2021, 14:42 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

Steve Baldwin <steve.baldwin@gmail.com> writes:

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.

What are your pg_hba.conf settings for local connections?

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.

This suggests that you're using trust, peer, or something else in which
the password isn't actually relevant.

regards, tom lane