With this Application, you can provide automatic ldap sync to your postgresql architectures installed on operating systems such as RedHat, Centos, Rocky linux, Oracle Linux.

• Configurable per YAML config file
• Can use Active Directory as LDAP-Server
• Nested groups/roles supported
• Set scope of considered users/groups on LDAP and PG side
• Test mode which doesn't do any changes to the DBMS
• Both LDAP and PG connections can be secured by SSL/TLS

• Ruby-2.0+, JRuby-1.2+
• LDAP-v3 server
• PostgreSQL-server v9.0+
• Oracle-Linux/Centos/Rhel/RockyLinux

  $ yum install -y ruby rubygem-rake rubygems ruby-devel openldap-clients git wget tar curl make  rubygem-bigdecimal.x86_64 redhat-rpm-config libpq-devel.x86_64 gcc nano

$ git clone https://github.com/rkazak07/Postgresql-ldap-sync.git

$ cd Postgresql-ldap-sync

$ gem install bundler

$ bundle install

$ bundle exec rake install

$ gem install json

$ which pg_ldap_sync

We add roles for groups and users in Postgresql.

 $ sudo -su postgres psql

 $ create role ldap_users;

 $ create role ldap_groups;

  \du

Create a config file based on config/pg-ldap-sync-config.yaml

Run in test-mode:

  $ pg_ldap_sync -c my_config.yaml -vv -t

Run in modify-mode:

  $ pg_ldap_sync -c my_config.yaml -vv

Check whether the users taken from the active directory are written to Postgresql. If users appear in roles when you run the below command, they have been successfully added.

$ sudo -su postgres psql

 \du

$ ldapsearch -x -h ad-host-ip -D "[email protected]" -W "(sAMAccountName=*)" -b "OU=pgusers,OU=Service_Users,OU=organization-unit,DC=domain,DC=local"  | grep    sAMAccountName

Ldap Example

#filter: (sAMAccountName=*) sAMAccountName: user1 sAMAccountName: user2

Postgresql pg_hba.conf add ldap sync parameters

$ nano /var/lib/pgsql/13/data/pg_hba.conf

host all all 0.0.0.0/0 ldap ldapserver=domain-host ldapport=389 ldapprefix="" ldapsuffix="@domain.local" ldapscheme=ldap

$ systemctl restart postgresql-14

Now we are setting the user that will create the roles and authorizations between postgresql' and AD from the users we have added to the database.

  $ sudo -su postgres psql

$ create role "user1" superuser createdb createrole;

If we are going to create a user from existing ones, we are changing its authority

$ alter role "user1" superuser createdb createrole;

We will authorize the pggroup we created via AD to postgres.

$ drop role pggroup;

$ create role pggroup in role ldap_groups;

$ grant CONNECT ON DATABASE postgres to pggroup;

Postgresql Database Ldap Login Control

$ psql -h db-host-ip -U "ldapuser" -d postgres

We need to create a cronjob so that the pg-ldap-sync.yaml file we created can pull the users added to the pggroup via AD in certain periods.

 $ sudo yum -y install crontabs

We specify pg-ldap-sync and its runtime to the crontab.

$ crontab -e