Sometimes, you don’t want just anyone talking to your database. In fact, sometimes, you don’t want anyone accessing your database except very specific applications. In this case, I am setting up about 20 databases, each one dedicated to a specific use, and to be accessed only from one IP address each. In this post, I’ll talk about how I first set this up with pg_hba.conf, and then how I altered it to be more restrictive.
The environment
I just set up a FreeBSD 9.1 server and it’s running about 20 jails. If you’re not familiar with FreeBSD jails, it’s safe for this discussion to consider each of them a virtual machine. This server has 6x3TB HDD installed and it’s using the ZFS filesystem.
The purpose of this setup is hands-on experience with Bacula. I’m giving a tutorial on Wednesday.
Each user has their own jail, complete with Bacula pre-installed, but not configured. Bacula uses a database to store a list of all files backed up. These Bacula installations are pre-set to use PostgreSQL.
There is just one database server (PostgreSQL 9.2), running on the host system. Each jail will connect to the host, as demonstrated with this example:
[dan@bacula100:~] $ psql -h 10.0.44.91 -U dan bacula100 psql (9.2.4) Type "help" for help. bacula100=#
What we have; what we want
The initial setup restricted connections to a specific network range. Now that I have more time, I can narrow this down a bit.
Why?
I was thinking that the students might accidentally, or intentionally, connect to the database in use by someone else. By using pg_hba.conf, I can reduce the number of problems they will encounter. After all, the goal of this tutorial is to learn more about Bacula as a first-time user.
NOTE: I don’t show this in the examples below, but after each change to pg_hba.conf, I issue this [perhaps FreeBSD-specific] command to reload the settings:
/usr/local/etc/rc.d/postgresql reload
The current settings
At present, pg_hba.conf contains this:
# TYPE DATABASE USER ADDRESS METHOD host all all 10.0.44.0/24 trust
Which means that all databases are available to all users within the 10.0.44.0/24 range.
Restricting by IP address
Here’s my first change:
# TYPE DATABASE USER ADDRESS METHOD host bacula100 all 10.0.44.100/32 trust
This means that connections to the bacula100 database are accepted only from the 10.0.44.100 address. Let’s see what happens if we try connecting from my laptop, instead of the jail:
$ psql -h 10.0.44.91 -U dan bacula100 psql: FATAL: no pg_hba.conf entry for host "10.0.44.2", user "dan", database "bacula100", SSL off
OK, good, that’s exactly what we want.
Here is a connection from the 10.0.44.100 jail:
$ psql -h 10.0.44.91 -U dan bacula100 psql (9.2.4) Type "help" for help. bacula100=#
Yes, that still works.
Restricting by user
Now, let’s be more specific here: let’s restrict connections by user. In the past examples, we’ve been connecting as user dan. Now let’s change pg_hba.conf to allow connections only as the user bacula.
# TYPE DATABASE USER ADDRESS METHOD host bacula100 bacula 10.0.44.100/32 trust
Connections as dan now fail, but connections are bacula succeed:
$ psql -h 10.0.44.91 -U dan bacula100 psql: FATAL: no pg_hba.conf entry for host "10.0.44.100", user "dan", database "bacula100", SSL off $ psql -h 10.0.44.91 -U bacula bacula100 psql (9.2.4) Type "help" for help. bacula100=>
I could do more, but this is where I’m going to leave it. I’ll extend the rules to complete the range of IP addresses, but I’m not going to impose any further restrictions:
#host all all 10.0.44.0/24 trust host bacula100 bacula 10.0.44.100/32 trust host bacula101 bacula 10.0.44.101/32 trust host bacula102 bacula 10.0.44.102/32 trust host bacula103 bacula 10.0.44.103/32 trust host bacula104 bacula 10.0.44.104/32 trust host bacula105 bacula 10.0.44.105/32 trust host bacula106 bacula 10.0.44.106/32 trust host bacula107 bacula 10.0.44.107/32 trust host bacula108 bacula 10.0.44.108/32 trust host bacula109 bacula 10.0.44.109/32 trust host bacula110 bacula 10.0.44.110/32 trust host bacula111 bacula 10.0.44.111/32 trust host bacula112 bacula 10.0.44.112/32 trust host bacula113 bacula 10.0.44.113/32 trust host bacula114 bacula 10.0.44.114/32 trust host bacula115 bacula 10.0.44.115/32 trust host bacula116 bacula 10.0.44.116/32 trust host bacula117 bacula 10.0.44.117/32 trust host bacula118 bacula 10.0.44.118/32 trust host bacula119 bacula 10.0.44.119/32 trust host bacula120 bacula 10.0.44.120/32 trust host bacula121 bacula 10.0.44.121/32 trust
Exercises for the reader
We are trusting each connection. How could we make that more restrictive?
What other restrictions would you impose?
Probably good enough for what you’re doing.