In this section, we will demonstrate how the principles and high-level processes we have looked at so far for MariaDB on CentOS can be applied to another platform. Taking a high-level view, these processes can be applied to almost any database and Linux platform, with the proper attention to detail. Here, we will install PostgreSQL Server onto Ubuntu Server, and then secure it by setting the root password—essentially, analogous to the process we have performed in the preceding section.

Let us get started by creating a role called installpostgres. In this role we will again define a template for the package downloads from the official PostgreSQL sources, this time—of course—tailoring it to the fact that we're using Ubuntu Server, and not CentOS. The following code shows the template file—note that this is specific for Ubuntu Server 18.04 LTS—codename bionic:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

As before, once our package sources are defined, we can proceed to create the tasks that will install the database. In the case of Ubuntu, we must add the package-signing key manually to the apt keyring, in addition to copying the preceding template into place. Thus, our tasks within the role begin, as follows:

---
- name: Populate PostgreSQL apt template on target host
  template:
    src: templates/pgdg.list.j2
    dest: /etc/apt/sources.list.d/pgdg.list
    owner: root
    group: root
    mode: '0644'

We could also use apt_repository here, but, for consistency with the previous MariaDB example, we are using template. Both will achieve the same end result.

When the template package is in place, we must then add the package-signing key to apt's keyring, as follows:

- name: Add key for PostgreSQL packages
  apt_key:
    url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
    state: present

The postgresql-11 and other supporting packages are then installed (as per the documentation at https://www.postgresql.org/download/linux/ubuntu/), as follows:

- name: Install PostgreSQL 11 packages
  apt:
    name:
      - postgresql-11
      - postgresql-client-11
    state: latest
    update_cache: yes

As our default Ubuntu Server install is not running a firewall, the final task in this playbook is to start the service, and ensure it starts at boot time, as follows:

- name: Ensure PostgreSQL service is installed and started at boot time
  service:
    name: postgresql
    state: started
    enabled: yes

Running this should yield output similar to the following:

By default, out-of-the-box installation of PostgreSQL is much more secure than MariaDB. Without additional configuration, no remote logins are allowed at all, and although no password is set for the superuser account, it can only be accessed on the local machine from the postgres user account. Similarly, there is no test database to drop.

Thus, although the high-level process is the same, you must be aware of the nuances of both the database server you are using and the underlying operating system.

By way of example and to complete this section, let's create a database called production, and an associated user called produser who will be given access to it. Although technically, this overlaps with the next section on loading initial data, it is provided here to be analogous to the preceding section on MariaDB, and to demonstrate how to use the native Ansible modules for PostgreSQL.

1. Let's create a role called setuppostgres, and start by defining a task to install the Ubuntu package necessary to support the Ansible PostgreSQL modules, as follows:

---
- name: Install PostgreSQL Ansible support packages
  apt:
    name: python-psycopg2
    state: latest

2. After this, we add a task to create the database (this is a very simple example—you will want to tailor it to your exact requirements), as follows:

- name: Create production database
  postgresql_db:
    name: production
    state: present
  become_user: postgres

3. Notice how we leverage the local postgres account on the target machine for database superuser access with the become_user statement. Next, we'll add the user, and give them privileges on this database, as follows:

- name: Add produser account to database
  postgresql_user:
    db: production
    name: produser
    password: securepw
    priv: ALL
    state: present
  become_user: postgres

As usual, you would not just specify the password in plaintext like this—this has been done here for simplicity. As usual, substitute appropriate data for variables, and if those variables are sensitive, either encrypt them at rest using Ansible Vault or prompt the user for them when the playbook is run.

4. Now, to get PostgreSQL to listen for remote connections for this user, we need to perform two more actions. We need to add a line to pg_hba.conf, to tell PostgreSQL to allow the user we just created to access this database from the appropriate network—the following example is shown, but be sure to tailor it to your network and requirements:

- name: Grant produser access to the production database over the local network
  postgresql_pg_hba:
    dest: /etc/postgresql/11/main/pg_hba.conf
    contype: host
    users: produser
    source: 192.168.81.0/24
    databases: production
    method: md5

5. We must also change the listen_addresses parameter in the postgresql.conf file, which defaults to local connections only. The exact location of this file will vary depending on your operating system and version of PostgreSQL—the following example shown is suitable for our install of PostgreSQL 11 on Ubuntu Server 18.04:

- name: Ensure PostgreSQL is listening for remote connections
  lineinfile:
    dest: /etc/postgresql/11/main/postgresql.conf
    regexp: '^listen_addresses ='
    line: listen_addresses = '*'
  notify: Restart PostgreSQL

6. Observant readers will have noticed the use of handlers here too—the postgresql service must be restarted to pick up any changes to this file. However, this should only be performed when the file is changed, and hence we make use of handlers. Our handlers/main.yml file will look like this:

---
- name: Restart PostgreSQL
  service:
    name: postgresql
    state: restarted

7. With our playbook assembled, we can now run it, and the output should look something like the following screenshot:

Although this example is not strictly the same as the replication of the mysql_secure_installation tool in the previous section, it does show how to use native Ansible modules to configure and secure a PostgreSQL database and shows how Ansible can powerfully assist you in setting up and securing new database servers. These principles can be applied to just about any database server that is compatible with Linux, though the modules available for each database will vary. A full list of modules can be found here: https://docs.ansible.com/ansible/latest/modules/list_of_database_modules.html

Now that we have looked at the process of installing a database server, in the next section, we will build on our installation work, to load initial data and schemas.