Would you like to learn how to configure PostgreSQL LDAP authentication on Active directory? In this tutorial, we are going to show you how to authenticate PostgreSQL users using the Active directory database from Microsoft Windows and the LDAP protocol.

• Ubuntu 19.10
• PostgreSQL 12.1

PostgreSQL Related Tutorial:

On this page, we offer quick access to a list of tutorials related to PostgreSQL.

Tutorial - Windows Domain Controller Firewall

First, we need to create a Firewall rule on the Windows domain controller.

This firewall rule will allow the PostgreSQL server to query the Active directory database.

On the domain controller, open the application named Windows Firewall with Advanced Security

Create a new Inbound firewall rule.

zabbix active directory

Select the PORT option.

Select the TCP option.

Select the Specific local ports option.

Enter the TCP port 389.

zabbix windows firewall port ldap

Select the Allow the connection option.

zabbix windows firewall allow connection

Check the DOMAIN option.

Check the PRIVATE option.

Check the PUBLIC option.

Enter a description to the firewall rule.

windows firewall active directory

Congratulations, you have created the required firewall rule.

This rule will allow PostgreSQL  to query the Active directory database.

Tutorial - Windows Domain Account Creation

Next, we need to create at least 2 accounts on the Active directory database.

The BIND account will be used to query the Active Directory database.

The ADMIN account will be used as an example to access the PostgreSQL service.

On the domain controller, open the application named: Active Directory Users and Computers

Create a new account inside the Users container.

Zabbix active directory account

Create a new account named: admin

Password configured to the ADMIN user: 123qwe..

This account will be used to authenticate as an example.

active directory admin accountzabbix active directory admin properties

Create a new account named: bind

Password configured to the BIND user: 123qwe..

This account will be used to query the passwords stored on the Active Directory database.

active directory bind accountzabbix active directory ldap bind properties

Congratulations, you have created the required Active Directory accounts.

Tutorial PostgreSQL - LDAP Authentication on Active Directory

On the Linux console, use the following commands to check your Ubuntu codename.

Copy to Clipboard

In our example, Ubuntu 19.10 codename is eoan.

Take note of this information.

On the Linux console, use the following commands to add the PostgreSQL APT repository

Copy to Clipboard

Install the PostgreSQL service.

Copy to Clipboard

Use the following commands to create a new account named admin.

Copy to Clipboard

Here is the command output:

Copy to Clipboard

Locate and edit the pg_hba.conf configuration file.

Copy to Clipboard

Add the following line at the beginning of this file.

Copy to Clipboard

Here is our pg_hba.conf file content:

Copy to Clipboard

We performed the following configuration:

• LDAP Server IP - 34.221.44.138
• Search Scope - Entire Subtree
• Base DN - CN=Users,DC=tech,DC=local
• Authentication containers - CN=Users,DC=tech,DC=local
• Bind credentials - CN=bind,CN=Users,DC=tech,DC=local
• Bind credentials Password - Password of the BIND user account 123qwe..
• User naming attribute - samAccountName

You need to change the IP address to your domain controller IP.

You need to change the domain information to reflect your Network environment.

You need to change the bind credentials to reflect your Network environment.

Restart the PostgreSQL service.

Copy to Clipboard

Connect to the PostgreSQL server using the admin account and the password set on the Active directory.

Copy to Clipboard

Here is the command output:

Copy to Clipboard

Congratulations! You have configured the PostgreSQL authentication to use the Active Directory database.