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
Then start the server using
# 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
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
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.
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
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
Now create a new database (make sure you're in your own user account):
$ createdb project-name