Howto SSL enable Postgresql LDAP Authentication against Active Directory

Howto SSL enable Postgresql LDAP Authentication against Active Directory



Wednesday, March 23, 2007

jwang@sentillion.com



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



The following instruction applies to Redhat RPM installation.



(2) install all the needed rpms as root. Only



postgresql-8.2.3-1PGDG.i386.rpm

postgresql-libs-8.2.3-1PGDG.i386.rpm

postgresql-server-8.2.3-1PGDG.i386.rpm



are needed to get it going.   



(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



Change client authentication method by editing /var/lib/pgsql/data/pg_hba.conf. Instead of

Using Ident, I found it is easier for testing to use trust for all local access.

That is, local access does not require password. There is only one built-in superuser as postgres.

I also created an admin user as super user.



All TCP/IP remote access use LDAP authentication against AD:



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



(7) Modify /var/lib/pgsql/data/postgresql.conf file



(a) change the server to listen to TCP/IP connection from all IPs:



Edit /var/lib/pgsql/data/postgresql.conf



Add the line



listen_addresses ='*'

 

(b) Enable SSL



Add line:

Ssl=on



(7) Generate self-signed key and certificate: this is needed for SSL communication between client with DB.



Follow the document on Postgresql website.



(8) Create database



Create a new DB:



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.



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.



Then, you need to select the type. Types include the following:



Enterprise root CA



Enterprise subordinate CA Standalone root CA Standalone subordinate CA Select Enterprise root CA, as the Screen shows, 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). 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.



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





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



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





4. start DB: Service postgresql start





(10) Try to connect DB remotely:



Download win32 binary from http://www.postgresql.org. It also includes a .NET library for integrating with .NET

client software.



Install only psql package.



Now one can login using password of AD user jzw:



Psql -d mydb -h 172.20.x.x. -U jzw -W



Make sure one see the line



SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256). This means that SSL connection is

Established between Postgresql and client. 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.