Authenticating PostgreSQL Clients

Understanding Client Authentication in PostgreSQL

When a database sits behind a firewall, the first line of defense is often the way clients prove their identity. PostgreSQL has evolved a rich set of authentication methods that let administrators decide how strict or permissive they want to be. The choice of method can affect every part of your deployment, from internal services to external applications, from development workstations to public API gateways.

At the core of PostgreSQL’s authentication system is the pg_hba.conf file. HBA stands for “host‑based authentication,” and the file acts like a gatekeeper that maps who can connect to what database, from which IP range, and with which credential method. The file lives in the data directory - typically pointed to by the $PGDATA environment variable. Every time the server starts or receives a SIGHUP signal, it reloads the file, making changes effective immediately.

Three kinds of connections appear in pg_hba.conf: host, hostssl, and local. The first two refer to TCP/IP traffic; the former allows plain sockets, while the latter forces an SSL/TLS channel. Local entries match connections that come through a Unix domain socket, typically from applications running on the same machine. Knowing which type to use helps administrators enforce different rules for internal versus external traffic.

The next column specifies the database name that the rule applies to. You can target a single database, use all to match every database, or choose sameuser to allow a user to connect only to a database that shares their name. This level of granularity is handy when you have multiple services sharing a server but each service needs access to its own schema.

IP address and netmask fields follow. The IP field can be a single host or a range; the netmask tells PostgreSQL how many leading bits to compare. Using subnet masks like lets you accept connections from an entire /24 network, which is common in corporate environments.

The authentication type field is where the real work happens. PostgreSQL ships with a wide array of options. Here is a quick look at the most common ones:

