# PostgreSQL security hardening (roles, least privilege, pg_hba.conf, SCRAM, TLS/verify-full, OAuth, v18)

> A security-hardening guide to protect PostgreSQL at production quality. Faithful to the official docs, it explains least-privilege role design that doesn't run as superuser, GRANT/REVOKE and default privileges, the public-schema pitfall (the PG15 change), predefined roles for monitoring, pg_hba.conf authentication design, SCRAM and MD5 deprecation, how to prevent man-in-the-middle attacks with TLS/sslmode=verify-full, and PostgreSQL 18's OAuth 2.0 authentication.

- Published: 2026-06-20
- Author: 友田 陽大
- Tags: PostgreSQL, セキュリティ, アーキテクチャ設計
- URL: https://tomodahinata.com/en/blog/postgresql-security-hardening-roles-privileges-ssl-scram-guide
- Category: PostgreSQL operations & reliability
- Pillar guide: https://tomodahinata.com/en/blog/postgresql-production-operations-guide

## Key points

- Don't run the app as a superuser or table owner. A superuser bypasses all permission checks (official). Create a least-privilege LOGIN role with only the needed DML.
- Privileges: the owner has all privileges. PUBLIC is granted no privileges by default. You need both the schema's USAGE and object privileges. Use ALTER DEFAULT PRIVILEGES so future tables get the correct privileges too.
- Since PostgreSQL 15, PUBLIC lost the CREATE privilege on the public schema. On an upgraded DB, run REVOKE CREATE ON SCHEMA public FROM PUBLIC.
- pg_hba.conf is decided by the first match from the top. Use scram-sha-256, force TLS with hostssl, and narrow by source IP/DB/user. trust is strictly forbidden. MD5 is deprecated in PG18.
- The client connects with sslmode=verify-full. require encrypts but doesn't verify the certificate = can't prevent a man-in-the-middle attack. PG18 adds OAuth 2.0 authentication.

---

Database security is not "something you add last" but "something you design first." Running the app as a superuser, opening `pg_hba.conf` with `trust`, and getting by with TLS at `require` — each of these is **the shortest path to a data leak.**

This article explains a **hardening checklist** to protect PostgreSQL in production, faithful to the official docs. From roles and least privilege to network authentication, password methods, and encryption (man-in-the-middle countermeasures). It also serves as material for buyers to judge "was this DB built securely." It's a deep dive of [production-operations guide §6](/blog/postgresql-production-operations-guide).

> **Rules for this article**: the specs of roles, privileges, authentication, and encryption, and PostgreSQL 18 changes, are based on the **PostgreSQL 18 official documentation (as of June 2026).** Multi-tenant authorization via **Row Level Security (RLS)** is detailed in the [database/RLS cluster](/blog/category/database), so this article doesn't touch it and concentrates on roles, network, and encryption.

---

## 1. Least privilege: don't run the app as a superuser

The first principle. **Don't use a superuser or table owner for the app's execution role.** The official warning:

> A database superuser **bypasses all permission checks**, except the right to log in. … This is a **dangerous privilege and should not be used carelessly**; it is best to **do most of your work as a role that is not a superuser.**

A role is cluster-wide (official: "roles are global across a database cluster"), and one with the `LOGIN` attribute functions as a "user," one without as a "group." The production answer is — create an **app-dedicated least-privilege LOGIN role** and give only the needed DML.

```sql
-- 1) オブジェクト所有者（マイグレーション用・通常運用では使わない）
CREATE ROLE app_owner NOLOGIN;

-- 2) アプリ実行用の最小権限ロール（必要なDMLだけ。所有者でもスーパーユーザーでもない）
CREATE ROLE app_user LOGIN PASSWORD '***';

-- 3) スキーマと既存テーブルへ、必要な権限だけ付与
GRANT USAGE ON SCHEMA app TO app_user;                  -- スキーマのUSAGEは必須
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user; -- serial/identity を使うなら

-- 4) 将来作られるテーブルにも自動で同じ権限を（重要：付け忘れを防ぐ）
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
```

> Even if the app is hijacked, if `app_user` can't `DROP TABLE` or `CREATE ROLE`, the damage is confined to "data that role can touch." This is **damage containment via least privilege.**

---

## 2. Privilege design: GRANT, default privileges, the public-schema trap

### Owner, PUBLIC, USAGE

Official: "**the owner of an object inherently has all privileges.**" A new object is owned by its creator with all privileges. Pass other roles **only what's needed** via `GRANT`.

The special pseudo-role **`PUBLIC`** means "all roles." Official: "for tables, columns, sequences, schemas, etc., **PUBLIC is granted no privileges by default.**" What matters is that **you need not just table privileges but the schema's `USAGE` too** — access works only with both.

