PostgreSQL Configuration is part 3 of the multi-part series guide to configuring a full-featured mailserver using only open-source software. If you haven’t seen the previous parts, you can find them by clicking Here for Part 1 and Here for Part 2.

PostgreSQL

To review, we will be using PostgreSQL because it is comparable to MySQL in performance, but excels when it comes to security. In addition we are going for an open-source mailserver, PostgreSQL is true open-source while MySQL is mostly open-source. If you want even more reasons, SmartBear has some for you. Moving on, lets start installing and configuring PostgreSQL.

Installation

PostgreSQL should already be provided by your repositories included with CentOS 7 so go ahead and install it using yum:

yum install -y postgresql postgresql-server

Make it run at startup:

systemctl enable postgresql

Create the database cluster, this generates the files we need to edit for security:

postgresql-setup initdb

Security

There are a few security measures we need to take before setting up databases.

pg_hba.conf

The security settings for PostgreSQL are configured in a settings file at: “/var/lib/pgsql/data/pg_hba.conf”. Open that file and scroll to the bottom.

nano /var/lib/pgsql/data/pg_hba.conf

Scroll to the bottom where you will need to change the local line connection method from “peer” to “ident” and comment out two host lines; one under IPv4 and one under IPv6. Your permissions look like this:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
# IPv6 local connections:
#host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Save and close that file, and start the service:

systemctl start postgresql

Postgres User Password

When PostgreSQL is installed a superuser is created for the database server, but it doesn’t have a password and therefore cannot login. Use the following command as root to set the password, don’t forget the “sudo” in this case because you actually want to run the command as another user.

sudo -u postgres psql

Now you should be sitting at a “postgres=#” prompt, from here you can modify tables in the database. Use the following command to set the postgres user’s password:

ALTER USER postgres PASSWORD 'your-new-password';

The server should have responded with “ALTER ROLE” if the password was set successfully. Exit the prompt by using:

\q

Require Logon

Now that the password is configured we need to go back to “/var/lib/pgsql/data/pg_hba.conf” to make the local connections require a password. The local line you changed from “peer” to “ident” in a previous step, now needs to be changed to “md5”. Open pg_hba.conf and scroll to the bottom again.

nano /var/lib/pgsql/data/pg_hba.conf

Here is an example of what it should look like now:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident
#host    pfadmin         mailreader      127.0.0.1/32            md5
#host    pfadmin         pfadmin         127.0.0.1/32          md5
#host    roundcubemail   roundcube       127.0.0.1/32    md5
# IPv6 local connections:
#host    all         all         ::1/128               ident

Create the Database

Since we will be using ViMbAdmin to manage users and domains, we need to create a user and a database for it. The following command will create the user and prompt you to create the user’s password: (Again don’t leave off the “sudo” command because this command is meant to be run as the postgres user, also I usually like to change to /home before running the command so I don’t get the error changing to “/root” directory.)

cd /home
sudo -u postgres createuser -D -A -P vimbadmin

Create the Database:

sudo -u postgres createdb -O vimbadmin vimbadmin

Restart the service:

systemctl restart postgresql

Unix mailreader Account

The mail will be stored on the filesystem, so the mailserver needs a system account in order to read the mail. First lets see if the group exists. It should have already been created, you can verify using this command:

cat /etc/group | grep mail

It should return something like “mail:x:12:postfix” if the group ID number is not “12”, take note of this number because you will need it later. If the group doesn’t exist, add it using:

groupadd -g 12 mail

Create the new user for storing and accessing the email files on the disk:

useradd -g mail -u 200 -d /home/mail -s /sbin/nologin mailreader

The previous line adds the mailreader user to the “mail” group, sets their user ID number to 200, their “home” directory to “/home/mail” and disables interactive logon.

Conclusion

The Mailserver should now have a functioning PostgreSQL database, and we have created a user account for managing the mail files on the disk. In the next section we will install ViMbAdmin, and add tables to the database we created in the this section.

If you missed the previous parts, you can use the links below to read them:

Part 1: Preparing the Server and Certificates
Part 2: Installing and Configuring Postfix

Or you can Continue to Part 4 where we install ViMbAdmin.