IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)

Question

I have a Postgres server in Google SQL Cloud with multiple databases. I want to create a FOREIGN DATA WRAPPER between them. If I use the server's remote IP it works:

CREATE SERVER "some_db_fdw" FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '34.94.1.23', dbname 'some_db');
CREATE USER MAPPING FOR some_user SERVER "some_db_fdw" OPTIONS (user 'some_user', password 'some_password');
IMPORT FOREIGN SCHEMA public LIMIT TO (my_table) FROM SERVER "some_db_fdw" INTO public;

...but if I use the local IP (127.0.0.1) in place of 34.94.1.23 (sample IP only) it gives me the error:

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.

Why? I'm providing the password in the user mapping. In fact if I omit the password there then the error changes to:

ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

So it seems to know that it should be getting the password from the user mapping.

The reason I can't just use the remote IP is that we require certs so if I enable "Allow only SSL connections" using the remote IP fails because I don't have certs within psql (I have them on the machine I connect with or I wouldn't be able to connect at all, but since this server is in SQL Cloud, I don't have any filesystem access and can't provide the DB itself with certs to connect to the other DB - thus my attempt to use 127.0.0.1 to avoid this issue).

Answer 1

The problem is that when you connect locally, PostgreSQL does not demand a password. So while the user mapping might define a password, that password can't be used as it is never asked for. If it doesn't ask for a password but can still connect, that probably means it is relying on the identity of the process doing the connecting to authenticate it. Since the process doing the connecting is owned by a service account which is running PostgreSQL (usually 'postgres'), you are leveraging an identity you don't have a right to leverage. For security reasons, this must be disallowed.

Since you are using a hosted service, you are at the mercy of the host to fix it. There are several ways they could fix this. There are no ways you can fix it without their cooperation, other than to stop using their service.

"Allow only SSL connections" using the remote IP fails because I don't have certs

SSL certs are generally going to have the host name, not the host IP address. Did you try with the host name? It might be that Google automatically installs their own certs (or that you can talk them into doing so)

Answer 2

Building on top of jjanes's answer—for those still looking for an answer in an environment where you have control, you need to disable local trust for all users by modifying the following line in your pg_hba.conf:

local   all             all                                trust

to:

local   all             postgres                                trust

From there on, set the local authentication for your desired local user and the foreign user for the local DB and the foreign DB respectively as md5 or any other password authentication mechanism.