- Denish Patel
- 10th January 2018
- postgresql, security
I have connected our Postgres instance to Active Directory for authentication because I didn’t want to manage passwords for hundreds for users accessing mission critical databases. The authentication is being performed by Active Directory but authorization (managing groups, permissions at table/column/row level) is still be handled by Postgres. The biggest advantage of connecting Postgres to AD for real users, I don’t have to worry on password and password policies because the policies are handled by AD. On the other hand, users database authentication will be dependent on AD infrastructure. Please make sure AD infrastructure is redundant enough with low latency connection for your organization before making this decision.
I have come up with simple steps to connect Postgres to Active Directory. TBH, If you can get serviceAccount to be used to connect to organization’s active directory, the rest of the setup is fairly straight forward.
Get and active directory server name and test connection from Postgres server :
postgres-dbserver$ nc -v ldap-server.example.com 389 Connection to ldap-server.example.com 389 port [tcp/ldap] succeeded!
Install ldap-utils package so you can use ldap command line tools to test connections.
yum install ldap-utils or apt-get install ldap-utils
Ask your AD/LDAP admins to get full path of OU and CN for your organization and construct ldapsearch query. I’m using
sAMAccountName as username but you can use other attributes for username i.e
ldapsearch -v -x -H ldap://ldap-server.example.com -b "OU=Users,OU=local,DC=example,DC=com" -D "CN=service-account-username,OU=ServiceAccounts,OU=Users,OU=local,DC=example,DC=com" -W "(sAMAccountName=dpatel)"
If you are using
uid for the validation, your search query will look like this …
ldapsearch -v -x -H ldap://ldap-server.example.com -b "OU=Users,OU=local,DC=example,DC=com" -D "CN=service-account-username,OU=ServiceAccounts,OU=Users,OU=local,DC=example,DC=com" -W "(uid=dpatel)"
If the above query returns the result, you successfully found the user in the Active directory on specified path. If you can’t find user, please work with Active Directory Admin to find the correct
basedn (-b option in above query).
Add following line in pg_hba.conf and reload the config. Postgres reads pg_hba.conf from top to bottom. Please make sure to add this line at the end so authentication for other application users can be performed without active directory.
host all all 192.168.0.0/16 ldap ldapserver=ldap-server.example.com ldapbasedn="OU=Users,OU=local,DC=example,DC=com" ldapbinddn="CN=service-account-username,OU=ServiceAccounts,OU=Users,OU=local,DC=example,DC=com" ldapbindpasswd="Testing123" ldapsearchattribute="sAMAccountName"
* * Password with special characters like @! doesn’t parse properly in pg_hba.conf. Plus, I had to provide plain text password in pg_hba.conf for AD service account. If you know alternative, please let me know 🙂
psql> create role dpatel login;
Test the connection. Looks for Postgres logs for success/error.
Hopefully, this post will help someone to integrate Postgres with Active Directory using LDAP protocol.
- Running VACUUM FULL in Non-blocking Mode
- Connection Scaling
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?
- Postgres 11 partitioning