This post was originally written in 2023 and was updated in 2025.

Protecting sensitive information in PostgreSQL starts with strong authentication. It’s the process of verifying who’s trying to connect, whether through usernames and passwords, tokens, or one-time codes. For DBAs, that means not only choosing the right method but also monitoring and auditing logs to catch suspicious activity before it becomes a problem.

PostgreSQL authentication is managed through the pg_hba.conf file, which lets you define different rules for different types of connections. Options include password-based methods, client certificates, and external providers such as GSSAPI, LDAP, and RADIUS. By default, PostgreSQL allows “trust” connections from localhost without a password; a setting that’s fine for testing but not something you want in production. For remote or enterprise environments, stronger authentication methods are essential.

PostgreSQL authentication falls into three categories: internal authentication, OS-based, and external. We’ve already explored the first two in earlier posts. This article takes a closer look at external methods and how they can help you raise the bar on security.

PostgreSQL external authentication

PostgreSQL supports a variety of external authentication methods, including GSSAPI, LDAP, and RADIUS. GSSAPI, or Generic Security Service Application Program Interface, which uses Kerberos to authenticate users. LDAP, or Lightweight Directory Access Protocol, authenticates users against an LDAP server. RADIUS, or Remote Authentication Dial-In User Service, authenticates users against a RADIUS server. These external authentication methods can secure a PostgreSQL database by providing a centralized way of managing user access and eliminating the need to store passwords in the database. However, it’s important to note that GSSAPI and RADIUS are only supported in PostgreSQL server versions 9.1 or later, and LDAP is a built-in feature for all versions.

LDAP (Lightweight Directory Access Protocol)

LDAP is a popular authentication method for enterprise environments, and it can also be used with PostgreSQL. In this section, we will go over how to set up LDAP authentication for PostgreSQL and provide an example of how it can be configured. Before you begin, you will need access to an LDAP server and a basic understanding of how it works. You will also need to have PostgreSQL installed and running on your server.

Step 1: Install the OpenLDAP server and client packages

Step 2: Configure the OpenLDAP server

  • Select “No” when asked to configure the database with dbconfig-common.
  • Set the domain name for your LDAP server, for example, “example.com”.
  • Set the organization name for your LDAP server, for example, “Example Inc”.
  • Set the administrator password for your LDAP server.

Step 3: Create an LDAP directory structure

where “base.ldif” is a file containing the directory structure you want to create.

Step 4: Test the LDAP server

This command should return information about your LDAP directory.

Step 5: Add users and groups to the LDAP directory

where “users.ldif” is a file containing the users and groups you want to add to the directory.

Step 6: Test the user authentication

This command should return the DN (Distinguished Name) of the user, indicating that the user

Step 7: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable LDAP authentication, you’ll need to add a line to the pg_hba.conf file that specifies the LDAP server and method to be used.

For example:

This line tells PostgreSQL to use the LDAP server at ldap.example.com for authentication using the “simple” method.

Step 8: Restart PostgreSQL

After making changes to the pg_hba.conf file, you will need to restart the PostgreSQL service for the changes to take effect.

Step 9: Test the LDAP connection

You can test the connection to the LDAP server by running the following command:

This command will bind to the LDAP server as the “cn=admin,dc=example,dc=com” user with the password “adminpassword”. If the connection is successful, you should see a message indicating the user you are currently bound as.

Step 10: Test LDAP authentication

To test LDAP authentication with PostgreSQL, you can connect to the database using the psql command-line interface. For example,

If the LDAP authentication is set up correctly, you should be able to connect to the database using the myldapuser’s LDAP credentials.

GSSAPI (Generic Security Service Application Program Interface)

GSSAPI is an authentication method that allows users to authenticate to PostgreSQL using Kerberos. In this section, we will go over how to set up GSSAPI authentication for PostgreSQL and provide an example of how it can be configured.

Before you begin, you will need to have a Kerberos infrastructure and a basic understanding of how it works. You will also need to have PostgreSQL installed and running on your server and the Kerberos libraries (gssapi and gssapi_krb5) installed on the server where PostgreSQL is running.

