Postgres SSLMode值“ require”使用外部数据包装器未编译SSL支持时无效 [英] Postgres SSLMode Value "require" Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper

问题描述

我正在尝试使用外部数据包装器连接到远程服务器，以在数据库之间传输某些数据，但是请谨慎考虑未编译SSL支持时SSLMode值 require无效。这些是我正在运行的摘录：

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;

详细信息：sslmode值 require在未编译SSL支持时无效在

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

我尝试使用 psql > sslmode = require -但这似乎无济于事。创建服务器时，我还添加了 sslmode 作为标记。我要连接的postgres实例在Heroku上。如果我连接到远程服务器上的其他数据库，则查询将运行-但是从本地数据库失败。如何解决未编译的SSL支持？

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?

推荐答案

本地PostgreSQL实例使用本地 libpq 库进行连接

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

如果在编译时已使用-with-openssl （一个选项以传递到postgres源代码树的顶级 ./ configure 脚本），然后将其动态链接到 openssl 库和用于加密会话的呼叫站点。

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.

如果未使用此选项进行配置，则 libpq 根本没有任何SSL功能，并且该库无法连接到需要SSL的远程服务器。

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.

解决此问题的唯一方法是替换已安装的 libpq 库，另一库使用SSL支持进行编译，这也意味着您在sy上安装了OpenSSL干。可以自行编译，也可以根据喜好通过软件包安装。如今，一般而言，PostgreSQL的预编译版本通常都已启用SSL支持。

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.

即使libpq源位于服务器源内部，也可以单独编译并使用只是该库与其余库无关。
对此有一个疑问：如何下载，编译和编辑。在没有安装PostgreSQL的服务器上仅安装libpq源

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

这篇关于Postgres SSLMode值“ require”使用外部数据包装器未编译SSL支持时无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！