$ psql -d 'dbname=postgres sslmode=require' -h -U postgres Replace with your server's fully qualified domain name. Restart PostgreSQL $ service postgresql restartĬonnect to the database from another machine with the PostgreSQL client installed. For example: $ sudo ls /var/lib/postgresql/12/main/server.* Verify that Certbot copied the certs to the PostgreSQL data directory. $ sudo -u postgres psql -U postgres -c 'SHOW data_directory' Perform a forced renewal, which triggers the Certbot renewal hook to copy the certificates to the correct location for PostgreSQL. Optionally, to also allow insecure connections, add the following line: host all all 0.0.0.0/0 md5 For example: $ sudo nano /etc/postgresql/12/main/pg_hba.confĪdd the following line to enable secure SSL traffic from the internet. For example: listen_address = '*'Įdit the pg_hba.conf file, which is in the same directory as the configuration file. Locate the Connection Settings section and verify the listen_address is to * for all addresses. Locate the SSL section and edit your file to match these SSL settings: ssl = on For example: $ sudo nano /etc/postgresql/12/main/nf Get the path of the PostgreSQL configuration file: $ sudo -u postgres psql -U postgres -c 'SHOW config_file'Įdit the file shown by the previous command. $ sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/ploy #!/bin/bashĬp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crtĬp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.keyĬhown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key Replace the value for DATA_DIR with your PostgreSQL data directory. Replace with your server's fully-qualified domain name. $ sudo nano /etc/letsencrypt/renewal-hooks/deploy/ploy

$ sudo -u postgres psql -U postgres -c 'SHOW data_directory'Ĭreate the renewal hook file. You'll use this value in the renewal hook file. To allow PostgreSQL to use the certificate, it must create a copy with a Certbot renewal hook. Create a Certbot Renewal Hook for PostgreSQLĬertbot's certificates are only accessible by root. $ sudo certbot certonly -standalone -d ģ. Replace with the fully-qualified domain name of your server. $ sudo -u postgres psql -c 'ALTER USER postgres PASSWORD '' 'Ĭertbot is the free tool to automatically request Let's Encrypt certificates.įollow our guide to install Certbot with Snap. Set the password for the postgres account. $ sudo apt install postgresql postgresql-contrib Prerequisitesĭeploy an Ubuntu 20.04 LTS cloud server at Vultr.Ĭreate a fully-qualified domain name (DNS 'A' record) that points to your server's IP address.

This guide explains how to use a free Let's Encrypt certificate to secure connections to your PostgreSQL server. By default, most installations of PostgreSQL use insecure connections instead of encrypted connections.

Postgresql ssl client certificate