Obviously, for this tutorial we’ll use an environment with PostgreSQL database inside - you can easily create such if you haven’t done this yet.
1. To start with, connect to your database server via Jelastic SSH Gate.
Tip: In case you haven’t performed similar operation before, you need to:
2. Now, in order to make it work with SSL, you need to add the following three files to the /var/lib/pgsql/data server directory:
Within this tutorial, we’ll briefly consider how you can generate them by yourselves.
- we won’t explain commands parameters in details here, but if you’d like to know more, just refer to the Self-Signed Custom SSL page in our documentation or check the official OpenSSL site for the full list of available actions
- you can also use custom SSL certificate similarly to the described below (follow the Generate a Custom SSL Certificate section of the linked guide to get such). In this latter case, you can skip the generation instruction and jump directly to the 6th step of this instruction
So, navigate to the mentioned folder and proceed with steps below.
3. First of all, let’s create the first file - private key:
execute the next commands:
openssl genrsa -des3 -out server.key 1024
During the server.key generation, you’ll be asked for a pass phrase - specify any and confirm it to finish creation.
Now, in order to work with this key further, it’s required remove the pass phrase you’ve added previously. Execute the following command for this:
openssl rsa -in server.key -out server.key
Re-enter pass phrase one more time for confirmation.
Set the appropriate permission and ownership rights for your private key file with the next commands:
chmod 400 server.key
chown postgres.postgres server.key
4. Now, you need to create server certificate based on your server.key file, e.g.:
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=mysite.com/emailAddressemail@example.com'
Note: It’s required to set your personal data for subj parameter if the certificate is intended to be used in production:
|/OU= (optional)||Organizational Unit||IT Department|
You can also just skip the -subj
parameter within the command and pass all these arguments in the interactive mode within the automatically opened inquiry.
5. Since we are going to sign certs by ourselves, the generated server certificate can be used as a trusted root certificate as well, so just make its copy with the appropriate name:
Now, as you have all three certificate files, you can proceed to PostgreSQL database configurations, required for SSL activation and usage.
6. Open the pg_hba.conf file, located in the same folder, for editing with any preferable terminal editor (vim for example) or directly via dashboard.
Replace its default content with the following lines:
# TYPE DATABASE USER CIDR-ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:
host all all 127.0.0.1/32 trust# IPv4 remote connections for authenticated usershostssl all webadmin 0.0.0.0/0 md5 clientcert=1
Tip: In case you are going to work with database not as default webadmin user, change the appropriate value within the last line of the file to the required name. Note that in this case you’ll need to use the same user name for all the further commands (we’ll denote where this is required).
Save the updated file.
7. To finish configurations, you need apply some more changes to the postgresql.conf file.
Navigate to its Security and Authentication section (approximately at the 80th line) and activate SSL usage itself, through uncommenting the same-named setting and changing its value to “on”. Also, add the new ssl_ca_file parameter below:
ssl = on
ssl_ca_file = 'root.crt'
Don’t forget to save these changes.
8. Lastly, restart your PostgreSQL container in order to apply new settings:
sudo service postgresql restart