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

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.

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, 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.

-- 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."

-- 旧版からアップグレードした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.

-- アナリストは読み取り専用ロールで（スーパーユーザー不要）
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:

# 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.

-- パスワードは 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.

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.

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

// 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).

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, combined with connection pooling, backup/PITR, replication/HA, logical replication/upgrade, and non-stop DDL, 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
• 5.8. Privileges (GRANT / default privileges)
• 22.5. Predefined Roles
• 21.1. The pg_hba.conf File
• 21.5. Password Authentication (SCRAM)
• 19.9. Secure TCP/IP Connections with SSL / 34.19. SSL Support (sslmode)