Infrastructure at your Service

Daniel Westermann

Syncing Active Directory users and groups to PostgreSQL

A lot of companies use Active Directory to manage their users and groups. What most of this companies also want to do is to manage their database users and groups in Active Directory. PostgreSQL comes with ldap/kerberos authentication by default but does not provide anything that helps with managing users and groups in an external directory. And even for the authentication the user already needs to be existent in PostgreSQL. One tool you might want to have a look at and that helps with this requirement is pg-ldap-sync.

As usual I am using CentOS 7 for the scope of this post. For getting pg-ldap-sync onto the system PostgreSQL needs to be installed as pg_config is expected to be there. Once you have that several packages need to be installed (the openldap-clients is not required but it is handy to have it just in case you want to test some ldapsearch commands against Active Directory):

[root@pgadsync ~]$ yum install -y ruby rubygem-rake rubygems ruby-devel openldap-clients git

pg-ldap-sync can either be installed directly with ruby commands or you can install it from Git:

[postgres@pgadsync ~]$ git clone https://github.com/larskanis/pg-ldap-sync.git
[postgres@pgadsync ~]$ cd pg-ldap-sync
[postgres@pgadsync pg-ldap-sync]$ gem install bundler
[postgres@pgadsync pg-ldap-sync]$ bundle install
[postgres@pgadsync pg-ldap-sync]$ bundle exec rake install
[postgres@pgadsync pg-ldap-sync]$ which pg_ldap_sync 
~/bin/pg_ldap_sync
[postgres@pgadsync pg-ldap-sync]$ cd ..
[postgres@pgadsync ~]$ bin/pg_ldap_sync --help
Usage: bin/pg_ldap_sync [options]
    -v, --[no-]verbose               Increase verbose level
    -c, --config FILE                Config file [/etc/pg_ldap_sync.yaml]
    -t, --[no-]test                  Don't do any change in the database

And then, of course, you need something in the Active Directory for synchronization. In my test Active Directory I create a new “Organizational Unit” called “PostgreSQL”:

Selection_001

Inside this “Organizational Unit” there is a user which is used for authenticating against Active Directory:
Selection_002

Then we have two other “Organizational Units”, one for the PostgreSQL DBAs and one for the groups we’d like to sync:
Selection_003

There are three people in the pgadmins unit:
Selection_004

There is one group in the groups unit:
Selection_005

… and the group has two members:
Selection_006

This is what we want to synchronize to PostgreSQL. The final requirement is that two roles need to be there is PostgreSQL (you’ll notice later why that is important):

postgres@pgbox:/home/postgres/ [PG10] psql -X postgres
psql (10.3)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role ldap_users;
CREATE ROLE
postgres=# create role ldap_groups;
CREATE ROLE
postgres=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ldap_groups | Cannot login                                               | {}
 ldap_users  | Cannot login                                               | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# 

With pg-ldap-sync each instance you want to have synchronized needs a separate yaml file like this one:

# With this sample config the distinction between LDAP-synchronized
# groups/users from is done by the membership to ldap_user and
# ldap_group. These two roles has to be defined manally before
# pg_ldap_sync can run.

# Connection parameters to LDAP server
# see also: http://net-ldap.rubyforge.org/Net/LDAP.html#method-c-new
ldap_connection:
  host: 172.22.30.1
  port: 389
  auth:
    method: :simple
    username: CN=pgadsync,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
    password: xxxxx
#  encryption:
#    method: :simple_tls

# Search parameters for LDAP users which should be synchronized
ldap_users:
  base: OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
  # LDAP filter (according to RFC 2254)
  # defines to users in LDAP to be synchronized
#  filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
  filter: (sAMAccountName=*)
  # this attribute is used as PG role name
  name_attribute: sAMAccountName
  # lowercase name for use as PG role name
  lowercase_name: true

# Search parameters for LDAP groups which should be synchronized
ldap_groups:
  base: OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
  filter: (cn=dbas)
  # this attribute is used as PG role name
  name_attribute: cn
  # lowercase name for use as PG role name
  lowercase_name: false
  # this attribute must reference to all member DN's of the given group
  member_attribute: member

# Connection parameters to PostgreSQL server
# see also: http://rubydoc.info/gems/pg/PG/Connection#initialize-instance_method
pg_connection:
  host: 192.168.22.99
  dbname: postgres
  user: postgres
  password: postgres

pg_users:
  # Filter for identifying LDAP generated users in the database.
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_users')
  # Options for CREATE RULE statements
  create_options: LOGIN IN ROLE ldap_users

