Set CA certificate to enhance PostgreSQL Security tutorial, capostgresql

Set CA certificate to enhance PostgreSQL Security tutorial, capostgresql

After many experiments, I finally successfully implemented the SSL certificate authentication function. Therefore, I want to record these steps for future reference.

For security and convenience, I want to sign the customer's certificate on a separate dedicated machine, also known as the Certificate Authority (CA ).

Therefore, when authorizing a new client, you do not have to log on to the PostgreSQL server before signing the certificate or modifying pg_mirror.conf.

We want to create a special database group called sslcertusers. All users in this group can connect through the certificate signed by the CA.

In the following example, replace "trustly" with your company name or organization name. All commands are based on Ubuntu Linux 12.04 LTS.
 
Set CA
CA should be an offline computer in a highly secure environment.

Generate CA private key
 

sudo openssl genrsa -des3 -out /etc/ssl/private/trustly-ca.key 2048sudo chown root:ssl-cert /etc/ssl/private/trustly-ca.keysudo chmod 640 /etc/ssl/private/trustly-ca.key

Generate CA certificate
 

sudo openssl req -new -x509 -days 3650 \-subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=trustly' \-key /etc/ssl/private/trustly-ca.key \-out /usr/local/share/ca-certificates/trustly-ca.crtsudo update-ca-certificates

Configure the PostgreSQL Server
Generate the private key of the PostgreSQL Server
 

# Remove default snakeoil certssudo rm /var/lib/postgresql/9.1/main/server.keysudo rm /var/lib/postgresql/9.1/main/server.crt# Enter a passphrasesudo -u postgres openssl genrsa -des3 -out /var/lib/postgresql/9.1/main/server.key 2048# Remove the passphrasesudo -u postgres openssl rsa -in /var/lib/postgresql/9.1/main/server.key -out /var/lib/postgresql/9.1/main/server.keysudo -u postgres chmod 400 /var/lib/postgresql/9.1/main/server.key

Generate PostgreSQL Server Certificate Signing Request (CSR)

 

sudo -u postgres openssl req -new -nodes -key /var/lib/postgresql/9.1/main/server.key -days 3650 -out /tmp/server.csr -subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=postgres'

Use the CA private key to sign the PostgreSQL server certificate request
 

sudo openssl req -x509 \-key /etc/ssl/private/trustly-ca.key \-in /tmp/server.csr \-out /var/lib/postgresql/9.1/main/server.crtsudo chown postgres:postgres /var/lib/postgresql/9.1/main/server.crt

Create root certificate = PostgreSQL server certificate + CA certificate
 

sudo -u postgres sh -c 'cat /var/lib/postgresql/9.1/main/server.crt /etc/ssl/certs/trustly-ca.pem > /var/lib/postgresql/9.1/main/root.crt'sudo cp /var/lib/postgresql/9.1/main/root.crt /usr/local/share/ca-certificates/trustly-postgresql.crtsudo update-ca-certificates

Authorized access
 

CREATE GROUP sslcertusers;ALTER GROUP sslcertusers ADD USER joel; # /etc/postgresql/9.1/main/pg_hba.conf:hostssl nameofdatabase +sslcertusers 192.168.1.0/24 cert clientcert=1

Restart PostgreSQL
 

sudo service postgresql restart

PostgreSQL client settings
Copy the root certificate from the PostgreSQL Server
 

mkdir ~/.postgresqlcp /etc/ssl/certs/trustly-postgresql.pem ~/.postgresql/root.crt

Generate the private key of the PostgreSQL Client
 

openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024 # If this is a server, remove the passphrase:openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.key

Generate and sign the PostgreSQL client Certificate Signing Request
 

# Replace "joel" with username:openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr -subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=joel'sudo openssl x509 -req -in ~/.postgresql/postgresql.csr -CA /etc/ssl/certs/trustly-ca.pem -CAkey /etc/ssl/private/trustly-ca.key -out ~/.postgresql/postgresql.crt -CAcreateserialsudo chown joel:joel -R ~/.postgresqlsudo chmod 400 -R ~/.postgresql/postgresql.key