### ⚠ The public-schema trap (the PostgreSQL 15 change)

A historical landmine. Official:

> **Since PostgreSQL 15**, the default configuration supports this usage. **In earlier versions (or a DB upgraded from an old version), you need to remove PUBLIC's `CREATE` privilege from the `public` schema** (run `REVOKE CREATE ON SCHEMA public FROM PUBLIC`).

Historically, "all users could create objects in the `public` schema," and the official docs declare "**this is never a secure pattern.**"

```sql
-- 旧版からアップグレードしたDBでは必ず実行（PG15+で新規作成したDBは既定で対策済み）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- さらに厳格にするなら、public への接続時USAGEも絞る
-- REVOKE ALL ON SCHEMA public FROM PUBLIC;
```

### Predefined roles for monitoring/analytics

Giving a superuser to a "read-only analyst" or "monitoring agent" is excessive. Use the official **predefined roles.**

| Role | Privileges |
| --- | --- |
| `pg_read_all_data` | **SELECT-equivalent** on all tables/views/sequences + USAGE on all schemas (doesn't bypass RLS) |
| `pg_monitor` | Read of monitoring views/functions (includes `pg_read_all_settings` + `pg_read_all_stats`, etc.) |
| `pg_read_all_stats` | View all `pg_stat_*` |

```sql
-- アナリストは読み取り専用ロールで（スーパーユーザー不要）
GRANT pg_read_all_data TO analyst;
-- 監視エージェントは pg_monitor で
GRANT pg_monitor TO monitoring_agent;
```

---

## 3. Network authentication: pg_hba.conf

What decides "who, from where, to which DB, with which method" can connect is `pg_hba.conf`. The official most-important rule:

> The **first matching record** is used for authentication. There is **no "fall-through" or "backup"**: if a record is chosen and authentication fails, subsequent records are not considered. If no record matches, **access is denied.**

So **order is everything**, evaluated top-down and decided at the first match. Hardening points:

```text
# TYPE     DATABASE   USER       ADDRESS          METHOD
# ローカルの管理用ソケットのみ peer（OSユーザーと一致）
local      all        postgres                    peer
# アプリは TLS 必須（hostssl）＋ SCRAM ＋ 送信元を限定
hostssl    appdb      app_user   10.0.0.0/24      scram-sha-256
# 上記に一致しない全接続を明示的に拒否（デフォルト拒否）
host       all        all        0.0.0.0/0        reject
```

- Use **`hostssl`** and **don't match unless it's a TLS connection** (`host` matches both SSL/non-SSL).
- Authentication is **`scram-sha-256`.**
- **`trust` is strictly forbidden** — official: "**allow the connection unconditionally.** … no password or authentication is needed, and anyone can log in as any user." Don't use it except on a trusted local socket.
- **Narrow as much as possible** by source IP (CIDR), DB, and user.

---

## 4. Passwords: SCRAM, MD5 is deprecated

Official: the default of `password_encryption` is **`scram-sha-256`.** SCRAM is "**the most secure** of those currently offered." Meanwhile, **MD5 is weak** — official: "the MD5 hash algorithm is **not considered secure against determined attacks** today."

And in PostgreSQL **18, MD5 became deprecated** (official release notes):

> **Deprecate MD5 password authentication.** … Support will be **removed in a future major version.** `CREATE ROLE` and `ALTER ROLE` **emit a deprecation warning** when setting an MD5 password.

```sql
-- パスワードは SCRAM で保存される（既定）。MD5 ロールが残っていれば SCRAM へ移行
SET password_encryption = 'scram-sha-256';   -- 既定
ALTER ROLE app_user PASSWORD '***';          -- 設定し直すと SCRAM ハッシュで保存される

-- MD5 のまま残っているロールを洗い出す
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
```

---

## 5. Encryption: prevent man-in-the-middle attacks with sslmode=verify-full

Communication encryption is **two wheels of server config and client config.** Enable the server with `ssl = on` (needs `server.crt`/`server.key`).

What's decisively important is the **client's `sslmode`.** As the official table shows, encryption and **impersonation (man-in-the-middle) prevention** are different things.

| sslmode | Sniffing prevention | MITM prevention | What it verifies |
| --- | --- | --- | --- |
| `require` | ✅ | **❌** | Encryption only. **Doesn't verify the certificate** |
| `verify-ca` | ✅ | Depends on CA | Verifies the certificate chain up to the root CA |
| `verify-full` | ✅ | **✅** | The above + verifies **the host name matches the certificate** |

Official: "if a third party can alter data in transit, they can **impersonate the server.** SSL prevents this with certificate verification." `require` only encrypts and **can't prevent impersonation.** A production client connects with **`verify-full`** + the root CA.

```bash
# アプリの接続文字列：verify-full でなりすましを防ぐ（require では不十分）
DATABASE_URL="postgresql://app_user@db.internal/appdb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/db-ca.pem"
```

```ts
// node-postgres でも明示的に検証を有効化（rejectUnauthorized を切らない）
import { Pool } from "pg";
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // sslmode=verify-full を含む
  ssl: { rejectUnauthorized: true, ca: process.env.DB_CA_CERT }, // 証明書を必ず検証
});
```

> **What you must not do**: "just get it working" with `rejectUnauthorized: false` or `sslmode=require`. That's the worst half-measure — encrypting yet **allowing impersonation.**

---

## 6. PostgreSQL 18 security enhancements

From the official release notes.

- **OAuth 2.0 authentication**: the `oauth` method is added to `pg_hba.conf`. You can authorize/authenticate with a third-party IdP (OAuth 2.0). Useful for centralized identity management (a `--with-libcurl` build is needed).
- **MD5 deprecation** (§4).
- **SCRAM pass-through**: the client's SCRAM authentication can be carried over to `postgres_fdw`/`dblink` (`use_scram_passthrough`) — you avoid storing FDW credentials in the DB.
- **TLS**: `ssl_ecdh_curve` was renamed to `ssl_groups` (multiple curves specifiable, with X25519 in the default). **OpenSSL below 1.1.1 is end-of-support.**
- Privileges: `pg_get_acl()`, `ALTER DEFAULT PRIVILEGES` supports large objects, and the predefined role `pg_signal_autovacuum_worker` was added.

---

## 7. Hardening checklist

1. Run **the app as a least-privilege LOGIN role** (don't use superuser/owner).
2. Auto-grant correct privileges to future tables with **`ALTER DEFAULT PRIVILEGES`.**
3. On an old DB, **`REVOKE CREATE ON SCHEMA public FROM PUBLIC`** (match the PG15+ default).
4. Read/monitoring with **`pg_read_all_data` / `pg_monitor`** (don't hand out a superuser).
5. `pg_hba.conf` is **default-deny**, with the first-match order as intended, **`hostssl` + `scram-sha-256`**, narrowed by source IP/DB/user, **`trust` forbidden.**
6. Passwords with **SCRAM.** **Migrate remaining MD5 roles** (deprecated in PG18).
7. The client uses **`sslmode=verify-full`** + the root CA (`require` is not OK).
8. If you can, **OAuth 2.0** (PG18) for centralized identity management, and ensure OpenSSL ≥ 1.1.1.
9. Row-level multi-tenant separation with **RLS** ([database/RLS cluster](/blog/category/database)).

---

## 8. Summary

- **Least privilege** is the starting point. Don't run the app as superuser/owner. Prevent omissions with `ALTER DEFAULT PRIVILEGES`.
- Watch the **public-schema trap** (the PG15 change) — `REVOKE CREATE ... FROM PUBLIC` on an old DB.
- **`pg_hba.conf` is the first match.** Default-deny, `hostssl`, `scram-sha-256`, narrowed source. `trust` strictly forbidden.
- **Use SCRAM and drop MD5** (deprecated in PG18).
- Prevent impersonation with **`sslmode=verify-full`** (`require` is just encryption, insufficient).
- **PG18 adds OAuth 2.0 authentication.**

That completes a round of PostgreSQL's **operations/reliability series.** Starting from the [production-operations guide](/blog/postgresql-production-operations-guide), combined with [connection pooling](/blog/postgresql-connection-pooling-pgbouncer-serverless-guide), [backup/PITR](/blog/postgresql-backup-pitr-pg-dump-wal-archiving-guide), [replication/HA](/blog/postgresql-streaming-replication-high-availability-failover-guide), [logical replication/upgrade](/blog/postgresql-logical-replication-cdc-zero-downtime-upgrade-guide), and [non-stop DDL](/blog/postgresql-zero-downtime-schema-migration-lock-safe-ddl-guide), design a PostgreSQL that satisfies "don't break, don't stop, don't clog, protect, evolve."

---

### References (PostgreSQL 18 official documentation)

- [Chapter 22. Database Roles](https://www.postgresql.org/docs/18/user-manag.html)
- [5.8. Privileges (GRANT / default privileges)](https://www.postgresql.org/docs/18/ddl-priv.html)
- [22.5. Predefined Roles](https://www.postgresql.org/docs/18/predefined-roles.html)
- [21.1. The pg_hba.conf File](https://www.postgresql.org/docs/18/auth-pg-hba-conf.html)
- [21.5. Password Authentication (SCRAM)](https://www.postgresql.org/docs/18/auth-password.html)
- [19.9. Secure TCP/IP Connections with SSL / 34.19. SSL Support (sslmode)](https://www.postgresql.org/docs/18/libpq-ssl.html)