LDAP Authentication against AD

From PostgreSQL wiki

Revision as of 07:46, 24 July 2013 by Larskanis (Talk | contribs)

Jump to: navigation, search

Howto SSL enable Postgresql LDAP Authentication against Active Directory

The following instruction applies to Redhat RPM installation.

  1. Download SRPMs from website and rebuild with --with-ldap flag.
    The binary release from postgresql website does not include LDAP authentication support. If you want LDAP support, you will need build it from the source with a flag: --with-ldap. If you are deploying it to Redhat 4, you can download the source RPM from srpm folder of the release. You can simply add --with-ldap flag into the RPM specification file.
  2. Install all the needed rpms as root.
    postgresql-8.2.3-1PGDG.i386.rpm
    postgresql-libs-8.2.3-1PGDG.i386.rpm
    postgresql-server-8.2.3-1PGDG.i386.rpm
  3. Fix a permission on directory:
    chmod 755 /usr/share/pgsql/timezonesets/
  4. Initialize the database
    /sbin/service postgresql initdb
  5. Modify client authentication configuration by editing /var/lib/pgsql/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
    host all all 172.20.0.0/16 ldap "ldaps://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
    • Note that basedn is ignored in the Postgresql 8.2.3 build, but you will have to put it there in order for it to work. See bug report reference: 3095.
    • 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.
  6. Update /var/lib/pgsql/data/postgresql.conf
    • Listen to connections on all IP addresses
      listen_addresses ='*'
    • Enable SSL
      Ssl=on
  7. Generate self-signed key and certificate:
  8. Create database
    createdb mydb
  9. 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
  10. 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.
Personal tools