LDAP Authentication against AD

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.

1. Install all the needed rpms as root. This package will pull the other dependencies:

    yum -y install postgresql94-server

2. 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

3. 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.
4. Update /var/lib/pgsql/data/postgresql.conf
   • Listen to connections on all IP addresses

     listen_addresses ='*'

   • Enable SSL

     ssl=on

5. Generate self-signed key and certificate:
   • This is needed for SSL communication between client with DB. Follow the directions on the Postgresql official documentation.
6. Create database

   createdb mydb

7. Make LDAPs to work with AD
   • For the test, you may want to create your self-signed certificate on AD:
   1. 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).
      1. Start the Control Panel Add/Remove Programs applet.
      2. Click Add/Remove Windows Components to start the Windows Components wizard.
      3. Click Next when the welcome screen appears.
      4. When the list of components displays, select the Certificate Services checkbox and click Next.
      5. Select type Enterprise root CA, and click Next.
      6. Enter a CA name and other information about the organization, as the screen shows. Click Next.
      7. Accept the default location for the certificate database (i.e., %systemroot%\System32\CertLog), and click Next.
      8. If Microsoft IIS is running, the service will stop and a dialog box will display. Click OK.
      9. 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.
      10. When the wizard completes, click Finish.
   2. Import server certificate into Active Directory
      1. Open Default Group Policy editor. Navigate to Computer configuration->windows settings->security settings->Public key policies->Trusted root certificate authorities.
      2. Right click on Trusted root certificate authorities and choose import.
      3. Click on Next and browse to the certificate (.crt file) issued by CA. Click on open.
   3. Export CA from Active Directory
      1. Click on Trusted root certificate authorities to open the folder
      2. Right click on the CA you want to export and choose open
      3. Click on Details tab and click on copy to file button.
      4. You will see Certificate export wizard. Click on Next.
      5. Choose the format to export. For use with OpenSSL (OpenLDAP), choose base-64 encoded X.509 format.
      6. Copy the file to appropriate location on Redhat 4 server: /etc/openldap/concerto.cer
   4. 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

   5. Start DB

      service postgresql start

8. 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.