---
title: "securing postgresql connections: a practical guide to ssl, authentication, and access control"
author: "pilput"
canonical: "https://pilput.net/pilput/securing-postgresql-connections-a-practical-guide-to-ssl-authentication-and-access-control"
published: "2026-08-10T15:01:44.414933Z"
updated: "2026-08-10T15:02:54.367195Z"
description: "postgresql is one of the most trusted open-source databases in the world, powering everything from side projects to massive production systems. but here's the ..."
---
# securing postgresql connections: a practical guide to ssl, authentication, and access control

postgresql is one of the most trusted open-source databases in the world, powering everything from side projects to massive production systems. but here's the thing: **a database is only as safe as its connection layer**. if your traffic travels in plain text, or your authentication rules are too permissive, even the strongest password won't save you.

in this guide, we'll walk through three essential layers of postgresql security — **ssl/tls encryption**, **authentication**, and **access control** — with practical, copy-paste-ready examples. whether you're a student deploying your first full stack app or a devops engineer hardening production infrastructure, you'll leave with a setup you can trust.

## why postgresql connection security matters

when your application talks to postgresql, everything — queries, results, and sometimes passwords — travels over the network. without protection, anyone sitting between your app and your database (a compromised router, a rogue container, a public wi-fi sniffer) can read or modify that traffic. this is called a **man-in-the-middle (mitm) attack**, and it's more common than you'd think.

good connection security rests on three pillars:

- **encryption (ssl/tls):** scrambles data in transit so eavesdroppers see only noise.

- **authentication:** verifies _who_ is connecting, using methods like scram-sha-256 or certificates.

- **access control:** limits _what_ an authenticated user is allowed to do inside the database.

let's tackle each one step by step.

## layer 1: encrypting connections with ssl/tls

### what ssl/tls actually does for you

ssl (more accurately, its modern successor **tls**) creates an encrypted tunnel between your client and the postgresql server. it gives you two guarantees:

- **confidentiality:** nobody can read your queries or data while they travel across the network.

- **server identity verification:** your client can confirm it's talking to the real database server, not an impostor.

### step 1: enable ssl on the server

first, you need a certificate and a private key. for learning or internal testing, a self-signed certificate works fine:

```
openssl req -new -x509 -days 365 -nodes -text \
  -out server.crt \
  -keyout server.key \
  -subj "/cn=db.example.com"

chmod 600 server.key
chown postgres:postgres server.key server.crt
```

for production, use a certificate signed by a trusted **certificate authority (ca)** — services like let's encrypt or your cloud provider's managed certificates are great options.

next, edit `postgresql.conf`:

```
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file = '/etc/postgresql/server.key'
# optionally require tls 1.2 or higher
ssl_min_protocol_version = 'tlsv1.2'
```

restart postgresql to apply the changes:

```
sudo systemctl restart postgresql
```

### step 2: understand the client sslmode options

enabling ssl on the server is only half the story. your **client** decides how strictly to enforce encryption via the `sslmode` parameter. this is where many developers accidentally leave the door open.

      sslmode
      behavior
      recommended?

      `disable`
      never use ssl. plain text only.
      ❌ never

      `allow`
      try plain text first, ssl if the server insists.
      ❌ no

      `prefer`
      try ssl first, but silently fall back to plain text. **(default!)**
      ⚠️ risky

      `require`
      always use ssl, but don't verify the certificate.
      ✅ minimum

      `verify-ca`
      ssl + verify the server certificate is signed by a trusted ca.
      ✅ better

      `verify-full`
      ssl + verify the ca _and_ that the hostname matches the certificate.
      ✅ best

**key takeaway:** the default `prefer` mode will happily downgrade to an unencrypted connection. always set `sslmode` explicitly — use `verify-full` whenever possible.

### step 3: connect with ssl enforced

with `psql`:

```
psql "host=db.example.com dbname=mydb user=appuser sslmode=verify-full sslrootcert=/path/to/ca.crt"
```

