Using ldap2pg K8S CronJob to sync Directory Users/Groups and Postgresql roles/privileges

Demonstrate with Postgresql and OpenLDAP Helm Charts

Photo by Mykyta Martynenko on Unsplash

K8S rbac is pretty clear! We define “roles” to specify in which resources they can access. We define users or service accounts for end users or applications with “tokens”. We use rolebinding to associate users/serviceaccounts with the roles.

Like K8S rbac (role based access control), postgresql has its own rbac mechanism, pg_hba + pg_roles to authenticate users and pg_authid to authorize database objects. In my pg_hba series, I demonstrated how to intgrate external authentication methods in pg_hba.conf. LDAP auth is the most popular method. However, postgresql DBAs still need to pre-create or map postgresql “roles” to ldap users/groups (hint: inside pg_hba.conf and pg_ident.conf) and to maintain the authorization part (grant/revoke). When deploying postgresql to a cluster, we notice that we can configure pg_hba with ldap pretty easy. The gap is the authorization part: it is SQL, no yaml or json. In a legacy way, the best practices are:

1. Create NOLOGIN roles and grant some db object privileges to those roles.
2. Create LOGIN roles, aka users and make those users inherit roles.
3. Use ldap filter to make ldap users authenticated and to map ldap users to LOGIN roles.
4. “Figure” out how to drop users and revoke inherit roles when ldap users terminated by complicated ldapsearch and sql catalog joins.

Checking those items is one of DBA daily tasks. It is not fun.

In this article, we show how we manage the postgresql ldap authentication and authorization like k8s rbac by using ldap2pg as a k8s cronjob. In high level intro, ldap2pg uses a yaml file to describ the rolebinding between ldap entries and pg_authid/pg_role, and map the roles with some built-in privileges. With those built-in privileges, we don’t need to write sql statements in the yaml (although we love sql).

This article attempts to show how we make this daunted task easier for dba(s) step by step:

1. Deploy and test Openldap
2. Deploy and test postgresql with ldap auth
3. Test ldap2pg container
4. Deploy and test ldap2pg cronjob with simple configMap as ldap2pg.yml
5. Test and modify the ldap tree and validate the changes in postgresql.

Deploying Openldap using helm chart

First step, making a ldap tree is no fun. So we borrow helm-openldap with the following overrides. In the overrides, we specify the domain as “k8s.home.lab” and passwords (line 42 and 43) in the overrides. We use the credentials to add/modify the ldap tree. In line 45~83, we append two Ops (people and groups). In the people OU, we define two users (daniel and denis). In the groups OU, we define two groups (team1 and team2).

As the below script, we put the openldap service under namespace ldap (line 1). The openldap container provides ldap clients so we can run some simple tests to make sure we can do “simple” search and bind. Note that we are in the container, the ldap url is the container name (openldap-0). In line 4 of the script below, we kubectl exec to the openldap container and run ldapsearch with simple bind (-x), binddn (-D) and search for cn=daniel. In line 33, we run ldapwhoami to make sure we can bind as daniel. Both commands return correctly. Later, we will revisit those ldap client commands when we add/modify/delete ldap entries for further tests.

Deploying Postgresql 14 with ldap authentication

Second step, messing with pg_hba with ldap auth requires a lot of trials, errors, and fixes. To save some time, we deploy bitnami postgresql helm chart with the following “set” commands under pg namespace. Note that you need to escape the comma in values of baseDN and bindDN (line 7 and 8).

Once the chart deployed, use “helm status -n pg” for how to test and access the database. From line 11to line 40, we check the pg_hba_file_rules view and confirm that postgresql has the ldap authentication from 0.0.0.0/0. Note that we run “create role daniel login;” in line 43. Next, we can directly test if the ldap authentication works or not by running command in line 47~55 with daniel as user and changeme as password. So far so good, we have the psql prompt like line 58.

Testing ldap2pg container

