Comments (3)
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.
from pg_shard.
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 ofhost
in allpg_hba.conf
files to only allow SSL connections - Set the environment variable
PGSSLMODE
torequire
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.
from pg_shard.
Thanks.
from pg_shard.
Related Issues (20)
- Allow IMMUTABLE functions in modification commands HOT 8
- DISTINCT and JSON field selection HOT 2
- Does pg_shard support user defined functions? HOT 3
- build issue HOT 2
- Support UNLOGGED tables HOT 2
- Schema support incomplete? HOT 1
- Support RETURNING in modification commands HOT 3
- sync-metadata does not support quoted column names HOT 1
- Zapier Test
- Default to CitusDB SELECT plan when within CitusDB
- Let CitusDB invalidate pg_shard's shard interval cache
- Make zero-shard SELECT consistent with CitusDB
- Support "UPSERT"
- Support prepared statements HOT 2
- 1.2.1 build error against PostgreSQL 9.4 HOT 5
- 1.2.1 build error against PostgreSQL 9.5 HOT 2
- cant read data from master [issue moved to pg_shard mailing list]
- Cannot read data in partition sets HOT 3
- Handling bigserial / auto-increment [Moved to mailing list] HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pg_shard.