Step 1: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable GSSAPI authentication, you’ll need to add a line to the pg_hba.conf file that specifies the GSSAPI method to be used. For example:

This line tells PostgreSQL to use GSSAPI for authentication and not to include the Kerberos realm in the username. 

Step 2: Restart PostgreSQL

After making changes to the pg_hba.conf file, you will need to restart the PostgreSQL service for the changes to take effect. If you get this error, that means your server is not configured with –with-gssapi option.

Step 3: Kerberos configuration

You will need to configure Kerberos to work with PostgreSQL. You will need to create a service principal for the PostgreSQL server and add it to the Kerberos keytab file.

Step 4: Test the GSSAPI connection

You can test the GSSAPI connection by running the following command:

This command will acquire a Kerberos ticket for the service principal you created in step 3.

Step 5: Test GSSAPI authentication

To test GSSAPI authentication with PostgreSQL, you can connect to the database using the psql command-line interface. For example:

If the GSSAPI authentication is set up correctly, you should be able to connect to the database using your Kerberos credentials.

SSPI (Security Support Provider Interface)

SSPI is an authentication method that allows users to authenticate to PostgreSQL using Windows’ built-in security features. It is similar to GSSAPI but specific to the Windows operating system.

The PostgreSQL server uses the SSPI library to negotiate authentication with the client using the Kerberos or NTLM protocols. To set up SSPI authentication in PostgreSQL, you must configure the pg_hba.conf file to use the “sspi” method and restart the PostgreSQL service.

For example, to enable SSPI authentication for all connections from the local host, you would add the following line to the pg_hba.conf file:

This line tells PostgreSQL to use SSPI for authentication for all connections coming from the IP address 127.0.0.1, which corresponds to the localhost. Once the changes are made, you will need to restart the PostgreSQL service for the changes to take effect. To test the SSPI authentication, you can connect to the database using the psql command-line interface. SSPI authentication is only supported on Windows and is available on PostgreSQL server version 9.1 or later.

RADIUS (Remote Authentication Dial-In User Service)

RADIUS is a widely used protocol for authenticating remote users. It can also be used to authenticate users in a PostgreSQL database. In this blog post, we will go over how to set up RADIUS authentication for PostgreSQL and provide an example of how it can be configured.

Before you begin, you will need access to a RADIUS server and a basic understanding of how it works.

Step 1: Install FreeRADIUS  

Here are the steps to install and configure a RADIUS server on Ubuntu:

Step 2: Configure the RADIUS server

  • The main configuration file for FreeRADIUS is located at /etc/freeradius/radiusd.conf.
  • The users file is located at /etc/freeradius/users.

Step 3: Add users to the RADIUS server

Add a new entry for each user in the following format:

Step 4: Restart the RADIUS server

Step 5: Test the RADIUS server

If the test is successful, you should receive an “Access-Accept” response.

Step 6: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable RADIUS authentication, you’ll need to add a line to the pg_hba.conf file that specifies the RADIUS server and method to be used. For example:

This line tells PostgreSQL to use RADIUS for authentication.

Step 7: Restart PostgreSQL

After making changes to the pg_hba.conf and radius.conf files, you will need to restart the PostgreSQL service for the changes to take effect.

Step 8: Test RADIUS authentication

To test RADIUS authentication with PostgreSQL, you can try to connect to the database using the psql command-line interface. For example,

If the RADIUS authentication is set up correctly, you should be able to connect to the database using your RADIUS credentials.

Conclusion

External authentication methods like LDAP, GSSAPI, and RADIUS make PostgreSQL more secure by centralizing user management and reducing the risks of weak or scattered credentials. They’re a smart choice for production systems and remote access, especially when compliance is on the line. But authentication is just one piece of a much bigger security picture. If you want to see how all the layers fit together and what else to consider, we’ve put together a resource that covers it in detail.

 

How Percona for PostgreSQL Meets Compliance and Security Standards

Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments