×
Community Blog Setup RDS for PostgreSQL LDAP Authentication with Active Directory Deployed on ECS

Setup RDS for PostgreSQL LDAP Authentication with Active Directory Deployed on ECS

This tutorial shows you how to deploy and setup Microsoft AD on ECS Windows Server 2016 or later, and setup LDAP Authentication with the AD Domain Service for RDS for PostgreSQL on Alibaba Cloud.

You can access the tutorial artifact including deployment script (Terraform) from the github project

More tutorial around Alibaba Cloud Database, please refer to this link.

Overview

Active Directory (AD) is a directory service developed by Microsoft for Windows domain networks. It is included in most Windows Server operating systems as a set of processes and services.

In this solution tutorial, let's see how to deploy and setup Microsoft AD on ECS Windows Server 2016 or later, and setup LDAP Authentication with this AD Domain Service for RDS for PostgreSQL on Alibaba Cloud.

Deployment architecture of this tutorial:

1

Index

Step 1. Use Terraform to provision ECS and RDS PostgreSQL database on Alibaba Cloud
Step 2. Setup AD DS and LDAP user for RDS PostgreSQL database authentication
Step 3. Configure AD DS information on RDS PostgreSQL
Step 4. Verify the AD LDAP authentication for RDS PostgreSQL

Step 1. Use Terraform to provision ECS and RDS PostgreSQL database on Alibaba Cloud

If you are the 1st time to use Terraform, please refer to this link to learn how to install and use the Terraform on different operating systems.

Run the terraform script to initialize the resources (in this tutorial, we use 1 RDS for PostgreSQL, 1 Windows Server 2019 ECS for AD installation and 1 Ubuntu ECS for demo database application connecting to RDS PostgreSQL). Please specify the necessary information and region to deploy.

2

After the Terraform script execution finished, the ECS instance and RDS for PostgreSQL information are listed as below.

3

  • ad_ecs_private_ip: The private IP of the Windows Server ECS with Microsoft AD installation
  • ad_ecs_public_ip: The public IP of the Windows Server ECS with Microsoft AD installation
  • demo_ecs_public_ip: The public IP of the ECS for demo database application
  • rds_pg_url: The RDS for PostgreSQL database connection URL
  • rds_pg_port: The RDS for PostgreSQL database service port

Step 2. Setup AD DS and LDAP user for RDS PostgreSQL database authentication

Using Remote desktop to connect to Windows Server ECS. The log on password for user administrator is WindowsN1cetest, which is predefined in the terraform script.

Follow the screenshots below to setup the Microsoft AD DS on the Windows Server ECS.

4
5

6
7

8
9

10
11

12
13

14
15

16
17

18

Now, the AD DS and DNS Server have been setup successfully.

19
20
21

Configure the AD domain name. In this tutorial, we use pgsqldomain.net.

22

Set password for DSRM (Directory Services Restore Mode):

23
24
25
26
27
28

After the installation procedure finished, then the basic AD DS has been setup successfully. Then follow the steps to add users of domain administrator and database user for RDS PostgreSQL:

29
30

Define the domain administrator name as dbadmin and set the password. In this tutorial, let's set it as N1cetest, which will be used in RDS PostgreSQL AD DS setting.

31
32
33

Then set this domain administrator dbadmin as the member of the Domain Admins group.

34

35

Similarly, add database user for RDS PostgreSQL:

36

Define the database user name as ldapuser and set the password. In this tutorial, let's set it as ADN1cetest, which will be used as the password to connect to RDS PostgreSQL. Please be noticed that, there must be an account also with the name ldapuser created in RDS PostgreSQL.

37
38
39
40

Now, the AD has been setup successfully on Windows Server ECS.

Step 3. Configure AD DS information on RDS PostgreSQL

Log on to the RDS PostgreSQL web console, then follow the screenshots below to setup the Microsoft AD DS information on RDS PostgreSQL.

41

When editing the AD domain service, the content of the OPTION column should be set as:

ldapserver=<ECS AD Server Private IP> ldapbasedn="CN=Users,DC=pgsqldomain,DC=net" ldapbinddn="CN=<Domain Administrator User Name in AD>,CN=Users,DC=pgsqldomain,DC=net" ldapbindpasswd="<Domain Administrator User Password in AD>" ldapsearchattribute="sAMAccountName"
  • <ECS AD Server Private IP>: should be ad_ecs_private_ip in Step 1
  • <Domain Administrator User Name in AD>: should be the Domain Administrator User Name defined in Step 2, that is dbadmin in this tutorial
  • <Domain Administrator User Password in AD> should be the Domain Administrator User Password in Step 2, that is N1cetest in this tutorial
  • DC should be set for pgsqldomain.net in this tutorial

Such as the content is the following specifically,

ldapserver=192.168.0.35 ldapbasedn="CN=Users,DC=pgsqldomain,DC=net" ldapbinddn="CN=dbadmin,CN=Users,DC=pgsqldomain,DC=net" ldapbindpasswd="N1cetest" ldapsearchattribute="sAMAccountName"

42

Then add another record as following.

43
44

After submit the changes, the instance is going into Maintaining Instance status, and waiting for complete and back to the Running status.

45

Now, the AD has been setup successfully on RDS PostgreSQL.

Step 4. Verify the AD LDAP authentication for RDS PostgreSQL

Please log on to ECS with <demo_ecs_public_ip> and the password is N1cetest by default, which is preset in the terraform provision script in Step 1. If you've already changed it, please update accordingly.

ssh root@<demo_ecs_public_ip>

Execute the command to connect to RDS PostgreSQL:

psql -h <rds_pg_url> -U ldapuser -p <rds_pg_port> -d postgres
  • <rds_pg_url>: the in Step 1
  • <rds_pg_port>: the in Step 2

Such as the command is like below, please use the password of database user defined in Microsoft AD configured in Step 2 (it is ADN1cetest in this tutorial). If the connection succeeds, then all the setup and configuration is successful.

psql -h pgm-3nsl6a419da052iy168210.pg.rds.aliyuncs.com -U ldapuser -p 5432 -d postgres

46

0 1 0
Share on

ApsaraDB

448 posts | 95 followers

You may also like

Comments