Does pg_shard encrypt the messages between nodes?

[edeast]

Or is there a recommended way of dealing with a pgsql cluster in a vps?

[jasonmp85]

pg_shard should use the prefer (default) setting for the sslmode connection parameter when connecting to worker nodes, which first attempts an SSL connection and falls back to plaintext if a secure connection is not available.

By ensuring workers only accept an SSL connection, it should be possible to build a cluster that always uses SSL to talk to worker nodes, though the cluster will silently connect to non-SSL nodes as well. At the moment there is no configuration to reject plaintext connections, so you'd need to be careful that your workers only accept SSL.

[jasonmp85]

I hadn't closed this bug yet because I wanted to add specific instructions to explain how to ensure encryption. There are two things you'll want to do: the first is sufficient, but for full safety you'll probably want to do both.

Ensuring Encryption

The simplest change (which is sufficient to ensure that workers require encryption when talking to them) is to use the hostssl field in your workers pg_hba.conf files. So if your configuration says something like:

host    all             all             127.0.0.1/32            trust

… then you'll want to change it to:

hostssl    all             all             127.0.0.1/32            trust

According to the documentation:

This record matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption.

Since pg_shard does not specify a setting for the sslmode libpq connection parameter when connecting to worker nodes, it uses the default setting of prefer, defined as:

first try an SSL connection; if that fails, try a non-SSL connection

So (assuming SSL is enabled on the server), making the above change will reject all non-SSL connections. And since pg_shard will attempt with SSL before falling back to non-SSL, you know that your connections will succeed on the SSL attempt and be encrypted.

Extra Credit

You can override the sslmode default by setting the PGSSLMODE environment variable when you start any PostgreSQL program. If you're starting PostgreSQL manually, just ensure the environment variable is set; if you're on a platform with e.g. pg_ctlcluster, there is often an environment file, located alongside your other configuration files (like pg_hba.conf or postgresql.conf) in a file named environment.

Placing key-value pairs in this file will cause those environment variables to be set for the server process. For instance, if we already made the above change to pg_hba.conf, we can verify that plaintext connections are rejected by adding PGSSLMODE = disable to the master's environment file.

SELECT * FROM distributed_table;
-- WARNING:  08006: Connection failed to localhost:5433
-- DETAIL:  Remote message: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "jason", database "postgres", SSL off
-- LOCATION:  ReportRemoteError, connection.c:230
-- ERROR:  XX000: could not receive query results
-- LOCATION:  ExecuteMultipleShardSelect, pg_shard.c:1456

Since we set up the workers to require SSL, telling the master to disable SSL connections results in an error. So we know this environment variable is honored by pg_shard. The default value of prefer is nice, but setting it to require is best. In this way, libpq connections will require SSL by default unless a parameter is passed to override the new default of require.

Summary

Here's what we covered:

• Use hostssl instead of host in all pg_hba.conf files to only allow SSL connections
• Set the environment variable PGSSLMODE to require on all server processes to change the default libpq behavior from preferring SSL connections (with plaintext fallback) to requiring them (unless overridden directly by calling code)

With these two changes you can ensure your machines neither attempt nor accept plaintext connections.

[edeast]

Thanks.