Postgres SSLMode Value "require" Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper

Question

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?

RPostgreSQL code shows a parameter, USE_SSL, that if it is not defined (github.com/cran/RPostgreSQL/blob/…) will result in the error message (github.com/cran/RPostgreSQL/blob/…). How is USE_SSL defined/determined? — Brian D
– Brian D, Commented Jan 21, 2021 at 19:21

Community · Accepted Answer · 2017-05-23 11:51:32Z

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

LoMaPh · Accepted Answer · 2023-06-23 00:47:36Z

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

answered Jun 23, 2023 at 0:47

LoMaPh

1,7203 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

Postgres SSLMode Value "require" Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related