< Back to blog home Fujitsu PostgreSQL blog

Connecting Fujitsu Enterprise Postgres to Active Directory for Authentication using LDAP

Nishchay Kothari | June 15, 2023

System administrators can store data in databases securely and robustly with the aid of Fujitsu Enterprise Postgres. As many organizations use Active Directory to manage users and their resource permissions, it makes sense to integrate FEP into this authentication configuration as well. It also offers a variety of very dependable and secure authentication methods.

In this article, I'll show you how to set up FEP so that it can use LDAP to authenticate users from Active Directory.

What is LDAP?

LDAP (Lightweight Directory Access Protocol) was created by OpenLDAP as an open-source project.

A network's "directory services" can be maintained and accessed using the standard protocol known as LDAP. Consider a directory service as a phonebook for various network resources, such as users, devices, servers, printers, files, etc.

To create central authentication servers, this is frequently utilized. All network users' usernames and passwords are stored on these servers. The LDAP server can be accessed by any application or service to authenticate and authorize users.

Data in LDAP directories is often frequently accessed but rarely modified. Even for bigger datasets, LDAP is built to give incredibly quick READ performance. The WRITE performance, however, is noticeably worse.

Configure Active Directory user accounts

We must construct an AD user with a one-to-one relationship to a FEP database role to properly configure authentication with Active Directory. In other words, we must create a user with the identical login name on each system.

Starting with Active Directory, let's go on. In the Users container, create a new user and give it any name you choose. You will set up few users (dba, dev, and manager) for testing after configuring a test AD environment with the domain name "testldap.com".

In addition, you will make an AD user called "lookup" that can only be used for LDAP lookups.

1. Let’s create the “dba” user inside the AD as below.
2. Fill the necessary details as below and click on “Next >” button.
3. Fill the password and select the appropriate options as below and click “Next >” button.
4. Click on the “Finish” button.
5. Now you have created other users named “dev”, “manager” and “lookup” with the same above steps.
6. Now, you can see that all the newly created users are created successfully.

Configure Fujitsu Enterprise Postgres database users

You are going to create the database users “dba”, “dev”, and “manager” as shown below. The user will utilize AD to authenticate them. Therefore, these users do not require any passwords to be set.

(fsepuser@fepdb -)$ psql postgres
psql (14.0)
Type "help" for help.

postgres=# create role dba login ;
CREATE ROLE
postgres=# create role dev login ;
CREATE ROLE
postgres=# create role manager login ;
CREATE ROLE
postgres=# \du
                                  List of roles
Role name |                         Attributes                         | Member of
dba       |                                                            | ()
dev       |                                                            | ()
fsepuser  | Superuser, Create role, Create DB, Replication, Bypass RLS | ()
manager   |                                                            | ()

postgres=#

Configure Fujitsu Enterprise Postgres for remote connections

FEP is probably set up by default to only permit connections from the local server. You are going to make the following changes to the postgresql.conf configuration file to alter this behaviour. Save the file, close it, and then resume the FEP services:
listen_addresses = '*'

If you have already set as above, then you can ignore this step.

Configure Fujitsu Enterprise Postgres to query LDAP

In order for LDAP to be queried when someone wants to authenticate to FEP, we now need to install some LDAP-related packages and make some configuration file adjustments.

yum -y install openldap-clients openldap-servers

The ldapsearch command-line tool can be used as follows to determine whether your LDAP lookups are functioning properly or not.

ldapsearch -x -LLL -h add.testldap.com -D lookup@testldap.com -w 'Dec@2022' -b "dc=testldap,dc=com"

Where:

• ldapsearch is the tool that conducts the search
• -x specifies simple authentication
• -LLL prints responses in LDIF format without comments and version
• -h specifies the LDAP host to connect to
• -D indicates the user that will bind to AD
• lookup@testldap.com is the LDAP bind user and NETBIOS name of the domain
• -w specifies the password for the bind user
• -b is the base dn for the search

If the command is executed correctly, you should receive several pages of output containing information about the users, computers, and other domain objects.

The configuration of the FEP pg hba.conf file is now one of the most crucial procedures. Copy and paste the following into a blank line.

host all all 192.168.10.0/24 ldap ldapserver=add.testldap.com
ldapbasedn="cn=Users,dc=testldap,dc=com" ldapbinddn="CN=lookup,CN=Users,dc=testldap,dc=com" ldapbindpasswd="Dec@2022"
ldapsearchattribute="sAMAccountName"

Where:

• host all all 192.168.10.0/24 specifies which subnets (in this case, only the 192.168.10.0/24 subnet) should be allowed to connect remotely to this FEP database
• ldap is the authentication type
• ldapserver=add.testldap.com points to the IP/hostname of one of the domain controllers
• ldapbasedn="cn=Users,dc=testldap,dc=com” is the starting point in the domain’s hierarchy for your search
• ldapbinddn="CN=lookup,CN=Users,dc=testldap,dc=com" specifies the lookup user, which will authenticate to Active Directory to perform the searches
• ldapbindpasswd="Dec@2022" sets the password for the ldap user
• ldapsearchattribute="sAMAccountName" indicates the Active Directory attribute that will be searched for.

After the above changes let’s save and exit the file, then reload the FEP service to make the changes take effect.

[fsepuser@fepdb -]$ psql postgres
psql (14.0)
Type "help" for help.

postgres=# select pg_reload_conf(); pg_reload_conf();
---------------
 t
(1 row)

Connect to Fujitsu Enterprise Postgres

Let’s attempt to connect to the FEP server using the following syntax.

[fsepuser@fepdb -]$ psql -h 192.168.10.149 -U dba -d postgres
Password for user dba:
psql (14.0)
Type "help" for help.

postgres=> \q
[fsepuser@fepdb -]$ psql -h 192.168.10.149 -U dev -d postgres
Password for user dev:
psql (14.0)
Type "help" for help.

postgres=> \q [fsepuser@fepdb -]$ psql -h 192.168.10.149 -U manager -d postgres
Password for user manager:
psql (14.0)
Type "help" for help.

postgres=> \q
[fsepuser@fepdb -]$

After entering the password for the Active Directory, user will be able to login.

If we try to connect FEP with the other AD user which are not in Database, then it will throw error as below.

(fsepuserftepdb -1$ psql -h 192.168.10.149 -U lookup -d postgres
Password for user lookup:
psql: error: connection to server at "192.168.10.149", port 27500 failed: (20436)FATAL: liDAP authentication failed for user "lookup" (10684)
[fsepuser@fepdb -1$

Conclusion

In conclusion, PostgreSQL LDAP authentication is a strong feature that streamlines user administration while also improving security. It uses the LDAP protocol to delegate authentication to an LDAP server, providing a safe and efficient method of authenticating users in a PostgreSQL database. Organizations may increase overall security and optimize their user management operations by utilizing LDAP authentication.

Fujitsu Enterprise Postgres
leverages and extends the strength and reliability of PostgreSQL with additional enterprise features.

Compare the list of features.

Compare >

We also have a series of technical articles for PostgreSQL enthusiasts of all stripes, with tips and how-to's.