How To Install and Secure PostgreSQL on Ubuntu 18.04 DigitalOcean Community
PostgreSQL is a powerful, open source relational database management system (RDBMS) thats available for free on all major operating systems, including Linux. In this tutorial, we will cover how to install and secure PostgreSQL on an Ubuntu 18.04 server from scratch.
Before you follow along with this tutorial, it is recommended that you have a basic understanding of the command line, as well as SSH access to your Ubuntu server. If you are following along in a virtualized environment or on a cloud instance provider like DigitalOcean, be sure to create an appropriate firewall rule for your database (e.g., 5432) so that it is not exposed publicly.
Alright, let’s get our hands dirty and install some PostgreSQL on this Ubuntu Pro installation we got here. But first, let me tell you a little story about my experience with databases. Back in the day when I was just starting out as a Linux sysadmin, I had to deal with MySQL for the very first time. And boy oh boy, did that suck. The configuration files were all over the place, and every time I wanted to make a change, I felt like I needed a PhD in database management just to figure it out. But then one day, a friend of mine introduced me to PostgreSQL. It was love at first sight. The syntax for creating tables and indexes was so much cleaner than MySQL’s, and the documentation was actually helpful! And best of all, I could finally sleep soundly knowing that my data was secure with its robust encryption features. So without further ado, let’s get started on installing PostgreSQL on our Ubuntu Pro installation. To begin with, we need to update the package list and upgrade any existing packages:
Alright, Let’s jump right into this tutorial and learn how to configure SSH for Ubuntu Pro Installation with Ansible! But before that, let me tell you a little story about my experience with SSH. Back in the day when I was just starting out as a Linux sysadmin, configuring SSH used to be a nightmare. The syntax for creating configuration files and managing keys felt like rocket science. But then one day, Ansible came along and changed everything! With its simple YAML-based language and powerful modules, it made automating tasks on remote servers a breeze. So without further ado, let’s get started on configuring SSH for Ubuntu Pro Installation with Ansible!
To begin with, we need to update the package list and upgrade any existing packages:
# Update the package list using apt-get update command
# The && operator allows for multiple commands to be executed in one line
# The -y flag automatically answers yes to any prompts during the upgrade process
$ sudo apt-get update && sudo apt-get upgrade -y
Once that’s done, let’s install Ansible using the following command:
# Install Ansible using the following command:
# -y flag automatically confirms any prompts during installation
# sudo allows for administrative privileges
# apt-get is a package manager for Debian-based systems
# install is the command to install a package
# ansible is the name of the package being installed
$ sudo apt-get install ansible -y
After installation is complete, we need to create a new directory for our playbook and copy it over to our Ubuntu Pro server. Let’s call this directory `ansible_playbooks`. Inside that directory, let’s create a file called `configure-ssh.yml`:
# Create a new directory called "ansible_playbooks" and change into it
$ mkdir ansible_playbooks && cd ansible_playbooks/
# Create a new file called "configure-ssh.yml"
$ touch configure-ssh.yml
Now, open the `configure-ssh.yml` file in your favorite text editor and add the following content:
---
# This script configures SSH on an Ubuntu Pro installation
# and adds annotations to explain the functionality and purpose of each code segment.
# Define the name of the playbook and the target hosts
- name: Configure SSH on Ubuntu Pro Installation
hosts: ubuntuproserver
# Define variables for the SSH port and key
vars:
ssh_port: "2022" # Change this to a different port if desired
ssh_key: "/path/to/your/ssh/public/key.pub" # Replace with your own SSH public key
# Define tasks to be executed
tasks:
# Disable root login via SSH
- name: Disable root login via SSH
file:
path: /etc/ssh/sshd_config
line: "PermitRootLogin no"
state: present
insertafter: '#PermitRootLogin'
notify: "Restart SSH service" # Notify the next task to restart the SSH service
# Change default SSH port to the defined variable
- name: Change default SSH port to {{ ssh_port }}
file:
path: /etc/ssh/sshd_config
line: "Port {{ ssh_port }}"
state: present
insertafter: '#Port 22'
# Add SSH public key to authorized keys
- name: Add SSH public key to authorized keys
template:
src: templates/authorized_keys.j2
dest: "/home/ubuntu/.ssh/authorized_keys"
mode: "0644"
notify: "Restart SSH service" # Notify the next task to restart the SSH service
vars:
ssh_key: "{{ lookup('file', item) }}" # Use the lookup plugin to retrieve the SSH key from the defined variable
with_items:
- "{{ ssh_key }}" # Loop through the SSH key variable
# Restart the SSH service
- name: Restart SSH service
shell: sudo systemctl restart ssh
In this playbook, we’re disabling root login via SSH, changing the default SSH port to `2022`, adding our own SSH public key to authorized keys, and finally restarting the SSH service. Note that you should replace `/path/to/your/ssh/public/key.pub` with your actual SSH public key path.
Now let’s create a template for the authorized_keys file:
# Create a directory called "templates" and navigate into it
$ mkdir templates && cd templates/
# Create a file called "authorized_keys.j2"
$ touch authorized_keys.j2
# The following commands will be used to configure SSH settings
# Change the default SSH port to 2022
# Add our own SSH public key to authorized keys
# Restart the SSH service
# Note: Replace "/path/to/your/ssh/public/key.pub" with your actual SSH public key path
# Create a directory called "templates" and navigate into it
$ mkdir templates && cd templates/
# Create a file called "authorized_keys.j2"
$ touch authorized_keys.j2
Open `authorized_keys.j2` in your favorite text editor and add the following content:
Here is the corrected script with annotations:
# This script is used to add an SSH key to the authorized_keys.j2 file
# in order to allow access to a remote server.
# The following line uses the ssh_key variable to add the SSH key to the file.
# This variable should be defined in the playbook or inventory file.
{{ ssh_key }}
Finally, let’s run our playbook using Ansible:
# This script runs an Ansible playbook to configure SSH on a Ubuntu Pro server
# The playbook is specified as "configure-ssh.yml" and the inventory is set to "ubuntuproserver"
# Run the playbook using the "ansible-playbook" command
ansible-playbook configure-ssh.yml
# Specify the inventory using the "-i" flag and set it to "ubuntuproserver"
-i "ubuntuproserver"
Replace `ubuntuproserver` with the IP address or hostname of your Ubuntu Pro server. This will run our playbook on that server and apply the changes we specified in it.
Your SSH configuration is now securely configured using Ansible.