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

All we need is an easy explanation of the problem, so here it is.

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).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply