🐘 PostgreSQL Learning Series | Part 5

PostgreSQL Configuration Files: postgresql.conf, pg_hba.conf & pg_ident.conf

If you are coming from SQL Server, PostgreSQL configuration can initially feel quite different.

In SQL Server, many server-level settings are managed through SSMS, sp_configure, SQL Server Configuration Manager, startup parameters, or operating-system settings.

PostgreSQL is much more configuration-file-oriented. Three files are especially important:

postgresql.conf
pg_hba.conf
pg_ident.conf

postgresql.conf controls how PostgreSQL behaves.
pg_hba.conf controls client authentication rules.
pg_ident.conf provides username mapping when required by certain authentication methods.

1. The Big Picture

                    PostgreSQL Instance
                           |
          +----------------+----------------+
          |                |                |
          v                v                v
  postgresql.conf      pg_hba.conf      pg_ident.conf
          |                |                |
     Server Behavior    Authentication    User Mapping
     Memory             Client IP         OS User
     Connections        Database             |
     WAL                User                 v
     Logging            Auth Method       PG Role
     Performance

File	Primary Purpose
`postgresql.conf`	PostgreSQL server configuration
`pg_hba.conf`	Client connection and authentication rules
`pg_ident.conf`	External/OS username to PostgreSQL role mapping

2. Where Are the PostgreSQL Configuration Files?

Do not assume the location of PostgreSQL configuration files. Ask PostgreSQL itself.

Find postgresql.conf

SHOW config_file;

Find pg_hba.conf

SHOW hba_file;

Find pg_ident.conf

SHOW ident_file;

Find Data Directory

SHOW data_directory;

You can retrieve all four together:

SELECT
    current_setting('config_file') AS config_file,
    current_setting('hba_file') AS hba_file,
    current_setting('ident_file') AS ident_file,
    current_setting('data_directory') AS data_directory;

TAZ DBA Tip:
Always identify the active configuration file before editing it. On servers containing multiple PostgreSQL clusters, editing the wrong configuration file is an easy mistake to make.

3. Understanding postgresql.conf

postgresql.conf is the primary PostgreSQL server configuration file.

It controls settings related to:

Network connectivity
Memory
Client connections
Query execution
WAL
Checkpoints
Logging
Replication
Autovacuum
Background processes
Resource consumption
Performance

Think of postgresql.conf as one of the main control centers of your PostgreSQL instance.

4. Structure of postgresql.conf

A typical configuration entry looks like:

parameter = value

Example:

max_connections = 100
shared_buffers = 2GB
work_mem = 16MB

Comments normally start with #.

#max_connections = 100

5. Important PostgreSQL Configuration Parameters

Network Settings

listen_addresses = 'localhost'
port = 5432
max_connections = 100

listen_addresses

This setting defines which network interfaces PostgreSQL listens on.

Local connections only:

listen_addresses = 'localhost'

Specific server address:

listen_addresses = '192.168.1.20'

Multiple interfaces:

listen_addresses = 'localhost,192.168.1.20'

All available interfaces:

listen_addresses = '*'

Important:
Setting listen_addresses = '*' does not automatically mean that everyone can connect.

listen_addresses determines where PostgreSQL listens.
pg_hba.conf determines which matching clients can authenticate.

6. PostgreSQL Memory Configuration

Important memory-related parameters include:

shared_buffers
work_mem
maintenance_work_mem
effective_cache_size

shared_buffers

shared_buffers = 2GB

This defines memory dedicated to PostgreSQL's shared buffer cache.

Do not blindly copy a memory percentage from another server. Consider physical RAM, workload, concurrent connections, operating-system cache, query complexity, and other applications running on the server.

work_mem

work_mem = 16MB

This setting is particularly important because work_mem can be consumed by individual sort or hash operations within queries.

DBA Warning:
Do not assume that setting work_mem = 16MB limits the entire PostgreSQL server to 16 MB of query-processing memory. Multiple operations and concurrent sessions can use their own allocations.

maintenance_work_mem

maintenance_work_mem = 512MB

Used by maintenance operations such as:

VACUUM
CREATE INDEX
ALTER TABLE ADD FOREIGN KEY

effective_cache_size

effective_cache_size = 6GB

An important distinction is that PostgreSQL does not allocate this amount of memory. It is an estimate used by the query planner when evaluating how much data may already be cached.

7. WAL and Checkpoint Settings

Important parameters include:

wal_level
max_wal_size
min_wal_size
checkpoint_timeout
checkpoint_completion_target

For example:

