Configuring PostgreSQL

Before starting on Postgres configuration, take a look at the relevant documentation for your version of PostgreSQL. The PostgreSQL documentation is fantastic. I have almost always been able to answer my questions with the official documentation, which is not something that can be said about many open-source projects.

The first thing to do is to initialize a database. FreeBSD installs a PostgreSQL-specific user named pgsql in order to give secure permissions to your data. We'll need to use the su command to get root, so that we can then su again into the pgsql user. You can't just go straight between your user and pgsql because your user doesn't (or shouldn't) have the right permissions.

$ su
# su - pgsql
$ initdb -D /usr/local/pgsql/data

Your database server is now initialized and ready to use. It's time to fire up the server and see if it works. First, drop out of the pgsql user and enable PostgreSQL in /etc/rc.conf as root:

postgresql_enable="YES"

Then start the server using service:

 # service postgresql start

Now, test that you can access the database locally:

 # service postgresql restart
 # su pgsql
 $ psql template1

You should now have a prompt that looks like this: template1=#. You can quit psql with the command \q.

Now (still as pgsql) add a database user for yourself:

 $ createuser -P your-name

You will be prompted for a password. Once you have entered the password, you can drop back down to your regular user by typing exit.

Now, create a new database for your project, and test that you can log into it:

 $ createdb project-name
 $ psql project-name

Now we should configure internet access (or the prevention thereof) to the database. Since we're making a Django app, you will mostly be managing the database through its utilities, so it shouldn't be a big deal to just shut off internet access to it. If you really need to run a query in SQL, you can either modify the configuration to allow internet access, or just use psql, the command line interface to PostgreSQL.

There are two primary files you will need to use to configure Postgres: pg_hba.conf (official docs) and postgresql.conf. The default postgresql.conf should work. Tuning PostgreSQL is beyond the scope of this tutorial, but the PostgreSQL wiki has a basic tutorial.

pg_hba.conf

The 'hba' in pg_hba.conf means "host-based authentication". In its default form, the majority of pg_hba.conf is comments, which are very informative. The actual configuration itself, however is not nearly as intimidating as it might seem. Since we're only using Django to manage the database, we can make a pretty restrictive configuration, since we only have to accommodate one "user". In the default config, access is set up for local, IPv4, and IPv6 connections. We don't really need IPv6, and we only need to accept host connections from loopback, so this file can be pared down to two lines:

 local     all            all                      md5
 host      project-name   your-name 127.0.0.1/0    md5

The first column describes the type of connection. A local entry is one that is internal to the operating system and occurs over Unix sockets. A host entry is a TCP/IP network connection. Despite the fact that Django will be running and connecting to a database on the same computer, it still uses TCP/IP so it needs a host entry.

The second column is the database name that the entry is for, and the third is the user name.

The fourth column is the IP address(es) that will have these rules applied to them. Remember to include the network mask.

Finally, the fifth column is the authentication method. If you don't want to run a separate authentication service, md5 is likely the best choice. If you don't have to worry about security, trust is also fine in many cases (or when troubleshooting problems). password is usually a bad choice, because it transmits the password in plaintext. Use md5 instead.

Now create a new database (make sure you're in your own user account):

 $ createdb project-name

That's it!