Saturday, November 20, 2004

Generating certificates with OpenSSL (Jun 2003)

I created this page as my notes on how to generate SSL certificates for my home web server. This document does not meant to be a comprehensive guide on SSL certificate. Read this on your own risk...

What is OpenSSL?

"The OpenSSL Project is a collaborative effort to develop a robust, commercial-grade, full-featured, and Open Source toolkit implementing the Secure Sockets Layer (SSL v2/v3) and Transport Layer Security (TLS v1) protocols as well as a full-strength general purpose cryptography library."

Random numbers

Before we start generating certificates, we need some random numbers for key generation. This step is optional, but it increases the security of the generated keys. Also you can use any random number generation menthod you like.

dd if=/dev/urandom of=rand.txt bs=8192 count=1

The above command copies 8192 pseudo-random bytes from /dev/urandom. I did this on my FreeBSD machine. Note that you can/should generate different random numbers for each key generation presented below.

Generating a Certificate Authority (CA)

First we generate the CA private key. Then based on the key, generate a certificate request. Finally we will sign the request with our own CA key.

openssl genrsa -rand rand.txt -out ca.key 1024
openssl req -new -key ca.key -out ca.csr
openssl x509 -req -days 365 -in ca.csr 
-signkey ca.key -out ca.crt


Generating a Web Server certificate

Here is how we generate a self-signed certificate for use with web servers. The procedures are similar to generating the CA certificate.

openssl genrsa -rand rand.txt -out server.key 1024
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 365 -in server.csr 
-signkey server.key -out server.crt

You can choose to sign the server certificate with the CA key (ca.key) generated in previous step.

(BTW, details on how to install the certificate on an Apache server are not presented here... maybe later when I have more time... :)

Generating a client certificate

The generation of client certificate is similar to others

openssl genrsa -rand rand.txt -out client.key 1024
openssl req -new -key client.key -out client.csr


Then use the CA key to sign the certificate request

openssl x509 -req -days 365 -CA ca.crt -CAkey ca.key 
-CAcreateserial -in client.csr -out client.crt


Finally, package the certificate and key in PKCS12 format so we can import it into browsers

openssl pkcs12 -export -clcerts -in client.crt 
-inkey client.key -out client.p12


By default, OpenSSL uses RC2 and 3DES for encrypting the certificate and private key. Note that some binary distributions of OpenSSL do not have RC2 compiled in. And some browsers don't support certificate encrypted with RC2. If so, try to use RC4 or DES etc other encryptions for the certificate. e.g.

openssl pkcs12 -export -clcerts -in client.crt 
-inkey client.key -out client.p12 -certpbe des-ecb -descert


or

openssl pkcs12 -export -clcerts -in client.crt 
-inkey client.key -out client.p12 -certpbe rc4-40 -descert


You can then copy the .p12 file to the client computer for installation. For example, on Windows 2000, just double click the file and follow the instruction to install the certificate.

PostgreSQL on Windows (April 2004)

(Note that I wrote this article a long time ago. Now Postgresql has its own native Windows distribution.)

This is a quick installation guide of PostgreSQL on Windows. PostgreSQL and MySQL are two of the database systems that I used for program development at home. I usually run the database servers on either FreeBSD or Linux. Recently, a project idea came up and I decided to try PostgreSQL on Windows.

This guide only concentrates on the installation process. This is not a tutorial on PostgreSQL/JDBC/Cygwin......

What is PostgreSQL?

"PostgreSQL is the most advanced open source database server." -- PostgreSQL: Introduction and Concepts by Bruce Momjian.

What is Cygwin?

Cygwin is a Linux-like environment for Windows.

Overview

  • Install Cygwin (together with PostgreSQL)

  • Install cygipc

  • Init and start the PostgreSQL server

  • Create users and databases

  • Using PostgreSQL with JDBC

Install Cygwin and PostgreSQL

We can choose to install the PostgreSQL together with Cygwin.

  1. Goto the Cygwin homepage

  2. Click on the Install or update now! link and download the setup.exe file

  3. Execute the executable. Select the "Install from Internet" option



  4. Select the location to install


  5. The setup program needs to download packages before installing. Specify a directory to store these packages.



  6. Select the connection method. Then pick a mirror site to download the packages.


  7. Select the packages that you wants. Remember to select the PostgreSQL package under the Database category.


  8. Proceed with the download and install process.



