What are the different authentication methods available in PostgreSQL, and when would you use each one?

Question

Accepted Answer

PostgreSQL supports multiple authentication methods configured in the pg_hba.conf file. Each method has different security characteristics and is suited for different use cases. Understanding these options is essential for configuring secure database access.

The pg_hba.conf file controls client authentication by matching incoming connections to rules based on the connection type, database, user, and client address. Each rule specifies an authentication method. Rules are evaluated top to bottom, and the first match is used, so ordering matters.

Password-based authentication is the most common method. PostgreSQL supports three password authentication types. The md5 method sends an MD5-hashed password, which was the standard for many years. The scram-sha-256 method, introduced in PostgreSQL 10, uses the more secure SCRAM authentication protocol and is the recommended choice for new installations. The password method sends passwords in clear text and should only be used over encrypted connections. Always prefer scram-sha-256 over md5 for new setups.

Peer authentication verifies that the operating system user name matches the requested database user name. It only works for local connections through Unix domain sockets. This is the default method for local connections on many installations. It is convenient for the postgres superuser account because you simply switch to the postgres OS user to connect without a password. However, it requires that database users map to OS users.

Ident authentication is similar to peer but works for TCP connections by querying an ident server on the client machine. It is less common today because ident servers are rarely deployed, and the protocol is inherently insecure as the client machine is trusted to report its user identity accurately.

Certificate authentication uses SSL client certificates to verify client identity. The client presents a certificate signed by a trusted certificate authority, and PostgreSQL verifies it. This is one of the most secure methods because it does not rely on passwords that can be guessed or stolen. It is appropriate for server-to-server communication, such as replication connections or application server connections in high-security environments.

LDAP authentication verifies credentials against an external LDAP directory, such as Microsoft Active Directory. This is useful in enterprise environments where user management is centralized in a directory service. Users authenticate with their corporate credentials rather than having separate database passwords. Two modes are available: simple bind, where PostgreSQL constructs a DN from the username and attempts to bind, and search-and-bind, where PostgreSQL first searches for the user DN and then binds.

RADIUS authentication delegates authentication to a RADIUS server, common in environments with existing RADIUS infrastructure. Kerberos and GSSAPI authentication provide single sign-on capabilities in environments with Kerberos infrastructure, common in enterprise Windows environments with Active Directory.

As a junior DBA, the most important practice is to configure pg_hba.conf with the most restrictive rules possible. Use scram-sha-256 for remote password authentication, require SSL for all remote connections, and limit access to specific IP addresses or subnets. Review pg_hba.conf regularly and remove any rules that are no longer needed.