Generate SSL Certificate
- The first step is to generate an SSL certificate that will be used to secure the communication between the client and the server.
- This can be done using a tool such as OpenSSL.
- For example, to generate a self-signed certificate, you can use the following command:
openssl req -new -x509 -nodes -out server.crt -keyout server.key
This will generate a certificate and a private key file in the current directory.
-
Configure PostgreSQL Server
- The next step is to configure the PostgreSQL server to use SSL.
- Open the
postgresql.conffile and uncomment thesslline and set its value toon. - Also, uncomment the
ssl_cert_fileandssl_key_filelines and set their values to the paths of the certificate and private key files generated in step 1.
-
Configure PostgreSQL Client
- The PostgreSQL client must also be configured to use SSL to communicate with the server.
- Open the
pg_hba.conffile and add an entry for SSL connections. - For example:
hostssl all all 0.0.0.0/0 md5 clientcert=1
This will allow SSL connections from any IP address using the md5 authentication method and require the client to provide a valid SSL certificate.
-
Restart PostgreSQL Server
- After making the above changes, you need to restart the PostgreSQL server for the changes to take effect.
-
Test SSL Connection
- To test the SSL connection, you can use the
psqlcommand-line tool with the-sslmodeoption set torequire. - For example:
- To test the SSL connection, you can use the
psql -h <hostname> -U <username> -d <database> -sslmode=require
This will establish an SSL-encrypted connection to the PostgreSQL server.
Note: It’s important to keep the SSL certificate and private key files secure and to configure the server and client to use strong encryption protocols and ciphers.
Enable SSL in PostgreSQL client
Configure following three files on ~/.postgresql/ directory
root.crt (trusted root certificate)
postgresql.crt (client certificate)
postgresql.key (private key)
genarte postgresql.key on client side machine and purge the passphrase.
$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
$ openssl rsa -in /crt/postgresql.key -out /crt/postgresql.key
genrate postgresql.crt, and sign it with using the trusted root (private key file from the database server). Please note, when you are prompted for certificate common name (CN), set it to database name.
$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
$ openssl x509 -req -in server.req -out /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
Copy the three files created in server’s /tmp directory to client machine.
Copy root.crt from server /tmp directory to client’s ~/.postgresql/ directory.
Once all is done connect to database from client
and on database check if you are able to see the connection with ssl = True (T) using floowing quesry
SELECT datname,usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid;
