Use SSL Encryption with PostgreSQL on Ubuntu 20.04
source link: https://www.vultr.com/docs/use-ssl-encryption-with-postgresql-on-ubuntu-20-04
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Introduction
By default, most installations of PostgreSQL use insecure connections instead of encrypted connections. This guide explains how to use a free Let's Encrypt certificate to secure connections to your PostgreSQL server.
Prerequisites
Before beginning this guide:
- Deploy an Ubuntu 20.04 LTS cloud server at Vultr.
- Create a fully-qualified domain name (DNS "A" record) that points to your server's IP address.
1. Install PostgreSQL
Install the main PostgreSQL packages.
$ sudo apt install postgresql postgresql-contrib
Set the password for the postgres
account.
$ sudo -u postgres psql -c "ALTER USER postgres PASSWORD '<new_password>';"
2. Install Certbot and Certificate
Certbot is the free tool to automatically request Let's Encrypt certificates.
Follow our guide to install Certbot with Snap.
Request a certificate for your server. Replace psql.example.com
with the fully-qualified domain name of your server.
$ sudo certbot certonly --standalone -d psql.example.com
3. Create a Certbot Renewal Hook for PostgreSQL
Certbot's certificates are only accessible by root. To allow PostgreSQL to use the certificate, it must create a copy with a Certbot renewal hook.
Look up the PostgreSQL data directory. You'll use this value in the renewal hook file.
$ sudo -u postgres psql -U postgres -c 'SHOW data_directory'
Create the renewal hook file.
$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
Paste the following. Replace
psql.example.com
with your server's fully-qualified domain name. Replace the value forDATA_DIR
with your PostgreSQL data directory.#!/bin/bash umask 0177 DOMAIN=psql.example.com DATA_DIR=/var/lib/postgresql/12/main cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.key chown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key
Save and exit the file.
Give the file executable permissions.
$ sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
4. Configure PostgreSQL for SSL
Get the path of the PostgreSQL configuration file:
$ sudo -u postgres psql -U postgres -c 'SHOW config_file'
Edit the file shown by the previous command. For example:
$ sudo nano /etc/postgresql/12/main/postgresql.conf
Locate the SSL section and edit your file to match these SSL settings:
ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_prefer_server_ciphers = on
Locate the Connection Settings section and verify the
listen_address
is to*
for all addresses. Make sure the line is not commented out. For example:listen_address = '*'
Save and exit the file.
5. PostgreSQL Connection Configuration
Get the path of the PostgreSQL configuration file:
$ sudo -u postgres psql -U postgres -c 'SHOW config_file'
Edit the
pg_hba.conf
file, which is in the same directory as the configuration file. For example:$ sudo nano /etc/postgresql/12/main/pg_hba.conf
Add the following line to enable secure SSL traffic from the internet.
hostssl all all 0.0.0.0/0 md5
Optionally, to also allow insecure connections, add the following line:
host all all 0.0.0.0/0 md5
Save and exit the file.
6. Renew the Certificate
Perform a forced renewal, which triggers the Certbot renewal hook to copy the certificates to the correct location for PostgreSQL.
$ sudo certbot renew --force-renewal
Look up the PostgreSQL data directory.
$ sudo -u postgres psql -U postgres -c 'SHOW data_directory'
Verify that Certbot copied the certs to the PostgreSQL data directory. For example:
$ sudo ls /var/lib/postgresql/12/main/server.*
Restart PostgreSQL
$ service postgresql restart
7. Test the Connection
Connect to the database from another machine with the PostgreSQL client installed. Replace psql.example.com
with your server's fully qualified domain name.
$ psql -d "dbname=postgres sslmode=require" -h psql.example.com -U postgres
You should see the PostgreSQL prompt.
Password for user postgres:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Type \Q to exit the PostgreSQL client.
postgres=# \q
More Information
See the official documentation for more information.
Want to contribute?
You could earn up to $600 by adding new articles
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK