0

I'm attempting to connect to a remote server using a foreign data wrapper to transfer some data between databases but keey getting 'SSLMode Value "require" Invalid When SSL Support Is Not Compiled In'. These are the snippets I'm running:

CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(dbname '...', host '...', port '...', sslmode 'require');

CREATE USER MAPPING FOR USER SERVER remote
OPTIONS (user '...', password '...');

CREATE FOREIGN TABLE remote.accounts (
  ...
);

SELECT * FROM remote.accounts;

DETAIL: sslmode value "require" invalid when SSL support is not compiled in

I've tried connecting into psql using "sslmode=require" - but that doesn't seem to help. I've also included sslmode as a flag when creating the server. The postgres instance I'm connecting to is on Heroku. The queries run if I connect to a different database on a remote server - but fail from my local database. How can I fix the SSL support is not compiled in?

Kevin Sylvestre
  • 37,288
  • 33
  • 152
  • 232
  • RPostgreSQL code shows a parameter, USE_SSL, that if it is not defined (https://github.com/cran/RPostgreSQL/blob/0e9b0cf002188f98258bd21a0d43d13c632a317c/src/libpq/fe-connect.c#L851) will result in the error message (https://github.com/cran/RPostgreSQL/blob/0e9b0cf002188f98258bd21a0d43d13c632a317c/src/libpq/fe-connect.c#L867). How is USE_SSL defined/determined? – Brian D Jan 21 '21 at 19:21

2 Answers2

6

The local PostgreSQL instance uses the local libpq library to connect to the remote instance.

If, at compile time, that library has been configured with --with-openssl (an option to pass to the top-level ./configure script of postgres source tree), then it gets dynamically linked to the openssl library and the call sites to encrypt a session are added.

If on the other hand, it was not configured with this option, the libpq produced doesn't have any SSL capability at all, and it's impossible with that library to connect to remote servers that require SSL.

According to the error message, you are in the second case.

The only way out of this problem is to replace your installed libpq library by another one that is compiled with SSL support, which also mean that you have OpenSSL installed on your system. Either compile it yourself or install it through packages depending on your preferences. In general pre-compiled versions of PostgreSQL tend to have SSL support enabled these days.

Even if libpq sources are inside the server's source, it's possible to compile them separately and use just that library independently of the rest. There's a question on that: How to download, compile & install ONLY the libpq source on a server that DOES NOT have PostgreSQL installed

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
0

If you get this error in Lambda, you should use a version of psycopg2 with ssl (e.g. this repo has psycopg2 with and without ssl).

LoMaPh
  • 1,476
  • 2
  • 20
  • 33