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 '22.214.171.124', 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 126.96.36.199 (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).