LDAP Authentication and Management for PostgreSQL
LDAP (Lightweight Directory Access Protocol) is a powerful tool for centralizing user authentication and management in PostgreSQL. By integrating LDAP, organizations can streamline user access control, enhance security, and simplify administrative tasks. In this guide, we’ll walk you through the process of setting up LDAP authentication and management for PostgreSQL, including prerequisites, installation, configuration, and verification.
Prerequisites for LDAP Authentication
Before implementing LDAP authentication for PostgreSQL, make sure you have the necessary prerequisites in place. Here’s what you’ll need:
1. Configure PostgreSQL & pgpool with LDAP Libraries
To enable LDAP authentication for PostgreSQL, you should configure PostgreSQL and pgpool with LDAP libraries during installation or recompilation. Use the following options when configuring:
sudo ./configure … –with-pam –with-ldap
2. Install Required Library Files
Ensure that the following library files are installed on your system:
sudo apt-get install ldap-utils -y
sudo apt-get install libpam0g-dev -y
3. Install `pg-ldap-sync` Tool
`pg-ldap-sync` is a tool that synchronizes users, groups, and their memberships from LDAP to PostgreSQL. Here’s how to install it:
- sudo apt-get install ruby libpq-dev
- git clone https://github.com/larskanis/pg-ldap-sync.git
- cd pg-ldap-sync
- gem install bundler
- bundle install
- bundle exec rake install
Configuration of `pg-ldap-sync` Tool
The `pg-ldap-sync` tool requires several configuration steps:
1. Configure LDAP Connection
You need an AD user with read-only privileges to communicate with the LDAP server. Create two AD users for syncing in different environments (e.g., production and non-production).
a. To sync AD users & Groups on Production DB environment : pg-ldap-sync_prd
b. To sync AD users & Groups on Non-Production DB environment : pg-ldap-sync_dev
2. Create DB Service Account
You’ll also need a DB service account with SUPERUSER privileges to manage AD users and groups in the database. Create different DB users for each environment.
3. Setup `pool_hba.conf` File on pgpool Nodes
Modify the `pool_hba.conf` file on pgpool nodes to allow AD user connections using the bind+search method to find users from the LDAP server.
Bind+Search:
Avoid to pass the hard coded AD server name(There is no VIP concept at AD server side to manage HA) in the pool_hba connection string we have another method to perform the authentication with AD server is search+Bind.
For non-production environment:
host all all 0.0.0.0/0 ldap ldapbasedn=”OU=users,DC=Shreeyansh,DC=local”,ldapsearchattribute=”sAMAccountName” ldapbinddn=”pg-ldap-sync_dev”, ldapbindpasswd=”AD_user_password”
For production environment:
host all all 0.0.0.0/0 ldap ldapbasedn=”OU=users,DC=Shreeyansh,DC=local”,ldapsearchattribute=”sAMAccountName” ldapbinddn=”pg-ldap-sync_prd”, ldapbindpasswd=”AD_user_password”
3. Sync AD Users in PostgreSQL DB
Create a `.yaml` file (e.g., `stg_pgldap_users_and_groups_sync.yaml`) and configure the parameters as follows:
ldap_connection:
host: Shreeyansh.ldap
port: 389
auth:
method: :simple
username: CN=pg-ldap-sync_dev,OU=DataNAnalytics,OU=Service Accounts,DC=Shreeyansh,DC=local
password: password # encryption # method: :simple_tls
ldap_users : Search parameters for LDAP users which should be synchronized
ldap_users:base:
OU=Shreeyansh,DC=Shreeyansh,DC=localfilter: (&(objectCategory=Person)(objectCategory=organizationalPerson)(sAMAccountName=*)(|(memberOf=CN=Test-group1,OU=Groups,OU=Ecommerce,OU=DataNAnalytics,OU=Application Groups,OU=Shreeyansh,DC=Shreeyansh,DC=local)(memberOf=CN=Test-group2,OU=Groups,OU=Ecommerce,OU=DataNAnalytics,OU=Application Groups,OU=Shreeyansh,DC=Shreeyansh,DC=local)(memberOf=CN=Data Engineering,OU=Application Groups,OU=Shreeyansh,DC=Shreeyansh,DC=local)(memberOf=CN=Shreeyansh Consultants,OU=Application Groups,OU=Shreeyansh,DC=Shreeyansh,DC=local)))
# this attribute is used as PG role namename_attribute: sAMAccountName# lowercase name for use as PG role namelowercase_name: true# Add lowercase name *and* original name for use as PG role names (useful for migrating between case types)bothcase_name: false
ldap_groups :
Search parameters for LDAP groups which should be synchronized.
ldap_groups:
base: OU=Application Groups,OU=Shreeyansh,DC=Shreeyansh,DC=localfilter: (|(cn=Test-group1)(cn=Test-group2)(cn=data engineering)(cn=shreeyansh consultants))# this attribute is used as PG role namename_attribute: CN# lowercase name for use as PG role namelowercase_name: true# this attribute must reference to all member DN’s of the given groupmember_attribute: member
pg_connection : Connection parameters to PostgreSQL server
pg_connection:
host: shree.stage
dbname: db_name
user: user
password: password
port: 9999
pg_users : This section is used to filter out and identify the LDAP generated users in the database.
pg_users:# 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=’Shreeyansh_ad_users’) # Options for CREATE RULE statementscreate_options: LOGIN in role Shreeyansh_ad_users# create_options: LOGIN # grant_options: Superuser Create role Create DB
pg_groups : This section is used to filter out and identify the LDAP generated groups in the database.
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=’Shreeyansh_ad_groups’)
filter: NOT rolcanlogin AND NOT rolsuper
Options for CREATE RULE statementscreate_options: NOLOGIN in role Shreeyansh_ad_groups
grant_options: superuser
pg-ldap-sync Usage :
Once the config file setup is done, run the yaml file in test mode to see the results or errors if any :
pg_ldap_sync -c /home/pgldap_sync_stg/pgldap_sync_config_files/shree_stg/shree_stg_pgldap_users_and_groups_sync.yaml -vv -t
If the dry run succeed then execute the below command, it will create user & groups in database :
pg_ldap_sync -c /home/pgldap_sync_stg/pgldap_sync_config_files/shree_stg/shree_stg_pgldap_users_and_groups_sync.yaml -vv
Verification:
You can describe the users inside DB and verify it, is our all work up to date or not?
Conclusion
LDAP authentication and management for PostgreSQL provide a robust solution for centralizing user access control and enhancing security. By following these steps and prerequisites, you can seamlessly integrate LDAP with PostgreSQL, simplify user management, and ensure a secure database environment.
