LDAP Authentication against AD

== Howto SSL enable Postgresql LDAP Authentication against Active Directory ==
Category:Administration
The following instruction applies to RPM installation via community repository at . 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
#*:
#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
##*:
##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.