max_wal_size = 2GB
checkpoint_timeout = 5min

If you are coming from SQL Server, WAL can be conceptually compared with the SQL Server transaction log, although their internal architecture and implementation are not identical.

8. Logging Configuration

Useful logging parameters include:

logging_collector
log_directory
log_filename
log_min_duration_statement
log_connections
log_disconnections

Example:

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

To log slow statements taking at least one second:

log_min_duration_statement = 1000

9. How Do I Check PostgreSQL Settings?

SHOW max_connections;

SHOW shared_buffers;

SHOW work_mem;

SHOW ALL;

10. The Powerful pg_settings View

For a PostgreSQL DBA, pg_settings is one of the most useful system views.

SELECT
    name,
    setting,
    unit,
    context,
    source
FROM pg_settings
ORDER BY name;

To investigate one parameter:

SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name = 'shared_buffers';

This helps answer an important DBA question: Where did this configuration value actually come from?

11. Reload vs Restart

Reload Configuration

A reload tells PostgreSQL to reread configuration parameters that can be changed without restarting the server.

SELECT pg_reload_conf();

From the operating system, depending on the installation:

pg_ctl reload

On many systemd-based Linux systems:

sudo systemctl reload postgresql

Restart PostgreSQL

Some parameters require the PostgreSQL server to be restarted.

sudo systemctl restart postgresql

Check parameters waiting for a restart:

SELECT
    name,
    setting,
    pending_restart
FROM pg_settings
WHERE pending_restart = true;

12. SET vs ALTER SYSTEM

Session-Level Change

SET work_mem = '64MB';

SHOW work_mem;

This does not mean that you permanently changed the PostgreSQL configuration for every session.

ALTER SYSTEM

ALTER SYSTEM SET work_mem = '32MB';

SELECT pg_reload_conf();

SHOW work_mem;

Settings made through ALTER SYSTEM are stored separately, normally in:

postgresql.auto.conf

Check where a value originated:

SELECT
    name,
    setting,
    source,
    sourcefile
FROM pg_settings
WHERE name = 'work_mem';

Reset the parameter:

ALTER SYSTEM RESET work_mem;

SELECT pg_reload_conf();

13. Understanding pg_hba.conf

Now we come to one of the most important configuration files for PostgreSQL administrators:

pg_hba.conf

HBA = Host-Based Authentication

The file helps PostgreSQL determine:

WHO is connecting?
        +
FROM WHERE?
        +
TO WHICH database?
        +
AS WHICH PostgreSQL user?
        +
USING WHICH authentication method?

14. Structure of pg_hba.conf

TYPE    DATABASE    USER    ADDRESS         METHOD

Example:

host    tazlab      ajit    192.168.1.0/24  scram-sha-256

This means:

Connection Type : TCP/IP
Database        : tazlab
PostgreSQL User : ajit
Client Network  : 192.168.1.0/24
Authentication  : SCRAM-SHA-256

15. Connection Types

local

local   all   all   peer

Used for local Unix-domain-socket connections.

host

host    all    all    192.168.1.0/24    scram-sha-256

Used for matching TCP/IP connections.

hostssl

hostssl    all    all    10.10.10.0/24    scram-sha-256

Matches TCP/IP connections using SSL.

hostnossl

hostnossl    all    all    10.10.10.0/24    scram-sha-256

Matches TCP/IP connections that are not using SSL.

16. The Most Important pg_hba.conf Rule

Remember this:

The first matching pg_hba.conf rule wins.

Consider:

host    all       all    192.168.1.0/24    reject
host    tazlab    ajit   192.168.1.0/24    scram-sha-256

The first rule already matches clients from that network. The second rule therefore does not operate as a fallback authentication rule.

Rule ordering matters.

Specific Rules
      |
      v
Broader Rules
      |
      v
Catch-All Rules

17. Understanding CIDR Addresses

Example:

192.168.1.0/24

This represents an IPv4 subnet containing addresses such as:

192.168.1.0
...
192.168.1.255

To represent a single IPv4 host:

192.168.1.50/32

Example:

host    tazlab    ajit    192.168.1.50/32    scram-sha-256

18. Important Authentication Methods

scram-sha-256

host    all    all    192.168.1.0/24    scram-sha-256

SCRAM-SHA-256 is the preferred modern password-authentication choice for new PostgreSQL configurations requiring password authentication.

trust

host    all    all    192.168.1.0/24    trust

Security Warning:
Do not casually use trust in production simply to solve a connection problem. A matching client does not have to provide normal password proof under a trust rule.

