Initial Server Configuration

Learning Focus

Use this lesson to understand Initial Server Configuration — security hardening, memory tuning, logging, and authentication setup for a safe and performant PostgreSQL environment.

Basic Security Setup

a. Superuser Password Configuration

ALTER USER postgres WITH PASSWORD 'NewStrongPassword!123';

b. Create Application Roles (Least Privilege)

-- Create a login role for your application
CREATE ROLE app_user LOGIN PASSWORD 'AppSecurePass!456';

-- Grant connect and schema usage
GRANT CONNECT ON DATABASE pg_lab TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant table-level permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Ensure future tables also get permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

c. Remove Default Trust Authentication (Production)

By default, some installations allow local connections without a password. For production, always require authentication:

# pg_hba.conf — change 'trust' to 'scram-sha-256'
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  scram-sha-256
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

After editing, reload the configuration:

sudo systemctl reload postgresql

Or from psql:

SELECT pg_reload_conf();

Authentication Configuration (`pg_hba.conf`)

The pg_hba.conf file controls who can connect, from where, and how they authenticate.

File Format

# TYPE    DATABASE    USER        ADDRESS           METHOD
local     all         postgres                      peer
local     all         all                           scram-sha-256
host      all         all         127.0.0.1/32      scram-sha-256
host      all         all         ::1/128           scram-sha-256
host      all         all         192.168.1.0/24    scram-sha-256

Authentication Methods

Method	Description	Use Case
`peer`	OS username must match database role	Local Linux connections
`scram-sha-256`	Secure password authentication	Recommended for remote connections
`md5`	Older password authentication	Legacy systems (prefer `scram-sha-256`)
`cert`	SSL client certificate	High-security environments
`trust`	No authentication required	Testing only — never in production
`reject`	Deny connection	Block specific users/hosts

Common Patterns

# Allow local superuser access via OS auth
local   all         postgres                    peer

# Require password for all other local users
local   all         all                         scram-sha-256

# Allow app_user from a specific subnet
host    pg_lab      app_user    10.0.0.0/8      scram-sha-256

# Block a specific user from remote access
host    all         dangerous_user  0.0.0.0/0   reject

Network Configuration

a. Listen Addresses

# postgresql.conf
listen_addresses = 'localhost'          # Default: only local connections
# listen_addresses = '*'               # Allow remote connections (use with pg_hba.conf)

b. Port Configuration

# postgresql.conf
port = 5432                             # Default PostgreSQL port

c. Max Connections

# postgresql.conf
max_connections = 100                   # Default: 100
# For apps with connection pooling (PgBouncer), 100–200 is usually enough

Memory Configuration

a. Shared Buffers

The most important memory setting. Controls how much memory PostgreSQL uses for caching data pages.

# postgresql.conf
shared_buffers = 1GB                    # Recommended: 25% of total RAM
                                        # For a 4GB machine: 1GB
                                        # For a 16GB machine: 4GB

b. Work Memory

Memory per sort/hash operation per query. Be careful — this is per operation, not per connection.

# postgresql.conf
work_mem = 16MB                         # Default: 4MB
                                        # Increase for complex sorts and joins
                                        # Be careful: 100 connections × 16MB = 1.6GB

c. Maintenance Work Memory

Memory for maintenance operations (VACUUM, CREATE INDEX, ALTER TABLE).

# postgresql.conf
maintenance_work_mem = 256MB            # Default: 64MB
                                        # Higher values speed up VACUUM and index creation

d. Effective Cache Size

Tells the query planner how much memory is available for caching (OS cache + shared buffers).

# postgresql.conf
effective_cache_size = 3GB              # Recommended: 50–75% of total RAM
                                        # For a 4GB machine: 3GB
                                        # This is a planner hint, not an allocation

Memory Configuration Quick Reference

Setting	Recommended	What It Controls
`shared_buffers`	25% of RAM	PostgreSQL data page cache
`work_mem`	16–64 MB	Per-operation sort/hash memory
`maintenance_work_mem`	256 MB–1 GB	VACUUM, CREATE INDEX memory
`effective_cache_size`	50–75% of RAM	Planner hint for total available cache

WAL Configuration

# postgresql.conf
wal_level = replica                     # Default: replica (supports streaming replication)
max_wal_size = 1GB                      # Max WAL size before checkpoint
min_wal_size = 80MB                     # Min WAL size to retain

Logging Configuration

a. Basic Logging

# postgresql.conf
logging_collector = on
log_directory = 'log'                   # Relative to data directory
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

b. Query Logging (Slow Queries)

# postgresql.conf
log_min_duration_statement = 1000       # Log queries slower than 1 second (in ms)
log_statement = 'none'                  # Options: none, ddl, mod, all
                                        # Use 'ddl' to log schema changes
                                        # Use 'all' for debugging only

c. Connection Logging

# postgresql.conf
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: user=%u,db=%d '

d. Lock Wait Logging

# postgresql.conf
log_lock_waits = on
deadlock_timeout = 1s

Autovacuum Configuration

