Using a 'loopback' FDW
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
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
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
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, SteveOn 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