PostgreSQL Connections Are Unencrypted by Default

For some reason I thought PostgreSQL connections are encrypted by default. Don't get me wrong, I would never expose a DB port directly to the internet. Only over an SSH tunnel or a VPN like Tailscale.

Still, I thought the engineering generation who created Postgres (roughly at the same time as SSH) built some basic encryption into the protocol by default just because they could.

Well, I was wrong. I intercepted the traffic of a fresh Postgres instance and the bytes were as readable as they can be.

In retrospect, this also makes sense in scenarios where the DB is used locally and you want all the performance without disabling something. Or when you use an SSH tunnel so that the machine doesn't encrypt the data twice.

Here are my findings:

Let's run this query and take a look at the traffic.

SELECT 'SECRET_PASSWORD_XYZ' as password, 'credit_card_1234' as data;

0000  51 00 00 00 4a 53 45 4c 45 43 54 20 27 53 45 43   Q...JSELECT 'SEC
0010  52 45 54 5f 50 41 53 53 57 4f 52 44 5f 58 59 5a   RET_PASSWORD_XYZ
0020  27 20 61 73 20 70 61 73 73 77 6f 72 64 2c 20 27   ' as password, '
0030  63 72 65 64 69 74 5f 63 61 72 64 5f 31 32 33 34   credit_card_1234
0040  27 20 61 73 20 64 61 74 61 3b 00                  ' as data;.

That's the complete SQL query in plaintext. The 51 byte at the start? That's ASCII for 'Q', the "Query" message type. Readable. By anyone on the network.

The response is equally exposed:

...
0040  00 00 00 13 53 45 43 52 45 54 5f 50 41 53 53 57   ....SECRET_PASSW
0050  4f 52 44 5f 58 59 5a 00 00 00 10 63 72 65 64 69   ORD_XYZ....credi
0060  74 5f 63 61 72 64 5f 31 32 33 34                  t_card_1234

There's my "secret" password and credit card number, transmitted in the clear.

The server ships with ssl = off as the default configuration. You have to explicitly enable it. If you're connecting to a database over a network (especially the internet) without SSL, you're broadcasting your queries and data to anyone who cares to listen.

What That SSL Option Does

Tools like Datagrip allow you to configure SSL. Basically this only changes the connection string we use to authenticate with the database.

When you see "Use SSL" or "SSL Mode" in your database client, this is what it controls:

The default sslmode is prefer. This is true for libpq (the C library), psycopg2, psycopg3, and by extension SQLAlchemy. This means the client tries to use SSL but also silently uses plaintext if SSL is not configured on the server-side.

Server-Side SSL Doesn't Force Client SSL

I also discovered something important: enabling SSL on the server doesn't force clients to use it. When I connected to my SSL-enabled server without requesting SSL, the connection was accepted and the server happily served data unencrypted.

PostgreSQL's ssl = on means "I can do SSL", not "I require SSL". To actually force SSL on the server side, you need to edit pg_hba.conf:

# This allows both SSL and non-SSL connections:
host    all    all    0.0.0.0/0    scram-sha-256

# This REQUIRES SSL (note: hostssl, not host):
hostssl all    all    0.0.0.0/0    scram-sha-256

PostgreSQL SSL configuration combinations

I recommend ssl = on, hostssl in pg_hba. Then you made sure on the server side that clients have to use SSL otherwise the connection is refused.

How Query Results Are Actually Formatted

Now that we were already intercepting traffic, I thought it would be interesting to see how query data is formatted. Let's look at how PostgreSQL sends data back. Here's a simple query result:

SELECT 1 as num, 'hello' as greeting;

First comes a RowDescription message (type 'T'):

0000  54 00 00 00 37 00 02 6e 75 6d 00 00 00 00 00 00   T...7..num......
0010  00 00 00 00 17 00 04 ff ff ff ff 00 00 67 72 65   .............gre
0020  65 74 69 6e 67 00 00 00 00 00 00 00 00 00 00 19   eting...........

This tells the client: "You're about to receive 2 columns. First is 'num', second is 'greeting'."

Then comes the actual data in a DataRow message (type 'D'):

0000  44 00 00 00 14 00 02 00 00 00 01 31 00 00 00 05   D..........1....
0010  68 65 6c 6c 6f                                    hello

Notice something? The integer 1 is sent as the ASCII character '1' (0x31), not as a binary integer. The string 'hello' is just the ASCII bytes. This is PostgreSQL's "text" format: human-readable, but not particularly efficient.

PostgreSQL does support a binary format where integers are sent as actual binary values, but it must be explicitly requested (usually through the extended query protocol). Most client libraries stick with text format because it's simpler to parse and debug.

Compression

Here's something that surprised me: PostgreSQL's wire protocol has no built-in compression.

I checked the server settings:

SELECT name, setting FROM pg_settings WHERE name LIKE '%compress%';
           name            | setting
---------------------------+---------
 default_toast_compression | pglz
 wal_compression           | off

These settings affect on-disk storage, not network transmission. The default_toast_compression compresses large values stored in TOAST tables. The wal_compression setting compresses WAL files. Neither touches the wire protocol.

If you're transferring large result sets, every byte goes over the network uncompressed. For most applications this isn't a big deal since query results are typically small. But if you're doing bulk data transfers, the lack of protocol-level compression means you're paying full bandwidth costs.

So What Should You Actually Do?

After intercepting my own database traffic, here's what I'd recommend:

Always Use SSL

If your database is on a different machine, especially across the internet, SSL is non-negotiable (unless you have an SSH tunnel or a VPN).

On managed databases (AWS RDS, Google Cloud SQL, Azure, Supabase, etc.), SSL is typically pre-configured. You just need to set sslmode=require on the client side to be sure when you don't have access to the configs.

On self-hosted PostgreSQL, you need to generate certificates and configure the server:

# Generate a self-signed certificate (for testing/internal use)
# The CN (Common Name) should match the hostname clients use to connect.
# This only matters if clients use sslmode=verify-full.
# For sslmode=require, any CN works.
openssl req -new -x509 -days 365 -nodes \
    -out server.crt -keyout server.key \
    -subj '/CN=db.example.com'

# Set permissions (PostgreSQL requires this)
chmod 600 server.key
chown postgres:postgres server.key server.crt

# Move to PostgreSQL data directory
mv server.crt server.key /var/lib/postgresql/data/

Then in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

For production, you'd want certificates from a real CA (Let's Encrypt, your organization's internal CA, etc.) rather than self-signed ones.

Verify Certificates in Production

sslmode=require ensures encryption but doesn't verify the server's identity. It'll accept any certificate, including one from an attacker. For production, use sslmode=verify-full with proper CA certificates:

postgresql://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/path/to/ca.crt

This prevents man-in-the-middle attacks by ensuring you're actually talking to your database server.

Wrapping Up

Building a wire protocol proxy and actually watching the bytes flow between client and server made one thing clear: PostgreSQL's defaults assume a trusted network. Queries go out in plaintext. Results come back in plaintext. Even authentication, while not exposing raw passwords, happens in the open.

SSL exists and works well, but both sides need to cooperate. The server must enable it, the client must request it, and if you want guarantees, you need hostssl on the server and sslmode=require on the client. The default prefer mode is a trap waiting to silently downgrade your connection.

As for the wire format itself: it's text-based, uncompressed, and refreshingly simple to parse. That simplicity made this experiment possible in an afternoon with a few hundred lines of Python.