May 102013
 

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?

  One Response to “Restricting IP addresses to specific databases”

  1. Probably good enough for what you’re doing.