Using ldap2pg to integrate between AD Groups and Postgres Roles
On a recent customer call regarding Postgres instances and databases provisioned by Data Services Manager (DSM), there was a question regarding Active Directory / LDAP integration. Specifically, there was a question on how to automatically provide an Active Directory / LDAP group access to a Postgres database. Now, as many readers are aware, DSM already supports Directory Services such as Active Directory and LDAPS for Postgres databases. See this earlier blog post on how to do that. But once Directory Services is configured in DSM, and selected during Postgres database provisioning, the DBA needs to create logins and grant access to the database for each individual user. I wanted to take this a step further, and figure out how we could automate the integration of a group of users from Active Directory, and set them up with logins and database access. This led me to the ldap2ad tool. After some trial and error in the lab, I though this might be a useful tool for others facing this requirement. This is why I decided to create this post. Here you will find the steps to search for all the users in an AD group, and automatically set them up with login and superuser access to a particular database.
I began reading up the instructions on how to user ldap2pg from here. This also contains the installation instructions for ldap2pg. The first step that I wanted to test if I could retrieve the member of my AD Group using the ldapsearch tool from my Ubuntu Linux VM. I used the inbuilt ldapsearch tool to do that particular test on my AD Group called DSMUsers-Finance. In this example, user ‘cormac’ is a Domain Admin, and my domain is rainpole.com. My Domain Controller running Active Directory is dc01.rainpole.com. I was able to successfully retrieve the three members of the group using the following command.
$ ldapsearch -H ldaps://dc01.rainpole.com:3269 -cxWD "cn=cormac,cn=users,dc=rainpole,dc=com" \
-LLL -x -b "cn=users, dc=rainpole, dc=com" -s sub cn=DSMUsers-Finance member
Enter LDAP Password: *******
dn: CN=DSMUsers-Finance,CN=Users,DC=rainpole,DC=com
member: CN=dragomir,CN=Users,DC=rainpole,DC=com
member: CN=junchi,CN=Users,DC=rainpole,DC=com
member: CN=paudie,CN=Users,DC=rainpole,DC=com
Once that was working, the next step was to setup a bunch of ENV variables for both LDAP and Postgres in my user’s shell on my Ubuntu Linux VM:
cormac@cjh-ubuntu-01:~/ldap2pg$ env
.
.
PGPORT=5432
PGPASSWORD=xxxxx
PGUSER=pgadmin
PGDATABASE=marketing-pg-01
PGHOST=marketing-sec01.rainpole.com
LDAPPASSWORD=xxxxx
.
.
I also need to add entries for URI, LDAPSASL_AUTHCID and BINDDN to my /etc/ldap/ldap.conf. These are below, but are similar to some of the arguments used in the ldapsearch command seen earlier:
cormac@cjh-ubuntu-01:~/ldap2pg$ cat /etc/ldap/ldap.conf # # LDAP Defaults # # See ldap.conf(5) for details # This file should be world readable but not world writable. #BASE dc=example,dc=com #URI ldap://ldap.example.com ldap://ldap-provider.example.com:666 URI ldaps://dc01.rainpole.com:3269 LDAPSASL_AUTHCID cn=cormac,cn=users,dc=rainpole,dc=com BINDDN cn=cormac,cn=users,dc=rainpole,dc=com #SIZELIMIT 12 #TIMELIMIT 15 #DEREF never # TLS certificates (needed for GnuTLS) TLS_CACERT /etc/ssl/certs/ca-certificates.crt TLS_REQCERT never
Next, make the configuration file for ldap2pg. It is YAML formatted. I won’t go into all the details, but in a nutshell, I am asking ldap2pg to ignore all of the roles in the database by adding those entries to the “roles_blacklist_query” section. These roles are thus not impacted by this utility. I am then asking it to search through users that are members of the DSMUsers-Finance group and create new roles on the database with LOGIN and Superuser privileges using the “userPrincipalName” that it discovers for those users. The LDAP configuration for the database as implemented by DSM Directory Services is looking for the userPrincipalName. A snippet of the pg_hba.conf is here, and queried from the pg_hba_file_rules table on the database. Note that the ldapsearchattribute is set to userPrincipalName, as per the DSM Directory Services configuration. This will be set to whatever ldapsearchattribute was set on the Directory Services configaruation by the way, but it does default to userPrincipalName.
26 | /pgsql/data/pg_hba.conf | 161 | host | {all} | {all} | 0.0.0.0 |
0.0.0.0 | ldap |{ldapserver=dc01.rainpole.com,ldapport=3269,ldapscheme=ldaps,
"ldapbasedn=dc=rainpole,dc=com",ldapbinddn=cormac@rainpole.com,ldapbindpasswd=VMware123!,
ldapsearchattribute=userPrincipalName,ldapscope=2} |
And here is the Directory Services configuration from DSM:
The script will pick up the database from the environment variables that I set earlier, and will also pick up the LDAP password through environment variables. The other LDAP details are coming from the /etc/ldap/ldap.conf.
cormac@cjh-ubuntu-01:~/ldap2pg$ cat ldap2pg.yml version: 6 postgres: roles_blacklist_query: - postgres - postgres_exporter - "pg*" - "*slot*" rules: - description: "Sync roles from LDAP to PostgreSQL" ldapsearch: base: "cn=Users,dc=rainpole,dc=com" filter: (memberOf=CN=DSMUsers-Finance,CN=Users,DC=rainpole,DC=com) roles: - name: "{userPrincipalName.lower()}" options: LOGIN SUPERUSER
Start with a dry-run of the configuration. Note that it tells us what it will do. Basically, if run for real, it would create 3 new roles corresponding to the users in my AD group. These are the same users reported in my LDAP search output above, so this looks good.
cormac@cjh-ubuntu-01:~/ldap2pg$ ./ldap2pg -c ldap2pg.yml 11:39:20 INFO Starting ldap2pg version=6.4.2 commit=07ed9548 runtime=go1.24.2 pid=195160 11:39:20 INFO Using YAML configuration file. path=ldap2pg.yml 11:39:20 WARN Dry run. Postgres instance will be untouched. 11:39:20 INFO Running as superuser. user=pgadmin super=true server="PostgreSQL 17.5" cluster="" database=marketing-pg-01 11:39:20 INFO Connected to LDAP directory. uri=ldaps://dc01.rainpole.com:3269 11:39:20 INFO Sync roles from LDAP to PostgreSQL 11:39:20 CHANGE Would Create role. role=paudie database=marketing-pg-01 11:39:20 CHANGE Would Set role comment. role=paudie database=marketing-pg-01 11:39:20 CHANGE Would Create role. role=junchi database=marketing-pg-01 11:39:20 CHANGE Would Set role comment. role=junchi database=marketing-pg-01 11:39:20 CHANGE Would Create role. role=dragomir database=marketing-pg-01 11:39:20 CHANGE Would Set role comment. role=dragomir database=marketing-pg-01 11:39:20 INFO Comparison complete. searches=1 roles=3 queries=6 grants=0 11:39:20 INFO Use --real option to apply changes. 11:39:20 INFO Done. elapsed=53.136845ms mempeak=1.2MiB ldap=658.263µs inspect=13.079637ms sync=0s
Let’s now run it for real by adding the ‘–real’ option to the ldap2pg command.
cormac@cjh-ubuntu-01:~/ldap2pg$ ./ldap2pg -c ldap2pg.yml --real 11:40:18 INFO Starting ldap2pg version=6.4.2 commit=07ed9548 runtime=go1.24.2 pid=195319 11:40:18 INFO Using YAML configuration file. path=ldap2pg.yml 11:40:18 INFO Real mode. Postgres instance will be modified. 11:40:18 INFO Running as superuser. user=pgadmin super=true server="PostgreSQL 17.5" cluster="" database=marketing-pg-01 11:40:18 INFO Connected to LDAP directory. uri=ldaps://dc01.rainpole.com:3269 11:40:18 INFO Sync roles from LDAP to PostgreSQL 11:40:18 CHANGE Create role. role=paudie database=marketing-pg-01 11:40:18 CHANGE Set role comment. role=paudie database=marketing-pg-01 11:40:18 CHANGE Create role. role=junchi database=marketing-pg-01 11:40:18 CHANGE Set role comment. role=junchi database=marketing-pg-01 11:40:18 CHANGE Create role. role=dragomir database=marketing-pg-01 11:40:18 CHANGE Set role comment. role=dragomir database=marketing-pg-01 11:40:18 INFO Comparison complete. searches=1 roles=3 queries=6 grants=0 11:40:18 INFO Done. elapsed=73.766163ms mempeak=1.2MiB ldap=669.39µs inspect=20.893096ms sync=5.861424ms
Now let’s check if it worked by logging into the database as pgadmin, and displaying the roles and logins. Let’s see if our users from the group are present.
$ psql postgresql://pgadmin:xxxxx@marketing-sec01.rainpole.com:5432/marketing-pg-01 psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 17.5 (VMware Postgres 17.5.0)) WARNING: psql major version 16, server major version 17. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. marketing-pg-01=# SELECT rolname FROM pg_roles WHERE rolsuper = true; rolname --------------------------- postgres pgadmin pgsql-rep-user pgsql-rep-user-fin paudie@rainpole.com junchi@rainpole.com psql-rep-slot-sec-user dragomir@rainpole.com pgautofailover_replicator psql-rep-slot-sec-user2 (10 rows) marketing-pg-01=# SELECT rolname FROM pg_roles WHERE rolcanlogin = true; rolname --------------------------- postgres pgautofailover_monitor postgres_exporter pgadmin pgappuser pgsql-rep-user pgsql-rep-user-fin paudie@rainpole.com junchi@rainpole.com psql-rep-slot-sec-user dragomir@rainpole.com pgrouser pgautofailover_replicator psql-rep-slot-sec-user2 pgrwuser (15 rows) marketing-pg-01=# \q
It certainly seems to have picked up the userPrincipalNames of each of the group members. And it does appear that have both Superuser and Login capabilities. Now lets see if we can login to the database as one of those users. I am choosing user junchi@rainpole.com. Make sure you unset the PGPASSWORD environment variable btw, or psql will pick that up for this login attempt.
$ psql postgresql://marketing-sec01.rainpole.com:5432/marketing-pg-01 -U junchi@rainpole.com Password for user junchi@rainpole.com: ****** psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 17.5 (VMware Postgres 17.5.0)) WARNING: psql major version 16, server major version 17. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. marketing-pg-01=# \conninfo You are connected to database "marketing-pg-01" as user "junchi@rainpole.com" on host "marketing-sec01.rainpole.com" (address "xx.xx.xx.xx") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) marketing-pg-01=# \q
Well that all appears to have worked really well. It certainly seems that ldap2pg could be a useful tool for any admin to have access to, particularly if they need to give lots of AD/LDAP users access to various databases. There is obviously more granular controls in tools around permissions and grants, but this was just a simple proof-of-concept. Check out the rest of the ldap2pg documentation to learn more. And for anyone who is looking for an on-premises DBaaS offering for the VMware Cloud Foundation (VCF) deployment, be sure to check out Data Services Manager and all of the benefits that it brings, from automated lifecycle management to fleet management to automated backups.
