Tutorial on setting up a CA certificate to enhance PostgreSQL security-database other

After a lot of groping experiments I finally succeeded in achieving the SSL certificate authentication function, so I think this time I want to record these steps for future reference.

For security and convenience reasons, I want to sign a client's certificate on a separate dedicated machine, also known as a Certificate Certification center (CA).

This allows us to authorize new clients without having to log on to the PostgreSQL server before signing the certificate or modifying the pg_hba.conf.

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

In the following example, replace "trustly" with your company name or organization name. All commands are based on the Ubuntu Linux 12.04 LTS.

setting up a CA
The 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 2048
sudo chown root:ssl-cert/etc/ssl/private/ Trustly-ca.key
sudo chmod 640/etc/ssl/private/trustly-ca.key

Build 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.crt
sudo Update-ca-certificates

Configuring PostgreSQL Servers
generate PostgreSQL Server private key

# Remove Default snakeoil certs
sudo rm/var/lib/postgresql/9.1/main/server.key
sudo rm/var/lib/postgresql/ 9.1/MAIN/SERVER.CRT
# Enter a passphrase
sudo-u postgres OpenSSL genrsa-des3-out/var/lib/postgresql/9.1/ Main/server.key 2048
# Remove the passphrase
sudo-u postgres OpenSSL rsa-in/var/lib/postgresql/9.1/main/ Server.key-out/var/lib/postgresql/9.1/main/server.key
sudo-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-sub J '/c=se/st=stockholm/l=stockholm/o=trustly/cn=postgres '

Signing PostgreSQL server certificate requests with the CA private key

sudo openssl req-x509 \
-key/etc/ssl/private/trustly-ca.key \
-IN/TMP/SERVER.CSR \
-out/var/lib/ POSTGRESQL/9.1/MAIN/SERVER.CRT
sudo chown postgres:postgres/var/lib/postgresql/9.1/main/server.crt

Create the root (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.CRT
sudo 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 ~/.postgresql
Cp/etc/ssl/certs/trustly-postgresql.pem ~/.postgresql/root.crt

Generate PostgreSQL Client private key

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 PostgreSQL client certificate signing request and signing

# 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- Cacreateserial
sudo chown joel:joel-r ~/.postgresql
sudo chmod 400-r ~/.postgresql/postgresql.key