• trust – no password, anyone can log in if the client can reach the server. Use only on a secure, isolated network.
• password – the client sends the password in clear text. This requires the client library to support SSL or to use hostssl entries.
• md5 – the client sends a salted hash of the password. This is the recommended method for modern deployments.
• ident – the server queries the client’s ident service to determine the operating‑system user, then maps that to a PostgreSQL role.
• pam – leverages the Pluggable Authentication Module framework on the host machine, letting you tie database access to system accounts, LDAP, or other PAM backends.
• krb5 – integrates with Kerberos v5, providing single sign‑on capabilities.
• reject – forces denial of connection for matching criteria. This is useful when you want to block a range after granting broader access earlier in the file.

  While the file looks static, you can achieve dynamic mapping through pg_ident.conf. That file contains mapping rules that translate the ident username reported by the client into a PostgreSQL role. The mapping name is supplied in the auth_argument column of a matching ident rule in pg_hba.conf. A typical mapping might say that the OS user appuser should become the database role app_role. If you use the special map name sameuser, PostgreSQL will skip pg_ident.conf and match the ident user directly to a PostgreSQL role of the same name.

  When planning authentication, remember that the first matching rule wins. Rules are evaluated from top to bottom. Placing a broad host all all 0.0.0.0/0 reject at the end of the file guarantees that no stray traffic sneaks through if you forget to exclude it earlier.

  Because authentication is a gatekeeper, a misconfigured pg_hba.conf can lock you out of your own server or, worse, expose your data to untrusted parties. Always backup the file before editing, test in a staging environment, and consider using the pg_hba.conf.sample as a reference when you first install PostgreSQL.

  With this foundational knowledge, you can tailor the authentication scheme to match your organization’s risk profile, regulatory requirements, and operational convenience. The next section walks through how to actually create the configuration files, showing practical examples and the syntax you’ll use day‑to‑day.

  Creating and Managing pg_hba.conf and pg_ident.conf

  The PostgreSQL documentation recommends that pg_hba.conf be kept in a clean, human‑readable format. A typical entry looks like this:

  Prompt

  host all all 192.168.1.0/24 md5</p>

  Let’s unpack each field. The first word, host, tells PostgreSQL the rule applies to TCP/IP connections. The next two words, all and all, mean the rule applies to every database and every role. The IP range 192.168.1.0/24 accepts any client in that subnet. Finally, md5 tells PostgreSQL to require a password hash.

  Suppose your database server needs to accept connections from a remote API gateway that lives on . The API will use a specific role, api_user, and you want to enforce SSL. You could write:

  Prompt

  hostssl all api_user 10.5.4.12/32 password</p>

  In this line, hostssl ensures encryption, the password type forces clear text over SSL, and the subnet mask /32 restricts the rule to that single host. If you prefer the safer MD5 method, replace password with md5

  When you need to map operating‑system users to database roles, pg_ident.conf comes into play. It has three columns: map name, ident username, and PostgreSQL username. The file begins with a comment header, then one or more mappings. For example:

  Prompt

  # Map Linux users to PostgreSQL roles</p> <p>mymap alice app_role</p> <p>mymap bob admin_role</p>

  With this map, an ident query that reports alice will be granted the role app_role, while bob becomes admin_role. To tie this map into pg_hba.conf, add a rule like:

  Prompt

  host all all 192.168.10.0/24 ident mymap</p>

  Here, any client in the 192.168.10.0/24 subnet will have its ident username mapped through mymap. If the client is alice, PostgreSQL will see app_role as the authenticated role. The rule applies to all databases; if you want to limit it to a specific database, replace the second all with that database’s name.

  Sometimes you want to allow users to connect only to databases that share their name. PostgreSQL offers the sameuser keyword for that. A line such as:

  Prompt

  local sameuser sameuser 0.0.0.0/0 md5</p>

  lets any OS user who can reach the server via a Unix socket log in only to a database that has the same name. This pattern is handy for multi‑tenant setups where each tenant runs a separate database with a matching role.

  For environments that use LDAP or PAM, you can set the authentication type to pam or ldap (the latter requires the pg_authid extension). For example, to use PAM with a custom service name, you might add:

  Prompt

  host all all 10.1.0.0/16 pam mypostgres</p>

  The mypostgres service name refers to a PAM configuration file, typically found in /etc/pam.d/. This lets you delegate all password checks to the operating system, simplifying credential management across many applications.

  Once you have edited pg_hba.conf or pg_ident.conf, restart the server or send a SIGHUP to the postmaster process. PostgreSQL will reload the files without dropping existing connections. If a syntax error occurs, the server logs the failure, and the previous configuration remains in effect.

  Because the authentication configuration can dramatically affect the security posture of your database, it’s common practice to version‑control these files in a configuration management system. That way you can audit changes, roll back missteps, and maintain a clear record of who approved each rule. Also, keep the file’s permissions tight; only privileged users should be able to edit it.

  With these examples in hand, you can now craft a rule set that matches your deployment’s needs - whether that means tight SSL only, a mix of local and remote connections, or a full identity‑based mapping strategy.

  Common Pitfalls and Best Practices for PostgreSQL Authentication

  While PostgreSQL’s authentication system is powerful, its flexibility can lead to mistakes that expose the database or lock legitimate users out. Below are several pitfalls to avoid, along with practical ways to keep the configuration robust.

  First, never leave a trust rule in place on a machine that is accessible from the internet. Even if you intend to use trust for local debugging, accidentally dropping the rule into the pg_hba.conf can give anyone on the network full superuser access. Instead, use reject at the end of the file to catch anything that slips through:

  Prompt

  # Block all other traffic</p> <p>host all all 0.0.0.0/0 reject</p>

  Second, pay attention to rule order. PostgreSQL stops evaluating after it finds the first match. If you place a broad md5 rule before a specific ident rule, the client will never hit the ident path. Arrange your entries from most specific to most general, and use reject as a safety net at the end.

  Third, avoid mixing authentication methods for the same host and database. For instance, having both md5 and pam entries for the same IP range can confuse clients. Pick a single method per host/directory and document why it was chosen. If you later need to switch, update both pg_hba.conf and pg_ident.conf accordingly.

  Fourth, keep your server’s time in sync. Kerberos and certain SSL setups rely on accurate timestamps. If the server’s clock drifts, authentication may fail or, worse, succeed for the wrong user. Use NTP or a similar time‑synchronization service to maintain consistency.

  Fifth, review the logs. PostgreSQL writes detailed authentication attempts to the log file. Regularly parse these logs for failed attempts, especially from unfamiliar IPs. A spike in failed logins could indicate a brute‑force attack or a misconfigured client.

  Another best practice is to use roles with minimal privileges. The principle of least privilege should guide role design. Grant only the permissions required for a job, and use role inheritance sparingly. When a role needs temporary superuser rights, consider using a separate sudo‑like mechanism outside of PostgreSQL, or grant superuser temporarily and revoke it immediately after the task completes.

  When using ident authentication, make sure the client’s OS user matches the role expected by the database. In environments where the client’s OS account is dynamic or not guaranteed, ident can break. In those cases, md5 or pam is safer.

  For highly secure environments, enable ssl for all remote connections. This ensures that even if the md5 hash is intercepted, it cannot be replayed without the private key. Generate strong certificates, rotate them regularly, and keep the private key on a hardware security module (HSM) if possible.

  Finally, test changes in a staging environment before applying them to production. Even a single typo can prevent all remote connections, bringing critical services offline. Automated tests that attempt to connect with all allowed methods can catch such issues early.

  By keeping these guidelines in mind, you can configure PostgreSQL authentication in a way that is both secure and maintainable. The next time you edit pg_hba.conf or pg_ident.conf, remember that the rules you write today shape how your data will be protected tomorrow.

  Jay Fougere, IT manager for the murdok network, also writes occasional articles on database security. For more detailed guidance or to share your own experiences, contact him at Jay@murdok.org.