If the setup program doesn't install the packages after download, you can execute the setup.exe again. This time, select "Install from local directory". Then select the director where the packages are downloaded. Proceed to install the packages.


Install cygipc

PostgreSQL depends on an utility package called cygipc. It provides IPC (shared memory, semaphores etc) functions.

Note that newer versions of Cygwin already include the cygipc package. Also the command is changed to ipc-daemon2


  1. Download the package here.

  2. Start a cygwin shell window

  3. Extract files from the package.

    $ cd /
    $ bunzip2 -c path_of_package/cygipc-1.14-1.tar.bz2 | tar xvf -

    Check the availability of the ipc daemon installed. Then execute the daemon in background.
    $ which ipc-daemon
    /usr/local/bin/ipc-daemon
    
    $ /usr/local/bin/ipc-daemon&


Init and start the PostgreSQL server

Before we can start the PostgreSQL server, we need to initialize a database storage area on disk.


  1. If the ipc daemon is not running, start it now

    $ which ipc-daemon
    /usr/local/bin/ipc-daemon
    
    $ /usr/local/bin/ipc-daemon&

  2. Create the directory /usr/local/pgsql/data for storing PostgreSQL data.

  3. Initialize the storage area

    $ /usr/bin/initdb -D /usr/local/pgsql/data

  4. Now we can start the server. Note that we need to use the -i option so that the server will listen to TCP connections. Otherwise, it just listens on Unix sockets.

    $ /usr/bin/postmaster -i -D /usr/local/pgsql/data


    (Note: You can also choose to run it as a background job. But leaving it on foreground can let us view the log)


Create users and databases

We first create a new user clarence and grant it the right to create databases. Then using this user account, we create a database with Unicode encoding.

Here are the explanations of the commands


  • psql -l: List existing databases

  • psql template1: Login to the template1 database (as Administrator)

  • CREATE USER clarence CREATEDB;: Create a new user clarence with permission to create databases

  • q: Quit psql command line

  • psql template1 clarence: Login to the template1 database (as clarence)

  • CREATE DATABASE test WITH ENCODING='UNICODE';: Create a new database test with Unicode encoding.



Using PostgreSQL with JDBC

There is no special trick for using PostgreSQL with JDBC.


  • Download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/

  • Include the jar in the classpath

  • The JDBC URL for PostgreSQL can be


    • jdbc:postgresql:database

    • jdbc:postgresql://host/database

    • jdbc:postgresql://host:port/database


  • Access the database as usual
    ......
    import java.sql.*;
    import org.postgresql.*;
    
    ......
    Class.forName(
    "org.postgresql.Driver");
    
    ......
    con = DriverManager.getConnection(
    "jdbc:postgresql://127.0.0.1/test",
    "clarence", "");
    
    PreparedStatement ps =
    con.prepareStatement("select * from version()");
    ResultSet rs = ps.executeQuery();
    ......
Misc.


  • Control client authentication by editing the file /usr/local/pgsql/data/pg_hba.conf

  • To install PL/pgSQL into a database, execute the command "createlang plpgsql dbname"

  • To install PostgreSQL as a service, use the cygrunsrv command. First install the IPC daemon as service:
    ipc-daemon --install-as-service

    Then use cygrunsrv to add PostgreSQL as a service:
    cygrunsrv --install PostgreSQL --path /usr/bin/postmaster 
    --args "-i -D /usr/local/pgsql/data" --dep ipc-daemon 
    --user username --password password --stdout stdoutlog 
    --stderr stderrlog --termsig INT --shutdown

  • To enable the statistics collection, edit the postgresql.conf file and set STATS_START_COLLECTOR, STATS_COMMAND_STRING, STATS_BLOCK_LEVEL, and STATS_ROW_LEVEL accordingly. View the stats by selecting pg_stat_all_tables, pg_stat_user_tables, pg_statio_all_tables, pg_statio_user_tables,...

  • PostgreSQL: http://www.postgresql.org/

  • Cygwin: http://cygwin.com/

  • GUI admin tool: http://pgadmin.postgresql.org/

  • On-line book PostgreSQL: Introduction and Concepts: http://www.postgresql.org/docs/awbook.html