LDAP Authentication against AD
From PostgreSQL wiki
Howto SSL enable Postgresql LDAP Authentication against Active Directory
The following instruction applies to RPM installation via community repository at [1] . RPMs already have LDAP support. Please replace 9.4 and 94 in the instructions below with the exact PostgreSQL version >= 9.0, if you are using one of them.
- Install all the needed rpms as root. This package will pull the other dependencies:
yum -y install postgresql94-server
- Initialize the database
- On RHEL/CentOS/SL/OL 5&6:
/sbin/service postgresql-9.4 initdb
- On Fedora 19+, and RHEL 7+:
/usr/pgsql-9.4/bin/postgresql94-setup initdb
- Modify client authentication configuration by editing /var/lib/pgsql/9.4/data/pg_hba.conf
- # For testing, allow any local user without a password
- # Remote connection for admin and postgres users require a password. Everybody else use LDAP
- # TYPE DATABASE USER CIDR-ADDRESS METHOD
- # "local" is for Unix domain socket connections only
- local all all trust sameuser
- # IPv4 local connections:
- host all all 127.0.0.1/32 trust sameuser
- # IPv6 local connections:
- host all all ::1/128 trust sameuser
- # Remote TCP/IP connection
- host all postgres,admin 172.20.0.0/16 password
- # Note: ldaps scheme is not supported (only ldap://, or ldap:// with ldaptls=1, not ldapS:// - http://www.postgresql.org/docs/9.4/static/auth-methods.html#AUTH-LDAP )
- # Note: yes it is! since PostgreSQL 11, you can use ldaps, see the documentation
- host all all 172.20.0.0/16 ldap "ldap://172.20.13.130/basedn;cn=;,cn=users,dc=concert,dc=music"
- In this configuration, we assume all users are under cn=users,dc=concert,dc=music. Otherwise, user Name defined inside postgresql needs to include ou:
- Jzw,ou=dev
- It is important to use double-quote around ldap url.
- LDAP authentication only verifies user credentials from AD, but the user has to be pre-created inside Postgresql. This can be automated by pg-ldap-sync.
- Update /var/lib/pgsql/data/postgresql.conf
- Listen to connections on all IP addresses
- listen_addresses ='*'
- Enable SSL
- ssl=on
- Listen to connections on all IP addresses
- Generate self-signed key and certificate:
- This is needed for SSL communication between client with DB. Follow the directions on the Postgresql official documentation.
- Create database
- createdb mydb
- Make LDAPs to work with AD
- For the test, you may want to create your self-signed certificate on AD:
- Create Root Certificate Authority
- If you are importing a certificate from Verisign, skip this part and go to part 2 (Import server cert into AD).
- Start the Control Panel Add/Remove Programs applet.
- Click Add/Remove Windows Components to start the Windows Components wizard.
- Click Next when the welcome screen appears.
- When the list of components displays, select the Certificate Services checkbox and click Next.
- Select type Enterprise root CA, and click Next.
- Enter a CA name and other information about the organization, as the screen shows. Click Next.
- Accept the default location for the certificate database (i.e., %systemroot%\System32\CertLog), and click Next.
- If Microsoft IIS is running, the service will stop and a dialog box will display. Click OK.
- A list of files to copy will generate, and the files will install. Service and system configurations will also install. You might need to insert the Windows 2000 Server CD-ROM.
- When the wizard completes, click Finish.
- Import server certificate into Active Directory
- Open Default Group Policy editor. Navigate to Computer configuration->windows settings->security settings->Public key policies->Trusted root certificate authorities.
- Right click on Trusted root certificate authorities and choose import.
- Click on Next and browse to the certificate (.crt file) issued by CA. Click on open.
- Export CA from Active Directory
- Click on Trusted root certificate authorities to open the folder
- Right click on the CA you want to export and choose open
- Click on Details tab and click on copy to file button.
- You will see Certificate export wizard. Click on Next.
- Choose the format to export. For use with OpenSSL (OpenLDAP), choose base-64 encoded X.509 format.
- Copy the file to appropriate location on Redhat 4 server: /etc/openldap/concerto.cer
- Configure LDAPS connection to Active Directory
- Configure ldap.conf and add the following lines:
- TLS_CACERT /etc/openldap/concerto.cer
- TLS_CACERTDIR /etc/openldap/
- # TLS_REQCERT never
- Configure ldap.conf and add the following lines:
- Start DB
- service postgresql start
- Try to connect DB remotely:
- On Windows client, download win32 binary from http://www.postgresql.org. It also includes a .NET library for integrating with .NET client software. Install only psql package.
- Login using password of AD user jzw:
psql -d mydb -h 172.20.x.x. -U jzw -W
- Make sure one see this line to verify that SSL connection is established between client and DB
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256).
- To verify if SSL is working between Postgresql and AD, you can run tcpdump to see if content is encrypted. I found that psql client does not verify the certificate. Not sure if that is a bug or a default settings.