or via a connection uri (common in full stack apps):

```
postgresql://appuser:secret@db.example.com:5432/mydb?sslmode=verify-full&sslrootcert=/path/to/ca.crt
```

you can verify your connection is encrypted by running this query after connecting:

```
select ssl, version, cipher from pg_stat_ssl where pid = pg_backend_pid();
```

if `ssl` shows `t` (true), congratulations — your traffic is encrypted! 🎉

## layer 2: authentication with pg_hba.conf

### meet the gatekeeper: pg_hba.conf

the file `pg_hba.conf` (hba stands for **host-based authentication**) controls who can connect, from where, to which databases, and how they must prove their identity. every incoming connection is checked against this file **from top to bottom**, and the first matching rule wins.

a typical line looks like this:

```
# type   database   user      address          method
hostssl  all        all       0.0.0.0/0        scram-sha-256
```

let's break it down:

- **type:** `local` (unix socket), `host` (tcp, ssl or not), `hostssl` (tcp with ssl only), or `hostnossl` (tcp without ssl only).

- **database:** which databases this rule applies to (`all`, a specific name, or comma-separated list).

- **user:** which roles the rule matches.

- **address:** allowed client ip ranges in cidr notation.

- **method:** how the user must authenticate.

### authentication methods, from worst to best

- `trust` — **no password at all.** anyone who can reach the server gets in. only acceptable for local development on your own machine.

- `password` — sends the password in **plain text**. acceptable only over an ssl-encrypted connection, but still not ideal.

- `md5` — older hashing method. now considered **weak** and vulnerable to modern cracking techniques. avoid it.

- `scram-sha-256` — the current gold standard. passwords are never sent or stored in a reversible form, and it's resistant to replay attacks. **use this.**

- `peer` — uses the operating system user name for `local` connections. great for admin tasks on the server itself.

- `cert` — authenticates via client ssl certificates instead of passwords. excellent for service-to-service communication.

### a secure, real-world pg_hba.conf example

```
# type     database   user           address            method

# 1. local admin access via os user (no network involved)
local      all        postgres                          peer

# 2. application servers: ssl required, strong password auth
hostssl    mydb       appuser        10.0.1.0/24        scram-sha-256

# 3. read-only analytics user from a specific ip
hostssl    mydb       analytics_ro   203.0.113.50/32    scram-sha-256

# 4. reject everything else (implicit, but good to remember:
#    if no rule matches, the connection is refused)
```

a few things to notice:

- we use `hostssl` instead of `host`, so **unencrypted connections are rejected outright** — no accidental downgrades.

- ip ranges are as **specific as possible**. avoid `0.0.0.0/0` unless you truly need connections from anywhere.

- every rule uses `scram-sha-256` for network connections.

after editing, reload the configuration (no full restart needed):

```
sudo systemctl reload postgresql
```

### don't forget: switch password encryption to scram

even if `pg_hba.conf` says `scram-sha-256`, existing passwords may still be stored as md5. fix this by setting the encryption method and resetting passwords:

```
-- in postgresql.conf:
-- password_encryption = scram-sha-256

alter role appuser with password 'a-long-random-password';
```

## layer 3: access control with roles and privileges

authentication gets users through the door. **access control** decides what they can touch once inside. the golden rule here is the **principle of least privilege**: give every role only the permissions it absolutely needs — nothing more.

### never let your app use the superuser

this is one of the most common mistakes beginners make. the `postgres` superuser can drop databases, alter roles, and bypass all permission checks. if your application code has a sql injection vulnerability and it's connected as superuser, the attacker owns everything. instead, create dedicated roles:

```
-- a role for the application (read + write, but no schema changes)
create role app_user with login password 'strong-random-password';

grant connect on database mydb to app_user;
grant usage on schema public to app_user;
grant select, insert, update, delete on all tables in schema public to app_user;

-- make sure future tables get the same permissions automatically
alter default privileges in schema public
  grant select, insert, update, delete on tables to app_user;
```