Next small step, we attempt to run ldap2pg in a CLI way and to test what properties we should use when putting ldap2pg as a cronjob. CLI is a good way to test different ldap2pg parameters before we settle them in the ldap2pg.yml that ldap2pg will read automatically. In below script, we kubectl run ldap2pg docker image as a container in pg namespace. ldap2pg docker has a default entry-point, so I use “command” argu to get a bash terminal (line1). In line 2~17, I “tee” the ldap2pg yaml file that ldap2pg will read. Refer to ldap2pg doc for the yaml spec. Specifically, we hardcoded the PostgreSQL DSN (better idea: using kubectl to get pg secret or mount the secret as env) in postgres section, use the cn=admin as the binddn in ldap section (you are not supposed to use root here), and lastly set the base, scope and on_unexpected_dn flag. Then use cn with lower case as pg role name with LOGIN privilege in sync_map section (line 10~16). Note that ldap2pg doesn’t like the default umask of the yaml file and we chmod the yaml file to 400 in line 18.

In line 19, we test ldap2pg with the default “ — dry “ flag and we can confirm that ldap2pg will find users “denis” and “denial”, and would only create “denis” and update “daniel” (remember we pre-created this user?). In line 31, we run ldap2pg with “-N” flag for real mode.

To doublecheck, we login to postgresql container and confirm that user “denis” is created. Note that I created a role, test1 in postgresql which is not in ldap. As expected, this role will be dropped by ldap2pg (line41).

Creating ldap2pg Cronjob with ldap2pg.yml as ConfigMap

After confirming that ldap2pg works well, we make the ldap2pg yml file a configMap (line 1~23). We make this ldap2pg a Cronjob executed every minute (line 31). In line 42~50, we use volumeMount to mount the configMap to /etc/ldap2pg.yml where ldap2pg will pick up in default. Note that you need to set the configMap default mode 400 (line50), so ldap2pg won’t refuse to run due to the worldwide readable of the file (plain text password).

“kubectl get pod -n pg -w” shows the cronjob complete. If you see errors or crashbackloop, “kubectl logs -n pg pod-id” will show what was going on.

So far so good. Now we can sync individual ldap users to postgresql roles with the ldap2pg cronjob. Let the cronjob runs. We can focus on modifying ldap and change the configMap to do more “role-binding” like k8s rbac.

The first scenario: ldap user, “daniel” is assigned as the DBA. We create a ldap group called “dba” and make daniel a member of this group. We run a centos8 container as ldap-client and use ldapadd or ldapmodify commands as follow:

Modifying ldap is not a part of dba jobs. Normally, ldap activity is a part of the IT processes. So don’t worry too much about above ldap commands.

We turn our focus on the ldap2pg yaml file. You can fine an example in ldap2pg. In the below gist, we discuss “privileges”, “roles” and “grant” to the picture.

First, privileges are predefined wrappers of “grant” and “revoke” sql statements. We define ro, rw, and ddl privileges in line 18~29.

Inside the sync_map (line 30), roles are the pg_roles that ldap2pg would create and inherit.

In the “roles” block, we add “grant” to associate the privileges to those roles (line 42~51). Note that you can customize the grant and role with sql statements.

In the ldapsearch block, we modify the search to find the “dba” group (line 53,54) and assign the “member” with “owner” and “ldap_roles”roles. Since those predefined roles have NOLOGIN, we override LOGIN SUPERUSER in “options”.

The beauty of K8S cronJob: it picks up the change of mounted configMap. This make our job easy as just modifying/re-applying the configMap. After we apply this new configMap, we check the cronjob status.

The outcome looks good. We login our postgresql db and find that daniel is the superuser now!

Now, let’s add one more user to the dba group and see what happen. The following gist shows that user stan is in the dba group of ldap tree.

We do the same trick to check the outcome. “stan” is superuser too.

Refer to the example yaml and add team1 to writer role and team2 to reader role. We complete the ldap2pg yml file to reflect the ldap tree and those grant and revoke user queries will be off from DBA’s daily tasks.

In this tutorial, there are some security concerns and will need improvements.

1. passwords are exposed in many places.
2. Ldap StartTLS and postgresql TLS are off. Passwords are transmitted plaintext in this demo.

Next arctile, we will integrate vault to hide those passwords.

Stay tuned.