PostgreSQL Connection & Auth

PostgreSQL’s connection and authentication mechanisms form the first line of defense for any deployment. The connection

PostgreSQL’s connection and authentication mechanisms form the first line of defense for any deployment. The connection phase determines how a client reaches the server, while authentication decides which role is allowed to access which database and with what privileges. Mastering the relevant configuration files—postgresql.conf and pg_hba.conf—and the available authentication methods is essential for both developers and operations engineers.

Core Connection Parameters

A typical PostgreSQL client needs to supply the following pieces of information:

Parameter Typical Value Description
Host localhost, an IP address, or a DNS name Network endpoint of the server.
Port 5432 (default) TCP port on which the server listens.
Database mydb Target database name.
User app_user PostgreSQL role used for the session.
Password •••••• Credential required by the chosen authentication method.
Connection type Unix socket or TCP/IP Unix sockets are used for local connections; TCP/IP is required for remote access.

Local connections often default to a Unix socket located under /var/run/postgresql or /tmp. When a TCP connection is needed, the client contacts host:port.

Common client invocations

# Classic command‑line syntax
psql -h localhost -p 5432 -U app_user -d mydb

# URI form – handy for scripts and tools that accept a single string
psql "postgresql://app_user:SecretPwd@localhost:5432/mydb?sslmode=require"

# Environment‑variable shortcut
export PGHOST=localhost
export PGPORT=5432
export PGUSER=app_user
export PGDATABASE=mydb
psql

Server‑Side Listening Configuration

The postgresql.conf file controls whether the server accepts remote connections at all. The two most relevant settings are:

listen_addresses = 'localhost'   # bind only to the loopback interface
port = 5432                       # TCP port
  • listen_addresses = 'localhost' – the server is reachable only from the same host.
  • listen_addresses = '*' – the server listens on all network interfaces. In this mode you must rely on firewalls and pg_hba.conf to restrict who can connect.

After editing postgresql.conf, either restart the service or reload the configuration:

SELECT pg_reload_conf();   -- reloads postgresql.conf and pg_hba.conf

You can verify the active settings with:

SHOW listen_addresses;
SHOW port;

The pg_hba.conf File – Who May Connect, From Where, and How

pg_hba.conf (Host‑Based Authentication) is the gatekeeper that decides which client is allowed to connect to which database as which role, and by which authentication method. Locate the file with:

SHOW hba_file;

Rule syntax

type  database  user  address  auth-method  [options]
Field Typical values Meaning
type local, host, hostssl, hostnossl Determines the transport (Unix socket vs. TCP) and whether SSL is required.
database all, a specific DB name, sameuser, samerole Which databases the rule applies to.
user all, a role name, +group_name Which PostgreSQL roles are covered.
address CIDR block (e.g., 192.168.1.0/24), IP mask, or all Source IP range for TCP connections. Ignored for local.
auth‑method peer, scram-sha-256, md5, trust, … The actual authentication mechanism.
options map=…, clientcert=1, … Optional modifiers for certain methods.

Rules are evaluated top‑down; the first matching line wins. A typical configuration might look like this:

# 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    appdb     app       192.168.10.0/24 scram-sha-256
host    all       all       0.0.0.0/0      reject

After any change, reload the rules:

SELECT pg_reload_conf();

Authentication Methods – Choosing the Right One

Method Security level Typical use case
trust None (no password) Isolated test environments; never on a networked host.
peer OS‑level verification (user name must match) Local connections on Unix systems.
ident Uses an external ident server to map OS users to DB roles Legacy setups where a central ident service exists.
md5 MD5‑hashed password (now considered weak) Older installations; should be replaced.
scram‑sha‑256 Salted SHA‑256 hash, protects against replay attacks Recommended for all modern deployments.
password Plain‑text transmission (unless wrapped in SSL) Rarely appropriate; only with mandatory TLS.
cert Client SSL certificate validation Environments that already enforce mutual TLS.
gss, sspi, ldap, radius, pam External authentication providers (Kerberos, Active Directory, etc.) Enterprise integrations.

To make SCRAM the default password format, set in postgresql.conf:

password_encryption = 'scram-sha-256'

Create or update a role’s password:

CREATE ROLE app_user LOGIN PASSWORD 'StrongPass123!';
ALTER ROLE app_user PASSWORD 'EvenStrongerPass!';

Putting It All Together – A Walkthrough

  1. Create a role and a database 

    CREATE ROLE app_user LOGIN PASSWORD 'StrongPass123!';
CREATE DATABASE appdb OWNER app_user;

  2. Add an HBA entry for the application 

    # Allow app_user to connect to appdb from the 10.0.0.0/24 subnet
host    appdb    app_user    10.0.0.0/24    scram-sha-256

  3. Reload the configuration 

    SELECT pg_reload_conf();

  4. Connect from a client machine 

    psql "postgresql://app_user:StrongPass123!@10.0.0.5:5432/appdb?sslmode=prefer"

  5. Verify the session 

    SELECT current_user,
       current_database(),
       inet_server_addr(),
       inet_server_port();

  6. Inspect which HBA rule was applied 

    SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE user_name = 'app_user' AND database = 'appdb';

Security Best Practices & Troubleshooting

Hardening guidelines

  • Never expose trust or password to the public internet.

  • Restrict listen_addresses to the minimal set of interfaces required. Combine this with host‑based firewalls (iptables, firewalld, etc.).

  • Prefer scram-sha-256 for password authentication; disable legacy md5.

  • Force SSL for remote connections by using hostssl entries or by setting ssl = on in postgresql.conf.

  • Apply the principle of least privilege – avoid using the superuser postgres for application workloads.

  • Enable connection logging to aid forensic analysis: 

    log_connections = on
log_disconnections = on

Common error messages

Symptom Likely cause Quick fix
no pg_hba.conf entry for host "X.X.X.X", user "app_user", database "appdb", SSL off No matching rule for the source IP or SSL mode. Add a proper host or hostssl line covering the IP/subnet.
password authentication failed for user "app_user" Wrong password or mismatched auth method (e.g., md5 vs. scram). Verify the password, ensure password_encryption matches the stored hash, and reload HBA.
Peer authentication failed for user "app_user" A local connection attempted peer auth but the OS user does not match the DB role. Either change the role name, use a different auth method, or connect as the matching OS user.
Connection refused Server not listening on the requested address/port, firewall block, or wrong port number. Check listen_addresses, confirm the port, and inspect firewall rules.
Changes to pg_hba.conf seem ignored Reload not performed or syntax error in the file. Run SELECT pg_reload_conf(); and review the server log for parsing errors.

Conclusion

Understanding how PostgreSQL establishes a connection and validates a client is crucial for building secure, reliable applications. By carefully configuring postgresql.conf to expose only the necessary network interfaces, crafting precise pg_hba.conf rules, and adopting strong authentication methods such as SCRAM‑SHA‑256, you can protect your data while keeping legitimate access straightforward. Regularly audit your settings, keep logs enabled, and stay vigilant for misconfigurations—these habits will keep your PostgreSQL deployment both performant and safe.