Database security is crucial to protecting sensitive information stored in a database. A critical aspect is authentication, which refers to verifying the identity of a user trying to access the database. The authentication process is typically accomplished through usernames and passwords and includes other factors such as security tokens and one-time passwords. Database Administrators (DBAs) must implement robust authentication mechanisms to ensure that only authorized users can access the database and its data. Additionally, it is best practice to monitor regularly and audit database authentication logs to detect and prevent unauthorized access attempts.
PostgreSQL server authentication refers to verifying the identity of a user trying to connect to the PostgreSQL server. This can be done using various methods, including password-based authentication, a client certificate, and external authentication methods such as GSSAPI, LDAP, and RADIUS. The pg_hba.conf file is used to configure the authentication methods the PostgreSQL server uses and can specify different methods for different types of connections. By default, PostgreSQL uses trust-based authentication, where any connection from localhost is considered trusted and doesn’t require a password. However, it’s important to note that it’s recommended to use password-based or external authentication methods for remote connections and production environments to ensure secure access to the data.
When talking about security, authentication is the first line of defense. PostgreSQL provides various methods of authentication, which are categorized into three categories.
- PostgreSQL internal authentication
- OS-based authentication
- External server-based authentication
PostgreSQL internal authentication and OS-based authentication have already been discussed in previous blogs. Now it’s time to discuss external authentication methods.
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
|
1 2 |
sudo apt-get update sudo apt-get install slapd ldap-utils |
Step 2: Configure the OpenLDAP server
|
1 |
sudo dpkg-reconfigure slapd |
- 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
|
1 |
sudo ldapadd -x -D cn=admin,dc=example,dc=com -w <admin password> -f base.ldif |
where “base.ldif” is a file containing the directory structure you want to create.
Step 4: Test the LDAP server
|
1 |
ldapsearch -x -b dc=example,dc=com -D cn=admin,dc=example,dc=com -w <admin password> |
This command should return information about your LDAP directory.
Step 5: Add users and groups to the LDAP directory
|
1 |
ldapadd -x -D cn=admin,dc=example,dc=com -w <admin password> -f users.ldif |
where “users.ldif” is a file containing the users and groups you want to add to the directory.
Step 6: Test the user authentication
|
1 |
ldapwhoami -x -D cn=<user>,dc=example,dc=com -w <user password> |
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:
|
1 |
host all all ldapserver=ldap.example.com ldapmethod=simple |
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:
|
1 |
ldapwhoami -h ldap.example.com -D "cn=admin,dc=example,dc=com" -w "adminpassword" |
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,
|
1 |
psql -U myldapuser -h localhost |
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:
|
1 |
hostgssenc postgres postgres 192.168.0.102/32 gss include_realm=0 |
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.
|
1 |
2023-01-31 19:25:18.585 PKT [42302] LOG: hostgssenc record cannot match because GSSAPI is not supported by this build |
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:
|
1 |
kinit -k -t /path/to/keytab postgres/your.postgres.host@YOURREALM.COM |
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:
|
1 |
psql "dbname=mydb host=myhost user=myuser" |
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:
|
1 |
host all all 127.0.0.1/32 sspi |
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:
|
1 |
sudo apt-get update sudo apt-get install freeradius freeradius-utils |
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
|
1 |
sudo nano /etc/freeradius/users |
Add a new entry for each user in the following format:
|
1 |
username Auth-Type := Local, User-Password == "password" |
Step 4: Restart the RADIUS server
|
1 |
sudo service freeradius restart |
Step 5: Test the RADIUS server
|
1 |
sudo radtest username password 127.0.0.1 0 testing123 |
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:
|
1 |
host all all 0.0.0.0/0 radius radiusservers=192.168.1.1 radiussecrets=password radiusports=1812 |
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,
|
1 |
psql -U username -h localhost |
If the RADIUS authentication is set up correctly, you should be able to connect to the database using your RADIUS credentials.
Conclusion
In conclusion, external authentication methods such as GSSAPI, LDAP, and RADIUS can enhance the security of a PostgreSQL database by providing a centralized way of managing user access and eliminating the need to store passwords in the database. GSSAPI uses Kerberos to authenticate users, LDAP authenticates users against an LDAP server, and RADIUS authenticates users against a RADIUS server.
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. In addition to external authentication methods, the PostgreSQL server also supports password-based and client certificate-based authentication methods. It’s recommended to use password-based or external authentication methods for remote connections and production environments to ensure secure access to the data.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
About the Author
Ibrar AhmedJoined Percona in the month of July 2018. Before joining Percona, Ibrar worked as a Senior Database Architect at EnterpriseDB for 10 Years. Ibrar has 18 years of software development experience. Ibrar authored multiple books on PostgreSQL.