peer

local    all    postgres    peer

Commonly used for Unix-domain-socket authentication based on operating-system identity.

reject

host    all    all    10.10.20.0/24    reject

Explicitly rejects matching connections.

19. Practical pg_hba.conf Example

# TYPE  DATABASE    USER        ADDRESS             METHOD

# Local connections
local   all         all                             peer

# Ajit's TAZLab account
host    tazlab      ajit        192.168.1.50/32     scram-sha-256

# Application servers
host    tazlab      tazapp      192.168.10.0/24     scram-sha-256

# Explicit restriction
host    all         all         10.20.30.0/24       reject

20. Validate pg_hba.conf

PostgreSQL provides the extremely useful system view:

SELECT *
FROM pg_hba_file_rules;

A cleaner DBA query:

SELECT
    rule_number,
    file_name,
    line_number,
    type,
    database,
    user_name,
    address,
    netmask,
    auth_method,
    error
FROM pg_hba_file_rules
ORDER BY rule_number;

Check only problematic entries:

SELECT *
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

21. PostgreSQL Connection Troubleshooting Flow

Step 1: Is PostgreSQL running?

systemctl status postgresql

Step 2: Is PostgreSQL listening on port 5432?

SHOW port;

Linux:

ss -ltnp | grep 5432

Step 3: Check listen_addresses

SHOW listen_addresses;

Step 4: Find the active pg_hba.conf

SHOW hba_file;

Step 5: Check the rules

SELECT *
FROM pg_hba_file_rules
ORDER BY rule_number;

Step 6: Check for configuration errors

SELECT *
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

Step 7: Test the Connection

psql -h 192.168.1.20 -p 5432 -U ajit -d tazlab

Practical DBA Tip:
Test using the same hostname/IP, port, database and user used by the application. A successful local connection using the postgres account does not prove the remote application connection will work.

22. Understanding pg_ident.conf

pg_ident.conf provides username mapping for authentication methods that support such mappings.

Operating System / External Identity
              |
              v
        pg_ident.conf
              |
              v
        PostgreSQL Role

A mapping follows the general form:

MAPNAME    SYSTEM-USERNAME    DATABASE-USERNAME

Example:

taz_map    ajitlinux    ajit

The corresponding authentication configuration could reference the map:

local    tazlab    all    peer    map=taz_map

Conceptually:

OS User      : ajitlinux
                 |
                 v
Mapping      : taz_map
                 |
                 v
PostgreSQL   : ajit

23. SQL Server DBA Comparison

SQL Server	PostgreSQL
SQL Server Configuration Manager	postgresql.conf + OS/service configuration
sp_configure	PostgreSQL configuration parameters
sys.configurations	pg_settings
SQL Login	LOGIN role + authentication configuration
SQL Server ERRORLOG	PostgreSQL server logs
Restart-required configuration	PostgreSQL startup/postmaster settings

Note: These are conceptual comparisons intended to help SQL Server DBAs learn PostgreSQL. They are not always one-to-one architecture mappings.

24. Production Best Practices

✅ Identify Active Configuration Files

SHOW config_file;
SHOW hba_file;
SHOW ident_file;

✅ Back Up Configurations Before Major Changes

cp postgresql.conf postgresql.conf.bak
cp pg_hba.conf pg_hba.conf.bak

✅ Avoid Overly Permissive Authentication

Avoid using:

host all all 0.0.0.0/0 trust

Instead, restrict:

Database
User/role
Network range
Authentication method

✅ Prefer SCRAM for Password Authentication

scram-sha-256

✅ Check Pending Restart Changes

SELECT
    name,
    setting,
    pending_restart
FROM pg_settings
WHERE pending_restart = true;

✅ Never Copy Production Tuning Values Blindly

Configuration should reflect your workload, server RAM, CPU resources, connection concurrency, storage subsystem and application characteristics.

25. TAZLab Practical Exercises

Exercise 1: Find Configuration Files

SHOW config_file;
SHOW hba_file;
SHOW ident_file;
SHOW data_directory;

Exercise 2: Inspect Important Settings

SHOW port;
SHOW listen_addresses;
SHOW max_connections;
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

Exercise 3: Check Configuration Sources

SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name IN
(
    'port',
    'listen_addresses',
    'max_connections',
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem'
)
ORDER BY name;

Exercise 4: Session-Level Configuration

SHOW work_mem;

SET work_mem = '32MB';

SHOW work_mem;

Open another PostgreSQL session and check work_mem again. This is a simple way to understand session-level configuration.

Exercise 5: Inspect Authentication Rules

SELECT
    rule_number,
    type,
    database,
    user_name,
    address,
    auth_method,
    error
FROM pg_hba_file_rules
ORDER BY rule_number;

26. Useful DBA Configuration Dashboard Query

SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name IN
(
    'listen_addresses',
    'port',
    'max_connections',
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'wal_level',
    'max_wal_size',
    'checkpoint_timeout',
    'logging_collector',
    'log_min_duration_statement'
)
ORDER BY name;

TAZ DBA Tip:
Save this query in your PostgreSQL DBA toolkit. It gives you a quick snapshot of several important server configuration values and shows where those values originated.

27. Common DBA Mistakes

❌ Mistake 1: Editing the Wrong postgresql.conf

Check first:

SHOW config_file;

❌ Mistake 2: Changing listen_addresses but Forgetting pg_hba.conf

The server may start listening on the network, but the client's authentication path may still be rejected.

❌ Mistake 3: Ignoring pg_hba.conf Rule Order

An earlier matching rule can prevent your intended later rule from being used.

❌ Mistake 4: Using trust Just to Make the Connection Work

Fix the authentication issue instead of unnecessarily removing an authentication control.

❌ Mistake 5: Restarting PostgreSQL After Every Change

Determine whether the parameter requires a restart or can be activated with a configuration reload.

❌ Mistake 6: Setting work_mem Extremely High

Remember that memory use can multiply across query operations and concurrent database connections.

28. PostgreSQL Interview Questions

Q1. What is postgresql.conf?

It is the primary PostgreSQL server configuration file containing parameters related to connections, memory, logging, WAL, checkpoints and many other server behaviors.

Q2. What is pg_hba.conf?

It is PostgreSQL's host-based authentication configuration file. It controls how matching client connections are authenticated.

Q3. What does HBA stand for?

Host-Based Authentication.

Q4. How are pg_hba.conf rules evaluated?

PostgreSQL processes them in order. The first rule matching the connection is selected. Authentication failure against that selected rule does not cause PostgreSQL to continue looking for a later fallback rule.

Q5. How do you find the active PostgreSQL configuration file?

SHOW config_file;

Q6. How do you locate pg_hba.conf?

SHOW hba_file;

Q7. How can PostgreSQL configuration be reloaded?

SELECT pg_reload_conf();

Q8. How can you detect invalid pg_hba.conf entries?

SELECT *
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

Q9. What is pg_ident.conf?

It provides username mapping between authenticated external identities and PostgreSQL database roles when used with compatible authentication methods.

Q10. What is the difference between reload and restart?

A reload rereads configuration parameters that PostgreSQL allows to change while running. A restart stops and starts the PostgreSQL server and is required for startup-only parameters.

29. PostgreSQL DBA Quick Cheat Sheet

-- Find main configuration file
SHOW config_file;

-- Find pg_hba.conf
SHOW hba_file;

-- Find pg_ident.conf
SHOW ident_file;

-- Find data directory
SHOW data_directory;

-- Check PostgreSQL port
SHOW port;

-- Check listening interfaces
SHOW listen_addresses;

-- Check maximum connections
SHOW max_connections;

-- Check memory settings
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

-- View all settings
SHOW ALL;

-- Reload PostgreSQL configuration
SELECT pg_reload_conf();

-- Inspect configuration settings
SELECT *
FROM pg_settings;

-- Check changes requiring restart
SELECT *
FROM pg_settings
WHERE pending_restart = true;

-- Inspect authentication rules
SELECT *
FROM pg_hba_file_rules;

-- Find pg_hba.conf errors
SELECT *
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

30. Final Takeaway

For a PostgreSQL DBA, remember the responsibilities of these three files like this:

postgresql.conf
      |
      v
How should PostgreSQL operate?


pg_hba.conf
      |
      v
Who can connect and how are they authenticated?


pg_ident.conf
      |
      v
How should external usernames map to PostgreSQL roles?

The complete connection path can be visualized as:

Client
   |
   v
Network / Firewall
   |
   v
listen_addresses + port
   |
   v
pg_hba.conf
   |
   v
Authentication
   |
   v
PostgreSQL Role
   |
   v
Database / Schema / Object Privileges

Remember:

Successful network connectivity does not automatically mean successful PostgreSQL authentication.

Successful authentication does not automatically mean that the role has permission to access every database object.

These are separate layers, and understanding this distinction is fundamental to PostgreSQL administration and troubleshooting.

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning