Authentication is the process of verifying the identity of a user or system attempting to access a database. In the realm of PostgreSQL, authentication serves as the first line of defense, ensuring that only authorized individuals or applications gain entry. As the gateway to sensitive data, robust authentication is imperative, safeguarding against unauthorized access and fortifying the foundation of data protection. In this blog, we delve into the significance of authentication in PostgreSQL, unraveling its critical role in securing valuable information.
PostgreSQL supports various authentication methods to secure access to its database. The exact methods available may depend on the version of PostgreSQL you are using, In this blog we have mentioned a few of the most used authentication methods in PostgreSQL
pg_hba.conf file
The host-based authentication file AKA pg_hba.conf file in PostgreSQL is like a security guard, deciding who can access the database and when. It sets the rules for authentication, IP addresses, and user permissions, creating a strong defense for sensitive data. Admins must grasp and configure pg_hba.conf to protect against unauthorized access and security threats.
The pg_hba.conf file uses a line-by-line format, ignoring blank lines and text after # comments. Records, comprised of fields separated by spaces or tabs, can extend over lines and incorporate quoted values. Backslashes at the line’s end facilitate continuation.
The basic syntax of specifying a rule is mentioned below
# TYPE DATABASE USER ADDRESS METHODTYPE: Specifies the connection type (e.g., local, host, hostssl).
DATABASE: Specifies the name of the database for which the rule is applied.
USER: Specifies the PostgreSQL username for authentication.
ADDRESS: Specifies the IP address range or host allowed for the connection.
METHOD: Specifies the authentication method for the connection.
NOTE: If you wish to permit connection requests from external systems, you may need to modify a setting within the postgresql.conf file. Uncomment and set the listen_addresses property to ‘*’ to enable external connections.
Trust
Allows access without password authentication (not recommended for production).
This authentication method allows PostgreSQL connections without requiring a password, relying on the trustworthiness of the connecting client. It’s used cautiously, often in local development environments, as it may pose security risks when permitting unrestricted access to the database without password verification.
Rule to allow local Linux users to interact with the database without a password
local all all trust
Password
Authentication method in pg_hba.conf requires users to provide a password during PostgreSQL connections. This method enhances security by verifying user credentials nut the down side of this method is It sends passwords in clear text unless you are using an encrypted connection, such as SSL. Without encryption, passwords can potentially be intercepted by attackers during transmission. For enhanced security, it’s recommended to use encrypted connections when employing the “password” authentication method, especially over untrusted networks. OR use other encrypted password methods such as md5 or scram-sha-256
Rule to allow local Linux users to interact with the database with a password
local all all passwordMD5
This authentication method hashes passwords before transmission, offering a more secure approach than plain text. Initially considered secure, MD5 is now vulnerable to collision attacks, where different inputs produce the same hash. This weakness allows attackers to use precomputed tables (rainbow tables) or hash-cracking tools to swiftly match hash values to plaintext passwords. Consequently, using MD5 for password storage in production is strongly discouraged due to its compromised security.
Rule to allow local Linux users to interact with the database with md5
local all all md5SCRAM-SHA-256
Salted Challenge Response Authentication Mechanism with SHA-256 is a secure authentication method in PostgreSQL. It employs cryptographic hashing, including salt, to protect against common attacks. This method enhances password security, mitigating risks associated with older methods like MD5 and providing robust protection for user authentication.
It Is mostly used and considered a secure authentication method in PostgreSQL
Rule to allow local Linux users to interact with the database with scram-sha-256
local all all scram-sha-256Salt?
In cryptography, a “salt” is a random value that is generated uniquely for each password. It is then combined with the password before hashing. The purpose of a salt is to prevent attackers from using precomputed tables (rainbow tables) or other common attacks, where the same password would always result in the same hash. The use of a unique salt for each password ensures that even if two users have the same password, their hashed values will be different due to the unique salt, making it significantly more difficult for attackers to use precomputed tables or other known attacks. The salt adds an extra layer of security to password hashing.
See also: RFC 7677
Peer
This authentication method streamlines local connections by relying on the operating system user identity. When a PostgreSQL user shares the same name as the operating system user, authentication occurs seamlessly without requiring an additional password. This method simplifies access management, aligning PostgreSQL authentication with system credentials for enhanced security in local environments.
Rule to authenticate an OS username Alice with the database without having to type the password
local all alice peerSo in the above case a Unix-based system where the operating system user “Alice” wants to connect to the PostgreSQL database as the PostgreSQL user “Alice.”
With this configuration, when “Alice” attempts to connect locally, PostgreSQL will authenticate “Alice” based on the operating system user, allowing seamless access without requiring an explicit password for this specific user.
LDAP
Lightweight Directory Access Protocol is a standardized protocol utilized for accessing and administering directory information services. Commonly employed in network environments, LDAP plays a pivotal role in managing user authentication, authorization, and directory information. Additionally, LDAP is frequently adopted as an authentication method to facilitate user authentication against an LDAP directory.
LDAP can operate in two modes.
simple bind mode the server binds to a constructed distinguished name using prefix and suffix parameters. The prefix typically specifies “cn=” or “DOMAIN” in Active Directory.
search+bind mode, the server initially binds with a fixed username and password, conducts a search for the user, and then re-binds to verify the login. While this mode offers flexibility in user object locations, it involves two separate connections to the LDAP server.
A few important parameters
ldapserver: Specifies the address or hostname of the LDAP server that PostgreSQL should use for authentication.
ldapport : Specifies the port number on which the LDAP server is listening for connections
ldaptls: Set to 1 to make the connection between PostgreSQL and the LDAP server use TLS encryption
ldapprefix: Specifies a prefix that is added to the username during the construction of the distinguished name (DN) for binding to the LDAP directory.
ldapsuffix: Specifies a suffix that is added to the username during the construction of the distinguished name (DN) for binding to the LDAP directory.
Rule to allow host-based connections from any IP address to any database and any user, but only if the LDAP authentication with the specified server, prefix, and suffix is successful.
host all all 0.0.0.0/0 ldap ldapserver="ldap.example.com" ldapprefix="uid=" ldapsuffix=",ou=users,dc=example,dc=com"Certificate
It refers to the SSL certificate that a client presents to the PostgreSQL server during the SSL handshake process. The server validates the client’s certificate to authenticate the connection. This method is commonly used in secure environments where SSL/TLS encryption and client certificate verification are crucial for securing communication between clients and the PostgreSQL server.
Rule to permit SSL-encrypted connections (hostssl) from any IP address (0.0.0.0/0) to any database and any user, using certificate-based authentication (cert).
hostssl all all 0.0.0.0/0 certMap a user via pg_ident.conf file
This file is used for identity mapping, allowing you to map database usernames to system-level usernames. This is particularly useful when the PostgreSQL usernames differ from the system-level usernames. The file is typically located in the PostgreSQL data directory.
Assume you have a PostgreSQL database user named db_user who needs to connect using SSL certificates, and the corresponding system user on the PostgreSQL server is system_user. You can create an authentication map named “user_map” to establish this mapping.
pg_ident.conf entry
user_map db_user system_userpg_hba.conf entry
hostssl all all all cert map=user_mapRADIUS
Remote Authentication Dial-In User Service is a method of authentication used for PostgreSQL client connections. The radius authentication method in pg_hba.conf allows PostgreSQL to offload authentication to a RADIUS server.
Few important parameter
radiusservers: The DNS names or IP addresses of the RADIUS servers to connect to. This parameter is required.
radiussecrets: typically it refers to the shared secret used for secure communication between the PostgreSQL server and the RADIUS server.
Bellow is the rule to allow SSL-encrypted connections from the IP range 192.168.1.0/24, and authentication for these connections is delegated to a RADIUS server. The radiussecret parameter ensures secure communication by specifying a shared secret that must match the secret configured on the RADIUS server.
hostssl all all 192.168.1.0/24 radius radiussecret=my-secure-keyGSSAPI (Kerberos)
Generic Security Services Application Program Interface is a widely-used authentication protocol often paired with Kerberos for secure network communication.
Kerberos, a network authentication protocol, ensures secure user and service authentication, minimizing risks of unauthorized access. GSSAPI serves as a versatile framework supporting various security mechanisms, allowing applications to achieve secure communication.
A keytab file securely stores credentials, enabling PostgreSQL to authenticate itself to the Kerberos Key Distribution Center without password prompts. The krb_server_keyfile parameter in postgresql.conf specifies the path for the keytab file.
For a PostgreSQL server to recognize the correct Kerberos realm during user connections from the ‘stormatics.tech’ domain, use the following pg_hba.conf rule:
host all all {{ SUBNET }} gss include_realm=1 krb_realm=STORMATICS.TECH
Realm: is a logical administrative domain. It represents a portion of the Kerberos authentication infrastructure and is often associated with a specific organization or domain. Realms are used to partition the Kerberos database and namespace.
Example for initializing a cluster with SCRAM-SHA-256:
We’ll employ the PostgreSQL built-in binary, initdb, to initialize our cluster using the SCRAM-SHA-256 encryption method. Begin by creating a secure text file to store your password.
echo strongp > passfileNow, proceed with initializing the cluster
/usr/lib/postgresql/16/bin/initdb -D data -A scram-sha-256 --pwfile passfileHere, -A instructs to use the SCRAM-SHA-256 encryption method, and –pwfile is utilized to specify the location of the password file from which initdb will read the superuser password.
As we conclude our exploration of PostgreSQL authentication methods, we’re reminded that the security landscape is ever-evolving. By embracing and understanding the diverse array of authentication options, from traditional password-based methods to advanced mechanisms like SCRAM-SHA-256 and LDAP integration, you empower yourself to tailor your PostgreSQL setup to meet the demands of a dynamic environment. Remember, robust security is not a one-size-fits-all solution; it’s a journey of continuous learning and adaptation.
