1

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

2
  • My guess would be that the authentication rules in pg_hba.conf are different for these IP addresses.
    – mustaccio
    Mar 29 at 20:15
  • Unfortunately you don't have access to that file when using Google SQL Cloud, at least that is what our admin tells me.
    – WillyC
    Mar 29 at 20:20
2

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)

3
  • On the first part, I fear you are probably right. For the SSL portion - there is no hostname (or at least they don't provide one). You are given an IP and a socket name to work with. The SSL is a client/server pair, so I have the client cert deployed on my dev box and I can connect no problem. The problem is that I need to deploy the client cert to the DB itself so that when I am in database 'A' I can connect to database 'B' to set up the FDW - but DB 'A' needs the client cert. There's no file system, so no way to install it.
    – WillyC
    Mar 29 at 21:08
  • If the problem were just with the client cert, then starting in v13 you can put these into the user-mapping rather than into the filesystem. But in standard PostgreSQL at least, you have to be a superuser to set that up.
    – jjanes
    Mar 29 at 22:02
  • Unfortunately in SQL Cloud there is no super user access at all (from what I can tell). Even the Postgres user doesn’t have SU privs.
    – WillyC
    Mar 29 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.