and for a reporting tool that only needs to read data:

```
create role analytics_ro with login password 'another-strong-password';

grant connect on database mydb to analytics_ro;
grant usage on schema public to analytics_ro;
grant select on all tables in schema public to analytics_ro;

alter default privileges in schema public
  grant select on tables to analytics_ro;
```

### bonus: row-level security (rls)

for multi-tenant applications, postgresql's **row-level security** lets you restrict which _rows_ a user can see — enforced by the database itself, not just your application code:

```
alter table orders enable row level security;

create policy tenant_isolation on orders
  using (tenant_id = current_setting('app.current_tenant')::int);
```

now each request simply sets its tenant id, and postgresql filters rows automatically:

```
set app.current_tenant = '42';
select * from orders;  -- only returns rows where tenant_id = 42
```

this is a powerful safety net — even if your application code has a bug, the database won't leak other tenants' data.

## putting it all together: connecting from your code

### node.js (full stack / javascript)

```
const fs = require('fs');
const { client } = require('pg');

const client = new client({
  host: 'db.example.com',
  port: 5432,
  database: 'mydb',
  user: 'app_user',
  password: process.env.db_password, // never hardcode passwords!
  ssl: {
    rejectunauthorized: true,
    ca: fs.readfilesync('/path/to/ca.crt').tostring(),
  },
});

await client.connect();
const res = await client.query('select ssl from pg_stat_ssl where pid = pg_backend_pid()');
console.log('ssl active:', res.rows[0].ssl);
await client.end();
```

### python

```
import psycopg2

conn = psycopg2.connect(
    host="db.example.com",
    dbname="mydb",
    user="app_user",
    password=os.environ["db_password"],
    sslmode="verify-full",
    sslrootcert="/path/to/ca.crt",
)

cur = conn.cursor()
cur.execute("select ssl from pg_stat_ssl where pid = pg_backend_pid();")
print("ssl active:", cur.fetchone()[0])
```

notice the pattern in both examples: passwords come from **environment variables**, ssl verification is **enforced**, and we confirm encryption after connecting.

## common mistakes to avoid

- **using `trust` authentication on a networked server.** this is an open door for anyone who can reach your port.

- **leaving `sslmode` at the default `prefer`.** always set `require` at minimum, ideally `verify-full`.

- **sticking with `md5` password hashing.** upgrade to `scram-sha-256` and reset your passwords.

- **connecting your app as the `postgres` superuser.** create least-privilege roles instead.

- **opening port 5432 to the entire internet.** use firewalls, security groups, or vpns to restrict network access — database security starts before postgresql even sees the connection.

- **committing credentials to git.** use environment variables or a secrets manager (like aws secrets manager, hashicorp vault, or doppler).

## your postgresql security checklist

before you ship, run through this quick list:

- ✅ ssl enabled in `postgresql.conf` with tls 1.2+

- ✅ `pg_hba.conf` uses `hostssl` + `scram-sha-256` for all network connections

- ✅ clients connect with `sslmode=verify-full` (or at least `require`)

- ✅ no `trust` or `md5` methods for anything reachable over the network

- ✅ applications use dedicated, least-privilege roles — never the superuser

- ✅ ip address ranges in `pg_hba.conf` are as narrow as possible

- ✅ passwords live in environment variables or a secrets manager, never in code

- ✅ port 5432 is protected by a firewall or security group

## conclusion

securing postgresql connections isn't a single switch — it's a layered approach. **ssl/tls** protects your data in transit, **scram-sha-256 authentication** verifies who's knocking, and **role-based access control** limits the damage if credentials ever leak. the best part? everything in this guide can be set up in an afternoon, and most of it is "configure once, protect forever."

start with the checklist above, apply it to a test database, and then roll it out to production. your future self — and your users — will thank you. happy coding! 🐘🔒