pg_groups:
  # Filter for identifying LDAP generated groups in the database.
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_groups')
  # Options for CREATE RULE statements
  create_options: NOLOGIN IN ROLE ldap_groups
#grant_options:

When you have a look at the “pg_users” and “pg_groups” you will notice why the two PostgreSQL roles created above are required. They are used to distinguish the users and groups coming from the directory and those created locally.

Ready to sync:

[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv 
I, [2018-07-23T14:23:46.350588 #29270]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.360073 #29270]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.363133 #29270]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.474105 #29270]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:23:46.517468 #29270]  INFO -- : user stat: create: 3 drop: 0 keep: 0
I, [2018-07-23T14:23:46.517798 #29270]  INFO -- : group stat: create: 1 drop: 0 keep: 0
I, [2018-07-23T14:23:46.518047 #29270]  INFO -- : membership stat: grant: 2 revoke: 0 keep: 0
I, [2018-07-23T14:23:46.518201 #29270]  INFO -- : SQL: CREATE ROLE "dba1" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.522229 #29270]  INFO -- : SQL: CREATE ROLE "dba2" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.525156 #29270]  INFO -- : SQL: CREATE ROLE "dba3" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:23:46.528058 #29270]  INFO -- : SQL: CREATE ROLE "dbas" NOLOGIN IN ROLE ldap_groups
I, [2018-07-23T14:23:46.531065 #29270]  INFO -- : SQL: GRANT "dbas" TO "dba3","dba1" 

… and that’s it. Users and groups are now available in PostgreSQL:

postgres=# \du
                                        List of roles
  Role name  |                         Attributes                         |     Member of     
-------------+------------------------------------------------------------+-------------------
 dba1        |                                                            | {ldap_users,dbas}
 dba2        |                                                            | {ldap_users}
 dba3        |                                                            | {ldap_users,dbas}
 dbas        | Cannot login                                               | {ldap_groups}
 ldap_groups | Cannot login                                               | {}
 ldap_users  | Cannot login                                               | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

When you add anther user to the directory:

Selection_007

… and run the sync again all remaining users will of course not be touched but the new one gets created (notice that I copied the dba4 in the directory, this is why the user is member of the dbas group):

[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv 
I, [2018-07-23T14:27:26.314729 #29273]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.323719 #29273]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.326764 #29273]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.328800 #29273]  INFO -- : found user-dn: CN=dba4,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.394066 #29273]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
I, [2018-07-23T14:27:26.434236 #29273]  INFO -- : found pg-user: "dba1"
I, [2018-07-23T14:27:26.434443 #29273]  INFO -- : found pg-user: "dba2"
I, [2018-07-23T14:27:26.434531 #29273]  INFO -- : found pg-user: "dba3"
I, [2018-07-23T14:27:26.439065 #29273]  INFO -- : found pg-group: "dbas" with members: ["dba3", "dba1"]
I, [2018-07-23T14:27:26.439357 #29273]  INFO -- : user stat: create: 1 drop: 0 keep: 3
I, [2018-07-23T14:27:26.439468 #29273]  INFO -- : group stat: create: 0 drop: 0 keep: 1
I, [2018-07-23T14:27:26.439656 #29273]  INFO -- : membership stat: grant: 1 revoke: 0 keep: 2
I, [2018-07-23T14:27:26.439759 #29273]  INFO -- : SQL: CREATE ROLE "dba4" LOGIN IN ROLE ldap_users
I, [2018-07-23T14:27:26.441692 #29273]  INFO -- : SQL: GRANT "dbas" TO "dba4" 

To more tips: When you want the complete ldap path for a user can do it like this:
Selection_008

It is advisable to test the filters you have in the yaml like:

[postgres@pgadsync ~]$ ldapsearch -x -h 172.22.30.1 -D "pgadsync@test.dbiservices.com" -W "(sAMAccountName=*)" -b "OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com"  | grep sAMAccountName
Enter LDAP Password: 
# filter: (sAMAccountName=*)
sAMAccountName: dba1
sAMAccountName: dba2
sAMAccountName: dba3
sAMAccountName: dba4

You might wonder how you can assign the permissions then. Just pre-create the role and give the permissions you want:

postgres=# drop role dbas;
DROP ROLE
postgres=# create role dbas in role ldap_groups;
CREATE ROLE
postgres=# grant CONNECT ON DATABASE postgres to dbas;
GRANT

The assignments to that group will come from the directory once you run the next synchronization.

Hope that helps …

 

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure