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.
Leave a comment