Core Configuration: postgresql.conf and pg_hba.conf

Core Configuration: postgresql.conf and pg_hba.conf

Why Configuration Files Matter

A freshly installed PostgreSQL server uses conservative default settings designed to run on modest hardware. In a production environment those defaults rarely match real workloads. Two files control the vast majority of server behavior: postgresql.conf sets performance, logging, and replication parameters; pg_hba.conf controls who may connect, from where, and how they must authenticate. Mastering both files is a foundational DBA skill tested by the EnterpriseDB PostgreSQL CE exam and expected by every employer who runs PostgreSQL.

Always keep a timestamped backup of both files before editing. A syntax error in either file can prevent the server from starting or accepting connections, which constitutes a production outage.

---

postgresql.conf — Server Configuration

postgresql.conf is a plain-text file located in the data directory (often `/etc/postgresql/<version>/main/` on Debian-based systems or `$PGDATA` on others). Parameters follow the format:

```

parameter_name = value # optional comment

```

Lines beginning with `#` are comments. The server reads this file at startup; many parameters can also be reloaded without a full restart using `pg_ctl reload` or the SQL command `SELECT pg_reload_conf();`. A smaller set of parameters (such as `max_connections`) require a full service restart because they affect shared-memory allocation.

Ten commonly tuned parameters:

| Parameter | What it controls | Reload or restart? |

|---|---|---|

| `shared_buffers` | Size of the shared memory buffer pool | Restart |

| `work_mem` | Memory per sort/hash operation per query node | Reload |

| `maintenance_work_mem` | Memory for VACUUM, CREATE INDEX, etc. | Reload |

| `max_connections` | Maximum simultaneous client connections | Restart |

| `effective_cache_size` | Planner estimate of OS cache available | Reload |

| `wal_level` | Amount of information written to WAL | Restart |

| `checkpoint_completion_target` | Fraction of checkpoint interval for writes | Reload |

| `logging_collector` | Enable file-based log capture | Restart |

| `log_min_duration_statement` | Log queries exceeding this duration (ms) | Reload |

| `max_wal_size` | Maximum WAL size before forced checkpoint | Reload |

shared_buffers is the single most impactful parameter. The PostgreSQL community recommends starting at roughly 25% of total RAM as a baseline, then tuning based on observed performance. On a server with 64 GB RAM that gives 16 GB. PostgreSQL also relies on the operating system's file-system cache, so allocating more than ~40% of RAM to shared_buffers is generally counterproductive.

work_mem is per sort or hash operation per query node, and a single complex query can allocate it multiple times. Set it conservatively: `Total RAM × a small fraction ÷ max_connections` is a useful starting heuristic. Too high a value with many concurrent users causes out-of-memory conditions.

After editing, confirm active values with `SHOW parameter_name;` or query `pg_settings`:

```sql

SELECT name, setting, unit FROM pg_settings

WHERE name IN ('shared_buffers','work_mem','max_connections');

```

---

pg_hba.conf — Client Authentication

pg_hba.conf (Host-Based Authentication) is read top-to-bottom; the first matching rule wins. Each rule is one line with five space-separated fields:

```

TYPE DATABASE USER ADDRESS METHOD [options]

```

TYPE values:

• `local` — Unix-domain socket connections (no ADDRESS field used)
• `host` — TCP/IP connections (plain or SSL)
• `hostssl` — TCP/IP, SSL required
• `hostnossl` — TCP/IP, SSL forbidden

Common authentication methods:

| Method | Description |

|---|---|

| `trust` | No password; accept unconditionally. Use only on loopback for tightly controlled setups. |

| `reject` | Always deny. Useful as an explicit block rule. |

| `ident` | Map OS username to PostgreSQL username via the ident protocol (local/host). |

| `peer` | Check OS username of the connecting process (local connections only). |

| `md5` | MD5-hashed password challenge. Widely supported; weaker than scram. |

| `scram-sha-256` | SCRAM-SHA-256 challenge-response. Current recommended method for password authentication. |

| `ldap` | Delegate authentication to an LDAP server. |

| `cert` | Require a valid client SSL certificate. |

A safe rule ordering for a typical production server places `reject` rules for sensitive databases near the top, followed by specific user/CIDR rules, and ends with a narrow default.

Example rules:

```

local all postgres peer

host appdb appuser 192.168.10.0/24 scram-sha-256

host legacydb legacyuser 10.0.5.50/32 md5

```

After editing pg_hba.conf, reload (no restart needed): `SELECT pg_reload_conf();`

---

Safety and Best Practices

• Version control your config files. Use git or a configuration management tool so every change is traceable.
• Never use `trust` for remote TCP/IP connections in any production or multi-user environment.
• Prefer `scram-sha-256` over `md5` for all new deployments; md5 is vulnerable to offline dictionary attacks.
• Test rules before going live. Attempt connections as the target user from the expected source IP to confirm the correct rule fires.
• Document every change in your configuration journal with the date, the before/after values, the reason, and the observed effect. This record is essential for audits and incident response.
• In shared server rooms, always follow site physical-access procedures; for server hardware maintenance, observe lockout/tagout (LOTO) procedures per OSHA standards before touching electrical components—though most DBA work is performed remotely, awareness of lab safety rules is required when working in hands-on lab environments.