Autovacuum is critical for PostgreSQL health. It cleans up dead tuples and updates statistics.

# postgresql.conf — defaults are usually fine for learning
autovacuum = on                         # Never turn this off
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2    # Vacuum when 20% of rows are dead
autovacuum_analyze_scale_factor = 0.1   # Analyze when 10% of rows change

warning

Never disable autovacuum in production. Without it, tables bloat, query plans degrade, and eventually you risk transaction ID wraparound — which can cause the entire database to shut down.

Character Set and Locale Configuration

# postgresql.conf (set at initdb time for best results)
# Default encoding is UTF-8 on modern installations

# Verify current settings:

SHOW server_encoding;    -- Should be 'UTF8'
SHOW lc_collate;
SHOW lc_ctype;

Post-Configuration Steps

a. Reload Configuration (No Restart Required)

sudo systemctl reload postgresql       # Linux

Or from psql:

SELECT pg_reload_conf();

b. Restart Service (For Settings That Require Restart)

Settings like shared_buffers, max_connections, and listen_addresses require a full restart:

sudo systemctl restart postgresql      # Linux

c. Verify Configuration

-- Show a specific setting
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

-- Show all non-default settings
SELECT name, setting, unit, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

-- Check which settings need a restart
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

Configuration File Locations

File	Purpose	Location (Ubuntu/Debian)
`postgresql.conf`	Server settings	`/etc/postgresql/16/main/postgresql.conf`
`pg_hba.conf`	Client authentication	`/etc/postgresql/16/main/pg_hba.conf`
`pg_ident.conf`	OS-to-DB user mapping	`/etc/postgresql/16/main/pg_ident.conf`

Find config file locations from psql:

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

Safe Defaults for Learning

Keep a dedicated pg_lab database — never experiment in production databases
Do not develop as superuser — create a dedicated role with limited privileges
Use scram-sha-256 authentication — never trust except in throwaway Docker containers
Enable \timing in psql — always be aware of query performance
Set log_min_duration_statement = 1000 — catch slow queries early

Practice Exercise

Task: Secure a fresh PostgreSQL installation

Change the postgres superuser password
Create a pg_lab database
Create an app_user role with LOGIN and a strong password
Grant CONNECT on pg_lab and table permissions on public schema
Edit pg_hba.conf to require scram-sha-256 for all connections
Enable slow query logging (threshold: 500ms)
Reload the configuration and verify with SHOW commands

Concept Map

flowchart LR
    A[Install PostgreSQL] --> B[postgresql.conf]
    A --> C[pg_hba.conf]
    B --> D[Memory Tuning]
    B --> E[Logging]
    B --> F[WAL Config]
    C --> G[Authentication Rules]
    D --> H[Production Ready]
    E --> H
    G --> H

Common Pitfalls

Pitfall	Consequence	Prevention
Leaving `trust` authentication in pg_hba.conf	Anyone can connect without a password	Use `scram-sha-256` for all non-peer connections
Setting `work_mem` too high	OOM killer strikes under load	Calculate: `work_mem × max_connections × ops_per_query`
Editing `postgresql.conf` without reloading	Changes don't take effect	Always `SELECT pg_reload_conf()` or restart
Disabling autovacuum	Table bloat, degraded performance, TXID wraparound risk	Never disable autovacuum
Using `log_statement = 'all'` in production	Massive log volume, performance impact	Use `log_min_duration_statement` instead

Quick Reference

# Reload configuration (no downtime)
sudo systemctl reload postgresql
# Or from psql:
# SELECT pg_reload_conf();

# Restart (for shared_buffers, max_connections, etc.)
sudo systemctl restart postgresql

# Find config files
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

-- Verify settings
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SHOW log_min_duration_statement;

-- See non-default settings
SELECT name, setting, unit, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

What's Next

Previous: psql and Client Tools — Review the previous lesson to reinforce context.
Section Overview — Return to this section index and choose another related lesson.

Basic Security Setup​

a. Superuser Password Configuration​

b. Create Application Roles (Least Privilege)​

c. Remove Default Trust Authentication (Production)​

Authentication Configuration (pg_hba.conf)​

File Format​

Authentication Methods​

Common Patterns​

Network Configuration​

a. Listen Addresses​

b. Port Configuration​

c. Max Connections​

Memory Configuration​

a. Shared Buffers​

b. Work Memory​

c. Maintenance Work Memory​

d. Effective Cache Size​

Memory Configuration Quick Reference​

WAL Configuration​

Logging Configuration​

a. Basic Logging​

b. Query Logging (Slow Queries)​

c. Connection Logging​

d. Lock Wait Logging​

Autovacuum Configuration​

Character Set and Locale Configuration​

Post-Configuration Steps​

a. Reload Configuration (No Restart Required)​

b. Restart Service (For Settings That Require Restart)​

c. Verify Configuration​

Configuration File Locations​

Safe Defaults for Learning​

Practice Exercise​

Concept Map​

Common Pitfalls​

Quick Reference​

What's Next​